Financial Database Code

How to use Perl and Mysql to Track Financial Performance of Equities

2017-09-15

Introduction

Perl's utility can be increased through bolt on software modules, a powerful mechanism for doing this is via the Comprehensive Perl Archive Network (CPAN). This is a repository of over 250,000 software modules for Perl. CPAN modules are managed through an automated installer. CPAN was started in 1995 and has resulted in other languages adopting similar infrastructure.

To gather equity information from Yahoo we are going to use the Finance::Quote and Finance::Yahoo modules. This procedure will teach you how to set-up a portfolio tracking database. The procedure will conclude by showing you some SQL filters to do some data filtering. However, in future procedure I will be teaching how to do the basic math to calculate your own composite index.

Procedure

1. Make sure you have the CPAN modules you need.

Using shell go to the CPAN auto installer:

shell> perl -MCPAN -e shell

**** Answer no to the configuration question ****

Load the modules:

cpan>install Finance::Quote

cpan> install Finance::YahooQuote

2. Now design your portfolio database. I use a two table portfolio database, the first table tracks an equity universe. This table is a list of stocks you want to track, it has additional columns to allow you disactivate equities and add notes describing M&A activity. The second table is a data table that stores financial data at a specified time for each active stock in your list. I am not an active trader so I am not concerned with speed and primarily use this to store equity data on a weekly basis.

The command used to create the list table is below:

(

name VARCHAR(35) NOT NULL,

tckr VARCHAR(5) NOT NULL,

mrkt VARCHAR(6) NOT NULL,

sctr VARCHAR(6) NOT NULL,

mcap DECIMAL(5,1) NULL,

active ENUM('yes','no') NULL,

note VARCHAR(60) NULL

);

List variables explained:

name: Full company name.

tckr: The company's ticker symbol.

mrkt: The primary market the company is related to.

sctr: The primary economic sector the company is related to.

I use chem-chemical, eng-energy,finc-financial, inds-industrial, hc-health, food-food, tech-technology, mat-materials or mining.

mcap: Market capitalization, this is the value of the company based on the equity price.

active: If 'yes' equity is active, if 'no' equity is deactivity and not included in any fetching activity. You will need to do this often as companies merge or go bust.

note: Add notes on merger activity here.

The command used to create the list table is below:

(

recid int(10) unsigned not null primary key,

tkr varchar(10) not null,

curdate date not null,

lst numeric(6,2) null,

eps numeric(5,2) null,

pe numeric(4,2) null,

ps numeric(4,2) null,

pb numeric(4,2) null,

ds numeric(4,2) null,

yield numeric(3,2) null,

vol bigint(13) null,

cap bigint(15) null

);

Portfolio variables explained:

recid: Record identification number.

tkr: Company ticker

curdate: Date of data record.

lst: List price of equity.

pe: Price to earnings.

ps: Price to sales.

pb: Price to book.

ds: Dividend per share

yield: dividend yield in %.

volume: Number of shares traded at curdate

cap: Market capitilization of the equity at curdate.

The quickest way to build the tables is to invoke a Mysql command from the shell. You also need to prepare the table create command in a text file.

Ex. Shell Command: mysql -u root -p"password" "database" < ListTableCommands.txt

Note for the above command to work you need to cd into the directory where the file resides. Now use a similar technique to build the second table.

Click here for ListTableCommands.txt

Click here for PortfolioTableCommands.txt

3. Now we are ready to prepare list of equities. I will post two lists a short one with large cap equities (above 50 billion market capitalization) and a larger list (about 2 billion and greater).

Click here for the small list.

Click here for the larger list.

Now it is easy to dump the equity text file into the list table. Simply log into mysql from the terminal line and type the following command (ensure you are in directory where data resides): load data local infile '2017StkLstSmall.txt' into table list;

4. Now you list table is populated with equities. I use mysql-workbench to see the list table has loaded data.

5. Now is the hard part you need to use the perl finance modules to extract the equity data from the web and load it into the portfolio. To do this you need to prepare a perl script. Create a text file to store your script and start coding. I won't be breaking the script down further as it is fairly long. Instead, I added a fair amount of comments to describe how the code works.

Perl Script Fetch Financial Data

Click here to download the text file of perl script presented above.

6. Now set the script to executable and script in shell.

7. After typing in current data the database should populate with your desired list of equities. If this checks out in Mysql then congratulations you finished the project.

P.S. 1. Data analysis is not the intent of this procedure, but what good is this data if you don't know how to consume it. Here are some basic sql filters to get you started:

To review your active equities.

select * from list where tckr like '%' order by tckr asc

To review financial data for a specific date.

select * from portfolio where tkr like '%%' and curdate like '2017-09-18'

Compared to the first to sql statements the 3rd is a fairly complicated. It links the list and portfolio data based on ticker symbol and filters by many different variables. Thus, providing a powerful filter tool that you can further customize to whatever you fancy.

Select portfolio.tkr, list.mrkt, list.sctr, portfolio.curdate, portfolio.lst, portfolio.pe, portfolio.ps, portfolio.pb, portfolio.eps, portfolio.ds, portfolio.yield, portfolio.vol, portfolio.cap

FROM portfolio, list

WHERE portfolio.tkr=list.tckr and portfolio.tkr like '%' and portfolio.curdate like '%' and list.active like 'yes' and list.sctr like 'inds' and portfolio.curdate like '2017-09-18' and

pe < 20 and pb < 5 and ps < 5 and ds > 0.01

ORDER BY portfolio.tkr asc

Conclusion

You now have a database with two tables. The list table controls the equities you are currently interested in, and the portfolio table controls the equity data. Use the perl script to import data on a daily or weekly basis and enjoy.