Monday, August 3, 2009

How to download Yahoo historical stock quotes and store in a local database

I'm happy to introduce some sample code to demonstrate how one can use C# to download and evaluate historical stock prices from Yahoo finance services.

You can download the sample here:


The sample uses my Reflected Data C# Library (included in the demo zip)

The following code is just the main function:

static void Main(string[] args)

        {

            // step 1: build url, download and parse data

            string ticker = "JCI";

            DateTime fromDate = DateTime.Parse("1/13/2007");

            DateTime toDate = DateTime.Parse("7/22/2009");

            Console.WriteLine("Downloading from yahoo: for stock '" + ticker + "' " +

                fromDate.ToShortDateString() + " to " +

                toDate.ToShortDateString());

            string builtUrl = buildUrl(ticker, fromDate, toDate);

            var readWeb = readUrl(builtUrl);

            if (readWeb == null)

            {

                Console.WriteLine("Failed to contact yahoo web service");

                return;

            }

            Console.WriteLine("Parsing web results");

            var parsedLines = (from txtLine in readWeb.Skip(1)

                              select new HistoryLine(txtLine, ticker)).ToArray();

            // step 2: analyze line by line to identify divedends and splits

            // the order by which records are download is known and assumed to be ascending date order

            Console.WriteLine("Looking up dividends and splits");

            for (int i = 0; i < parsedLines.Length - 1; i++)

            {

                parsedLines[i].priorDayAdjClose = parsedLines[i + 1].AdjClose;

                parsedLines[i].priorDayClose = parsedLines[i + 1].closeValue;

                parsedLines[i].DecideSplitDiv();

            }

            // step 3: pour new data into a database

            Console.WriteLine("Opening DB and inserting data");

            var dataFile = new DataFileSource(StartupPath + @"\stock history demo.accdb");

            var historyTable = dataFile.Table<HistoryLine>();

            historyTable.DeleteByDateRange(fromDate, toDate);

            historyTable.InserBulk(parsedLines);

            // step 4: quickly do something with the data

            var chkData = historyTable.DateRangeSet(fromDate, toDate);

            Console.WriteLine(chkData.Count + " records imported, records web page:" + parsedLines.Length);

            Console.WriteLine("Max:" + chkData.Function(SqlFunction.Max, "High"));

            Console.WriteLine("Min:" + chkData.Function(SqlFunction.Min, "Low"));

            var lastRecordByDate = chkData.Sort("TheDate desc").First;

            Console.WriteLine("Last value:" + lastRecordByDate.closeValue + " on " + lastRecordByDate.TheDate.ToString("g"));

        }




Cheers!

No comments:

Post a Comment