The Salty Economist

Things I Should Have Learned in High School
posts - 56, comments - 0, trackbacks - 0

Getting Started with F# (3)

OK, so now we have database access.  What to do next?

The main flow of the program is to loop over all 100 industries, and then for each industry create an index that is made up of all the stock prices for the companies in the industry.  For each company, I need to retrieve its historical stock prices (from 1990 forward).  Because I want each company to be weighted equally, I need to take the vector of prices and index them to the starting point.  In other words, I divide the vector of prices by the initial price, thereby creating an index that starts at 1.0 for every company.  I can then add the individual company indices together and divide by the number of companies to create my overall industry index.

So, the first function I need is one that will read a company's stock prices over a certain date range and then divide them all by the first price.

Below is my code for my function getPriceData.

/------------------------------------------------------------------------------

type clsprice = {adate : DateTime; price : double}
type inputParam = {tick : int; sdate : string; edate : string} 

let getPriceData (parms : inputParam) =
   
    let tickID = parms.tick
    let sd = parms.sdate
    let ed = parms.edate
   
    let selsql = " SELECT ADATE, CLOSING_PRICE FROM TBL_PRICE_DATA WHERE CLOSING_PRICE > 0.001 AND TICKER_ID = " + tickID.ToString() + " AND ADATE BETWEEN '" + sd.ToString() + "' AND '" + ed.ToString() + "' ORDER BY ADATE "

    let conn = new System.Data.SqlClient.SqlConnection(connstr)
    let iOpen = conn.Open()

    let cmd = new System.Data.SqlClient.SqlCommand(selsql, conn)

    let reader = cmd.ExecuteReader()
   
    let xPrices = new List<clsprice>()

    let _,fprice = getFirstDate (Convert.ToDateTime("01/01/1990")) DateTime.Today tickID

    if fprice > 0.001 then   
        while reader.Read() do
            xPrices.Add {adate = reader.GetDateTime(0); price = reader.GetDouble(1)/fprice}

    xPrices

/------------------------------------------------------------------------------

Now let's study the code line by line.

The function "declaration" says let getPriceData  (with argument parms) = something.  In a function, the thing that is returned is the last line of the function -- in this case xPrices is what is returned.  In F# functions do not have an explicit keyword to return a value.  Instead, the return value of a function is simply the value of the last statement executed in the function.

The function has one input parameter "parms" that is of type "inputParam".  If I only had one input parameter of type integer, I would have written "let getPriceData (parm : int) =" instead.  If I did not want to explicitly type my input parameter, I would have written "let getPriceData parm =".  I chose to use the type  "inputParam" because I wanted to pass three parameters via a single argument.

The logic behind this approach is that F# has a function: List.Map aFunc aList that takes a list vector and applies a function to each of the values in the list and returns another List.  Each of the values serves as a parameter to the related function.  In a sense, this allows you to perform a vector operation without having to loop over each item in the list.  This is really cool.  So, my thought was to create my getPriceData function and then apply to a list of tickers (those for the companies in any given industry).  In order to do this, I needed to collapse my three input parameters into one.

The type  "inputParam" is declared above as

type inputParam = {tick : int; sdate : string; edate : string} 

This statement declares a viable type that has three parts:

(1)  A ticker (tick) id value of type integer (int)

(2)  A  start date (sdate) of type string

(3)  An  end date (edate) of type string

Why I chose strings for dates is somewhat arbitrary, but I will explain my logic presently.

The next three lines:

    let tickID = parms.tick
    let sd = parms.sdate
    let ed = parms.edate

extract the ticker ID, the start date and the end date from my input parameter.  Note the indenting.  All lines of the function are indented 4 spaces (not tabs).

The next four lines create a sql query to extract my price data for the company (tickID) over the date range passed into the function.  In the sql query, I need to append the method ".ToString()" to the arguments that are not strings (e.g. Dates or Integers) in order to avoid a concatenation error.

As an aside.  Although F# types variables implicitly by their context, it seems incapable of implicitly casting them if they are used in a different context.  Interesting.

Anyway, I dynamically create my sql string, open my connection and open a data reader.

The line:

let xPrices = new List<clsprice>()

creates a list of type 'clsprice':

type clsprice = {adate : DateTime; price : double}

which is a data type with two elements: a date and a price.  Note how the DateTime element is defined.  It uses capital letters!  I can't even believe this.  What is wrong with just 'date' or 'datetime'?  I can't tell you how long I grappled to figure this one out.  For awhile, I didn't think F# even had a datetime type.

Anyhow, the goal here is to load up my list with a set of prices that each contain information as to their date and closing price.

The next line:

let _,fprice = getFirstDate (Convert.ToDateTime("01/01/1990")) DateTime.Today tickID

needs some explanation.

I have a function  getFirstDate that I pass the arguments: a start date, an end date, and a ticker ID.  In this case I use the start date of 01/10/1990, the end date is today ( the implicit function DateTime.Today returns today).  Again, note that I have to enclose my Convert.ToDateTime function in parentheses because it is a function call.

The function returns a 'Tuple,'  which in this case contains two elements: a date and a price for the first stock price in the date range specified.  I only need the second element, so I return it into my varible 'fprice'.  The first element I don't care about, so I leave it blank -- thus the notation _,frpice.

I have another post 'The Trouble with Tuples' that you should read to learn more about Tuples.

The return value fprice is the earliest stock price for the associated stock ticker.  I will use this price to index all the subsequent prices.

The next lines are:

    if fprice > 0.001 then   
        while reader.Read() do
            xPrices.Add {adate = reader.GetDateTime(0); price = reader.GetDouble(1)/fprice}

    xPrices

The if block says that if the fprice (first price) is greater that 0.001 (I use this for 0.0), then add to my list xPrices another member of type clsprice.  The new entry has a date and a price, both returnd from the data reader.

The last line:

xPrices

is used to return the list xPrices to the calling function.

 

Print | posted on Friday, June 19, 2009 8:12 AM |

Powered by:
Powered By Subtext Powered By ASP.NET