Finance::Shares::MySQL - Access to stock data stored in a database
use Finance::Shares::MySQL; my $db = new Finance::Shares::MySQL( hostname => 'my_computer', port => 3306, user => 'myself', password => 'easy2guess', database => 'shares', tries => 3, exchange => 'NewYork', start_date => '1980-01-01', end_date => '2002-12-31', ); my @rows = $db->fetch( symbol => 'MSFT', name => 'Microsoft', exchange => 'NASDAQ', start_date => '1990-01-01', end_date => '1999-12-31' mode => 'offline', ); $db->data_set('Company::Data', 'var1', 666); my $v = $db->data_get('Company::Data', 'var1');
This module maintains stock quotes in a mysql database, fetching the data from the internet where necessary. There is also support for storing company information.
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.
Stock quotes are downloaded from Yahoo Finance in CSV format. The default url seems to be able to download historical quotes for all countries Yahoo keep data for, but a constructor option (url_function) allows this to be changed - to access a faster server, for example.
url_function
There are a lot of names used here and it can get a bit confusing. Yahoo uses a symbol made from the exchange's stock code and zero or more letters indicating the stock exchange. These are a symbol here.
Symbol Exchange Stock ====== ======== ===== MSFT NASDAQ Microsoft BT.L London British Telecom MICP.PA Paris Michelin
The underlying database has a table name for each of these. That is usually an unintelligable code used only for low level access. A user name refers to the same table, see "Namespace access", they have Quotes:: (note the leading space) prepended to the exchange and stock identifier. The identifer for each stock is the most useful and can be anything you like. It is usually used in conjunction with an identifier for the exchange - user defined again. So, in the example below, the data for Yahoo's 12126.PA might be stored under ' StockQuotes::Paris::Michelin'. It would actually be held in a mysql table with a name like 't176'.
Quotes::
Symbol Exchange Identifier ====== ======== ========== MSFT NASDAQ Microsoft BT.L London BT 12126.PA Paris Michelin
The fetch method encapsulates all the useful actions. Depending on the mode (online, cache or offline) it will fetch the quotes from the internet or the local database, returning a list of rows containing the data. The script fs_fetch will fetch a series of quotes from the internet; fs_fetch_csv additionally writing the data to a CSV file.
fetch
options are passed to the base class. See "new" in DBIx::Namespace for details of the keys
options
dbsource user password database
Keys recognized by this module are:
The end date to use when none is given. (Defaults to today's date)
Provide a default setting so that it doesn't have to be entered repeatedly. (Default: '')
This controls how the quotes are processed. Suitable values are:
Stock quotes are fetched directly from !Yahoo without being stored in the database.
Stock quotes are fetched from !Yahoo and stored in the database. If the data has already been fetched it is overwritten.
If the requested quotes seem to be stored in the database they are returned from there. Otherwise they are fetched from the internet and stored before being returned. This, the most efficient mode, is the default.
Quotes are only extracted from the database.
The default start date. (Default:'2000-01-01')
The number of attempts made to fetch a failed internet request.
This would be a function returning a fully qualified URL for fetching a series of up to 200 quotes using the same format as http://finance.yahoo.com. There should be no need to over-ride the default which works well with all exchanges known to !Yahoo. However, if it is needed the function should be a replacement for the yahoo method.
yahoo
Controls the number of warnings given. Can be 0, 1 or 2.
The following methods are specifically tailored to support a database of stock quotes. It is used to fill a Finance::Shares::Sample object, which in turn is used by almost all the other Finance::Shares:: modules.
The top level namespace is typically populated by exchanges (and a few internal names, all with leading spaces). Each exchange namespace holds identifiers for all the known stock quoted there. Each stock entry has two tables, one for quotes and another for data.
Most of these methods work with the quotes table, which has these fields:
QDATE The date OPEN Opening price HIGH Highest price on the day LOW Lowest price on the day CLOSE Closing price VOLUME Number of shares traded
The data table provides some limited way of storing information about each share. The variable name and the data are stored as strings up to 255 characters long. Its fields are:
VARIABLE The name VALUE The content
This is the main method, called automatically by the constructor. It acquires the stock quotes if it can, returning an array of array refs each having the form:
[ date, open, high, low, close, volume ]
options are in hash key/value format, with the following keys recognized.
The !Yahoo stock code whose quotes are being requested.
The identifier used for this particular stock. If omitted it is derived from symbol.
symbol
The identifier used for the stock exchange where symbol is quoted. If omitted it is derived from symbol, defaulting to 'US'.
The first day to fetch, in the form YYYY-MM-DD.
The last day to fetch, in the form YYYY-MM-DD.
This controls how the data is fetched and stored. See the constructor option mode for details.
mode
The number of attempts to be made fetching the quotes from the internet. This defaults to the value given to the constructor. A value of 0 forces mode to be 'offline'.
Exceptions may be thrown.
The fully qualified name of the table where var is stored.
var
Variable name.
A string up to 255 characters long.
Store a value against a variable name in a particular table.
Example
my $db = new Finance::Shares::MySQL(...); my $name = $db->data_name('BSY', 'London'); $db->data_set($name, 'Company', 'British Sky Broadcasting');
Return the string associated with a variable.
my $db = new Finance::Shares::MySQL(...); my $name = $db->data_name('BSY', 'London'); my $company = $db->data_get($name, 'Company');
Return a fully qualified user name leading to the data table for the stock code and exchange given.
Return a fully qualified user name leading to the quotes table for the stock code and exchange given. exch defaults to 'US'.
exch
Returns the start date used by fetch().
Returns the end date used by fetch().
Create a new blank table for the identified stock.
The identifier used for this particular stock.
The identifier used for the stock exchange where symbol is quoted.
Unlike the other stock_ methods this does not accept a mysql table. Instead it returns one which may or may not have been created on the way.
stock_
Quote data is fetched from the internet for one stock over the given period. The data is stored in a mysql table if one is given.
The stock code whose quotes are being requested.
The name of the mysql table where the data is to be stored.
Data is returned as an array of array refs each having the form:
An exception is thrown if there was a problem.
Check whether an appropriate number of values exist in the specified table between the dates given.
Return 1 if seems ok, 0 otherwise.
The Finance::Shares::MySQL object. This is needed to access date conversion functions.
The abbreviation used to identify the stock and exchange. E.g. 'BSY.L' for BSkyB quoted in London.
The first quote date requested, in YYYY-MM-DD format.
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.
yahoo('BA.L', '2002-06-01', '2002-06-30')
This would return (on a single line, of course)
'http://table.finance.yahoo.com/table.csv? a=6&b=1&c=2002&d=6&e=30&f=2002&s=BA.L'
Please report those you find to the author.
Chris Willmot, chris@willmot.org.uk
Finance::Shares::Sample.
There is also an introduction, Finance::Shares::Overview and a tutorial beginning with Finance::Shares::Lesson1.
To install TestFuncs, copy and paste the appropriate command in to your terminal.
cpanm
cpanm TestFuncs
CPAN shell
perl -MCPAN -e shell install TestFuncs
For more information on module installation, please visit the detailed CPAN module installation guide.