/ 
Finance-Shares-1.01
/ Finance::Shares::MySQL

NAME

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

SYNOPSIS

    use Finance::Shares::MySQL;

    my $db = new Finance::Shares::MySQL(
	    hostname   => 'my.server',
	    port       => 3306,
	    user       => 'myself',
	    password   => 'easy2guess',
	    database   => 'shares',
	    tries      => 3,
	    verbose    => 2,
	    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');

DESCRIPTION

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.

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.

Fetching Stock Quotes

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.

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'.

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.

CONSTRUCTOR

new( options )

options are passed to the base class. See "new" in DBIx::Namespace for details of the keys

dbsource
user
password
database

Keys recognized by this module are:

end_date

The end date to use when none is given. (Defaults to today's date)

exchange

Provide a default setting so that it doesn't have to be entered repeatedly. (Default: '')

mode

This controls how the quotes are processed. Suitable values are:

online

Stock quotes are fetched directly from !Yahoo without being stored in the database.

fetch

Stock quotes are fetched from !Yahoo and stored in the database. If the data has already been fetched it is overwritten.

cache

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.

offline

Quotes are only extracted from the database.

start_date

The default start date. (Default:'2000-01-01')

tries

The number of attempts made to fetch a failed internet request.

url_function

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.

verbose

Controls the number of warnings given. Can be 0, 1 or 2.

MAIN METHODS

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

fetch( options )

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.

symbol

The !Yahoo stock code whose quotes are being requested.

name

The identifier used for this particular stock. If omitted it is derived from symbol.

exchange

The identifier used for the stock exchange where symbol is quoted. If omitted it is derived from symbol, defaulting to 'US'.

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.

mode

This controls how the data is fetched and stored. See the constructor option mode for details.

tries

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.

data_set( name, var [, value] )

name

The fully qualified name of the table where var is stored.

var

Variable name.

value

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');

data_get( name, var )

name

The fully qualified name of the table where var is stored.

var

Variable name.

Return the string associated with a variable.

Example

my $db = new Finance::Shares::MySQL(...);
my $name = $db->data_name('BSY', 'London');
my $company = $db->data_get($name, 'Company');

data_name( id [, exch] )

Return a fully qualified user name leading to the data table for the stock code and exchange given.

quote_name( id [, exch] )

Return a fully qualified user name leading to the quotes table for the stock code and exchange given. exch defaults to 'US'.

SUPPORT METHODS

start_date( )

Returns the start date used by fetch().

end_date( )

Returns the end date used by fetch().

stock_create( id [, exch] )

Create a new blank table for the identified stock.

id

The identifier used for this particular stock.

exch

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_fetch( symbol, start, end [, table] )

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.

symbol

The stock code whose quotes are being requested.

start

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

end

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

table

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:

[ date, open, high, low, close, volume ]

An exception is thrown if there was a problem.

stock_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.

yahoo( obj, symbol, start, end )

obj

The Finance::Shares::MySQL object. This is needed to access date conversion functions.

symbol

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('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'

BUGS

Please report those you find to the author.

AUTHOR

Chris Willmot, chris@willmot.org.uk

SEE ALSO

Finance::Shares::Sample.

There is also an introduction, Finance::Shares::Overview and a tutorial beginning with Finance::Shares::Lesson1.