The London Perl and Raku Workshop takes place on 26th Oct 2024. If your company depends on Perl, please consider sponsoring and/or attending.
 / 
Finance-Shares-MySQL-0.03
/ Finance::Shares::MySQL

NAME

Finance::Shares::MySQL - Access to stock data stored in a database

SYNOPSIS

    use Finance::Shares::MySQL;
    use Finance::Shares::MySQL qw(yahoo_uk);

Simplest

Fetch quotes for one share from the internet and store in the database. Then output the data as a CSV file whose name is made from the EPIC and start and end dates.

    my $db = new Finance::Shares::MySQL( user => $user );
    $db->fetch($epic, $date1, $date2);
    $db->to_csv_file($epic, $date1, $date2);

Typical

Fetch quotes listed in a file and inspect quotes for one share.

    my $db = new Finance::Shares::MySQL ( 
                user      => $user,
                password  => $password,
                directory => '~/stocks',
                logfile   => 'db.log',
                loglevel  => 1 );
    
    my $failures = $db->fetch_from_file( "db.req" );
    Finance::Shares::MySQL->
        print_requests( $failures, "next.req" );

    my $tbl = $db->select_table("BSY_L", [qw(qdate close)]);
    Finance::Shares::print_table( $tbl, "BSY.csv" );

DESCRIPTION

The intent is to provide access to stock quotes and possibly other data, using an underlying mysql database to do all the hard work.

Preparing the Database

Before using this module, the necessary permissions must be in place on the database server you wish to use. This usually means logging on as root and giving the necessary password:

    root@here# mysql -u root -p mysql
    Password:

Within mysql grant the user the necessary privileges (replace quoted items with your own):

    mysql> grant all privileges on "shares".* to "joe"
           identified by "password";

Global file privileges and all privileges on the named database should be sufficient for Finance::Shares::MySQL use.

Accessing the Database

If a mysql database is available, a Finance::Shares::MySQL object handles accesses to it. The constructor ensures a connection is made and processing is logged. Data is not entered directly, but fetched from the internet. It is then made available either as an array or a csv file.

The first step is to call one of the fetch methods. fetch_from_file is probably the most convenient method for keeping the database up to date. If no dates are given, all shares listed in the file will be updated as needed, attempting to refetch any failed requests. fetch_batch is the method that processes these requests, and fetch is handles the HTTP transfer of quotes for a single share.

select_table is the principal function for accessing the share data. It is a wrapper around an SQL SELECT call, returning an array of arrays and the field order. A class method is provided which will print the data returned. Alternatively to_csv_file extracts quotes from the database and saves them in a suitably named file.

CONSTRUCTOR

new( [options] )

A connection is made to the mysql server and the specified database is selected for use.

It will die if the user is not known to the server or hasn't the requisite permissions. If the user has create privileges with the server, an attempt is made to create the database if necessary.

For testing purposes, the environment variables DBI_USER and DBI_PASS are consulted if no user or password are given.

options may be either a hash ref or a list of hash keys and values. Recognized keys are:

user

The user's name, e.g. 'joe'.

password

To avoid passing this as plain text, enter ''. The password will be asked for interactively if it is not specified here.

database

Defaults to shares.

hostname

Defaults to localhost.

port

Defaults to 3306, the port number for mysql.

directory

The default directory to use for the log and csv files.

logfile

Name of the log file. See method logfile.

loglevel

See method loglevel.

url_function

Specify an alternative function for constructing the URL for fetching the quotes. (Default: \&yahoo_uk)

See yahoo_uk for function details.

OBJECT METHODS

show( item )

Pass the string item to the SQL show command. Return a reference to an array of array references. For example:

    @$array_ref = ( [ "mysql" ]
                    [ "test" ]
                    [ "stocks" ] )

fetch( epic, start_date, end_date [, table] )

epic

The stock code whose quotes are being requested.

start_date

The first day to fetch, in the form YYYY-MM-DD.

end_date

The last day to fetch, in the form YYYY-MM-DD.

table

An optional name for the database table if epic is not suitable.

Quote data is fetched from the internet for one stock over the given period. If successful, the data is added to a table named epic.

Note that any non-alphanumeric characters in epic will be mapped to underscore ('_') in creating the default name for the table. The table name may be given directly if this proves unsuitable.

Returns any failed requests as an array of (epic, start_date, end_date) arrays.

fetch_batch( requests [, start_date [, end_date]] )

requests

This should be an array reference. The array in question should contain references to arrays, one for each stock request. These sub-arrays should contain an EPIC and start and end dates in YYYY-MM-DD format. If the epic won't produce a suitable name for a mysql table, a table name may be added.

start_date

An optional date in YYYY-MM-DD format. This becomes the default start date.

end_date

An optional date in YYYY-MM-DD format. This becomes the default end date.

Fetch a number of stock quotes from the internet and enter them into the database. Any failed requests are returned in the same format, ready for resending. 0 is returned if there are no failed requests.

Example 1

Ensure that the requests are satisfied.

  my $requests = [ [ BP.L, 2000-01-01, 2000-12-31 ],
       [ BSY.L, 2002-06-01, 2002-09-04, "BSkyB" ] ];
  do {
        my $failed = fetch_batch( $requests );
  } while ($failed);

Example 2

If the requests structure has dates as "", 0 or undefined, the specified defaults are used. Where no defaults are given the end date becomes today. The start date becomes either the last date stored or today if there was none.

  my $requests = [ ["RIO.L"], ["BT.L", "2002-01-01"],
         ["DMGOa.L", 0, "2002-03-31", "DMGO_L1"] ];
  fetch_batch( $requests );
  
      RIO.L     as RIO_L    from last date to today
      BT.L      as BT_L     from 2002-01-01 to today
      DMGOa.L as DMGO_L1  from last date to 2002-03-31  
  
  fetch_batch( $requests, "1999-11-20" );
  
      RIO.L     as RIO_L    from 1999-11-20 to today
      BT.L      as BT_L     from 2002-01-01 to today
      DMGOa.L as DMGO_L1  from 1999-11-20 to 2002-03-31  
  
  fetch_batch( $requests, "1999-11-20", "2000-12-31" );
  
      RIO.L     as RIO_L    from 1999-11-20 to 2000-12-31
      BT.L      as BT_L     from 2002-01-01 to 2000-12-31
      DMGOa.L as DMGO_L1  from 1999-11-20 to 2002-03-31

The last BT request would be ignored as the end date is before the start date.

fetch_from_file(file [, dir] [, start [, end])

file

A fully qualified path-and-file or a simple file name.

dir

An optional directory. If present (and file is not already an absolute path), it is prepended to file.

start

An optional date in YYYY-MM-DD format. This becomes the default start date.

end

An optional date in YYYY-MM-DD format. This becomes the default end date.

The stock codes (and dates) to be fetched are stored in the specified file. The return value is the same as fetch_batch(), although 3 attempts are made before any requests are failed.

The file may have '#' comments and blank lines, with leading and trailing spaces stripped. Each line should be of the following form, with items separated by spaces or commas.

  <epic> [, <start_date> [, <end_date> [, <table_name>]]]

Example 3

  # Rio Tinto will take on both default dates while
  # BT uses the default end date (probably 'today').
  # The Daily Mail 'a' stock will be fetched from
  # the default start (probably the last quote) to
  # 31st March, stored in mysql table DMGO_L1.
  
  RIO.L
  BT.L, 2002-01-01
  DMGOa.L, "", 2002-03-31, DMGO_L1

select_table( table, columns [, start [, end] )

table

Must be the name of a table in the database. Note that this is case sensitive.

columns

A reference to an array holding column names. Probably best specified as [qw(...)].

start

An optional start date in YYYY-MM-DD format. If omitted, values for all dates will be returned.

end

An optional end date in YYYY-MM-DD format. Both dates are inclusive.

Perform a SQL select command on the database to extract a single shares table.

If called in an array context, this returns two array refs. The first is the list of columns requested (it is just the 'columns' argument). If this is undefined, all columns have been returned. The second array holds arrayrefs indicating each row of data. In a scalar context, only the rows arrayref is returned.

Example 4

To extract BP price data for the week beginning 5th August 2002.

    my ($rows, $cols) = 
        $db->select_table('BP_L', [qw(qdate open close)],
                    '2002-08-05', '2002-08-09');

$rows would hold the open and close values for the dates requested.

    [ [ 2002-08-05, 527.39, 560.00 ],
      [ 2002-08-06, 542.14, 564.00 ],
      [ 2002-08-07, 555.89, 573.50 ],
      [ 2002-08-08, 571.13, 575.00 ],
      [ 2002-08-09, 576.05, 589.50 ] ]

$cols would point to the list of column names in the order requested.

    [ '', 'open', 'close' ]

Note that the date column is inserted automatically.

If something goes wrong, the error is logged and 'false' is returned.

to_csv_file( epic, start, end [,file [,dir]] )

epic

The share ID e.g. BSY.L.

start

The first date required, in YYYY-MM-DD format.

end

The last date required, in YYYY-MM-DD format.

file

Optional file name.

dir

Optional directory prepended to the file name.

Save a portion of stock data to a csv file. If no file name is given, one is created from the share name and the dates.

last( table )

In a scalar context, return the date of the most recent quote in the named table. In array context the whole record is returned.

present( table, start, end )

Check whether an appropriate number of values exist in the specified table between the dates given.

Return 1 if seems ok, 0 otherwise.

table_exists( table )

Return 1 if the named table exists in the database, 0 if it does not. Note that table is case sensitive.

do_job( job [, errmsg] )

Perform a 'do' call on the mysql database. errmsg prepends the database error report in the log file.

ACCESS METHODS

logfile( [file [, dir]] )

file

An optional fully qualified path-and-file, a simple file name, or "" for null device.

dir

An optional directory. If present (and file is not already an absolute path), it is prepended to file.

Specify the file to use for logging. If it doesn't already exist, it is created. With no arguments, this redirects output to STDERR, while "" is interpreted as the NULL device.

Returns current logfile or null if STDERR.

loglevel( [level] )

Subsequent log messages will only be output if they are marked as less than or equal to level. Suitable values are 0, 1 or 2.

Returns the last message threshold set.

directory( [dir] )

Set the default directory for source files etc. If dir is '', it is set to the current directory.

Return the current default directory.

SUPPORT FUNCTIONS

req

An array reference as returned by fetch_batch() or fetch_from_file().

file

An optional file to dump the requests to.

dir

The file and directory may optionally be given seperately.

Prints out any failed requests. If file is omitted, the listing is sent to STDERR, otherwise the output is written in a format that may be read by fetch_from_file() for fetching later.

rows

An array ref listing rows of data, as returned by select_table.

cols

An array ref listing the columns, as returned by select_table.

file

An optional file to dump the requests to.

dir

The file and directory may optionally be given seperately.

Prints out the results of a select_table() call. If file is omitted, the output is sent to STDOUT (not STDERR notice), otherwise the output is written to the file in CSV format.

EXPORTED FUNCTIONS

    use Finance::Shares::MySQL qw(yahoo_uk);

yahoo_uk( epic, start, end )

epic

The abbreviation used to identify the stock and exchange. E.g. 'BSY.L' for BSkyB quoted in London.

start

The first quote date requested, in YYYY-MM-DD format.

end

The last quote date requested, in YYYY-MM-DD format.

The default function for constructing a url. This one accesses http://uk.table.finance.yahoo.com. Obviously targetted for the London Stock Exchange, it will fetch quotes from other exchanges. Try it first before writing a replacement.

Any replacement should accept the three strings above, and return a fully qualified URL.

Example

    yahoo_uk('BA.L', '2002-06-01', '2002-06-30')

This would return (on a single line, of course)

    'http://uk.table.finance.yahoo.com/table.csv?
                a=6&b=1&c=2002&d=6&e=30&f=2002&s=BA.L'

BUGS

Please report those you find to the author.

AUTHOR

Chris Willmot, chris@willmot.co.uk

SEE ALSO

PostScript::Graph::Stock, Finance::Shares::Log. Finance::Shares::Sample.