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 " +


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

            var readWeb = readUrl(builtUrl);

            if (readWeb == null)


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



            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;



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


            // 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"));



No comments:

Post a Comment