C# .NET Yahoo Stock Download and Charting

How to download stock price history data from Yahoo finance, store in a SQLite database, and make nice stock charts from same.

As a stockbroker and commodities trader in a former life, I've never lost my fascination with the markets. This little application I "whipped up" features a class that will download historical trading data from Yahoo Finance for any valid specified date range and issue symbol, including indexes such as the Dow Industrials Average (that's "^DJI" with Yahoo).

These data are correctly ordered with the most recent date last, and stored in a SQLite 3.0 database. If not present on a new issue, a table with the same name as the issue is created. It will automatically replace / update existing table rows and insert any new data if run again at a later date.  I also illustrate how to get the stock histories of all 30 of the DOW Stocks, and I use NPlot to offer a charting feature.

While this is by no means intended to be a complete stock portfolio or trading application, it provides the basic functionality that a programmer can use to create one.

First, let's take a look at my "Downloader" class:

using System;
using System.Data;
using System.Net;
using System.Data.SQLite;

namespace YahooStocks
{
    public class Downloader
    {
        private string urlTemplate =
            @"http://ichart.finance.yahoo.com/table.csv?s=[symbol]&a=" +
              "[startMonth]&b=[startDay]&c=[startYear]&d=[endMonth]&e=" +
                 "[endDay]&f=[endYear]&g=d&ignore=.csv";

        public DataTable UpdateSymbol(string symbol, DateTime? startDate, DateTime? endDate)
        {
            if (!endDate.HasValue) endDate = DateTime.Now;
            if (!startDate.HasValue) startDate = DateTime.Now.AddYears(-5);
            if (symbol == null || symbol.Length < 1)
                throw new ArgumentException("Symbol invalid: " + symbol);
            // NOTE: Yahoo's scheme uses a month number 1 less than actual e.g. Jan. ="0"
            int strtMo = startDate.Value.Month - 1;
            string startMonth = strtMo.ToString();
            string startDay = startDate.Value.Day.ToString();
            string startYear = startDate.Value.Year.ToString();

            int endMo = endDate.Value.Month - 1;
            string endMonth = endMo.ToString();
            string endDay = endDate.Value.Day.ToString();
            string endYear = endDate.Value.Year.ToString();

            urlTemplate = urlTemplate.Replace("[symbol]", symbol);

            urlTemplate = urlTemplate.Replace("[startMonth]", startMonth);
            urlTemplate = urlTemplate.Replace("[startDay]", startDay);
            urlTemplate = urlTemplate.Replace("[startYear]", startYear);

            urlTemplate = urlTemplate.Replace("[endMonth]", endMonth);
            urlTemplate = urlTemplate.Replace("[endDay]", endDay);
            urlTemplate = urlTemplate.Replace("[endYear]", endYear);
            string history = String.Empty;
            WebClient wc = new WebClient();
            try
            {
                history = wc.DownloadString(urlTemplate);
            }
            catch(WebException wex)
            {
              //  throw wex;
            }
            finally
            {
                wc.Dispose();
            }
            DataTable dt = new DataTable();
            // trim off unused characters from end of line
            history = history.Replace("\r", "");
            // split to array on end of line
            string[] rows = history.Split('\n');
            // split to colums
            string[] colNames = rows[0].Split(',');
            // add the columns to the DataTable
            foreach (string colName in colNames)
                dt.Columns.Add(colName);
            DataRow row = null;
            string[] rowValues;
            object[] rowItems;
            // split the rows
            for (int i = rows.Length - 1; i > 0; i--)
            {
                rowValues = rows[i].Split(',');
                row = dt.NewRow();
                rowItems = ConvertStringArrayToObjectArray(rowValues);
                if (rowItems[0] != null && (string) rowItems[0] != "")
                {
                    row.ItemArray = rowItems;
                    dt.Rows.Add(row);
                }
            }
            return dt;
        }


        public void InsertOrUpdateIssue (DataTable issueTable, string symbol)
        {
            if (issueTable.Rows.Count == 0) return;
            symbol = symbol.Replace("^", "");
            string InsertMasterSQL = Constants.NewSymbolSQLTemplate;
            //"REPLACE INTO MASTER (SYMBOL,FIRSTDATE,LASTDATE) VALUES (@Symbol, @FirstDate, @LastDate)";
                         
            DateTime FirstDate = Convert.ToDateTime(issueTable.Rows[0]["Date"]);
            DateTime LastDate = Convert.ToDateTime(issueTable.Rows[issueTable.Rows.Count - 1]["Date"]);
            object[] parms = {symbol, FirstDate, LastDate};
            SQLiteHelper.ExecuteNonQuery(Constants.ConnectionString, InsertMasterSQL, parms);
            string createIssueTableSql = Constants.SymbolSQLTemplate;
           // "CREATE TABLE [Data](Symbol VARCHAR(50) NOT NULL,Date DATETIME NOT NULL,
       // Open FLOAT,High FLOAT NOT NULL, Low FLOAT NOT NULL, Close FLOAT NOT NULL, Volume INTEGER)";
            createIssueTableSql = createIssueTableSql.Replace("[Data]", symbol);
            try
            {
                SQLiteHelper.ExecuteNonQuery(Constants.ConnectionString, createIssueTableSql, null);
            }
            catch (Exception ex)
            {
                // TABLE ALREADY EXISTS (Sorry to use exception for biz logic, but hey...)
            }
            SQLiteConnection cn = new SQLiteConnection(Constants.ConnectionString);
            cn.Open();
            // always do multiple operations in SQLite in a transaction!
           SQLiteTransaction trans= cn.BeginTransaction();
            //"INSERT INTO [SYMBOL] (SYMBOL,DATE,OPEN,HIGH,LOW,CLOSE,VOLUME) 
        // VALUES(@Symbol,@Date,@Open,@High,@Low,@Close,@Volume)"
           string sql = Constants.InsertSymbolSQLTemplate;
            foreach(DataRow row in issueTable.Rows)
            {
                string sym = symbol;
                DateTime date = Convert.ToDateTime(row["Date"]);
                float open = (float)Convert.ToDouble(row["Open"]);
                float high = (float)Convert.ToDouble(row["High"]);
                float low = (float)Convert.ToDouble(row["Low"]);
                float close = (float)Convert.ToDouble(row["close"]);
                double volume = Convert.ToDouble(row["Volume"]);
                object[]  parms2 = {sym,date,open,high,low,close,volume };
                sql = sql.Replace("[SYMBOL]", symbol);
                SQLiteHelper.ExecuteNonQuery(trans, sql, parms2);
            }
            trans.Commit();  //using a transaction for SQLite speeds up multi-inserts bigtime!
            cn.Close();
        }
                
        private object[] ConvertStringArrayToObjectArray(string[] input)
        {
            int elements = input.Length;
            object[] objArray = new object[elements];
            input.CopyTo(objArray, 0);
            return objArray;
        }
    }
}

The methods of the downloader class are pretty much self-explanatory:

UpdateSymbol - Basically this takes the entered symbol and the specified start and end dates, does some massaging to make it all fit into the correct format for yahoo's url string to request a history, downloads the data,  and uses various Split methods on the string to reorder it by date and convert it all into a DataTable.

InsertOrUpdateIssue - This accepts the DataTable from the first method, and prepares the appropriate SQLite commands and parameters to store the data in the provided SQLite database. If the required table doesn't exist (you specified a new stock issue) the database table is created first. All the raw SQL statements are stored as static strings in the Constants class. This makes it easy to do whatever manipulation is necessary to prepare the statement for one of the methods in my SQLiteHelper class, which is essentially a clone of the popular SqlHelper DAAB v2 class for SQL Server. The nice thing about SqlHelper is that you can execute a Sql statement by simply specifying the connection string, the command text, and an object array of the parameter values, in a single line of code. The parameter names, types and values are derived for you.

If you are not familiar with SQLite as an alternative database, all I can say is -- this would be a good time to try it out. It's completely self-contained in a mixed - mode assembly with the 2.0 ADO.NET provider, which even provides design-time support. Most importantly, it's a "zero-install" database, and it's fast as all hell. In this download, which zip file is only a bit over 1MB, you are getting a complete database with some stock issues already in it, and the database engine with ADO.NET provider, and all you need to do is unzip it and the database is good to go!

The downloaded history data is shown in the main form's DataGridView, and if you click the CHART button when there is a stock symbol present in the Symbol textbox on the main form, it will pop up a second form as modal, using the NPlot library to make a nice Japanese Candlestick chart with a volume bar graph below it. NPlot is pretty sophisticated, and it's also tricky, but there are sufficient demo examples that come with it to get most developers started making really cool stock charts.

I hope this may be useful to some people, it was fun to write. And once again a big thanks to my friend and developer extraordinaire Robert Simpson, who singlehandedly  put together the SQLite 2.0 ADO.NET provider that is such a pleasure to use. Note that the Stocks.db3 database file, Nplot.dll and System.Data.SQLite.dll assemblies are in the bin/debug folder of the "Harness" project.

Hmm - looks like there's a lot of resistance there on MSFT around $31...where did I put my short pants?

N.B. Just in the first couple of days since this article went online, I've received questions about how to get lists of stock symbols.  Yahoo has a symbol lookup facility here. You can download listings of security symbols from NASDAQ here (bottom of page).

Download the complete Visual Studio 2005 Solution (1202KB)

Hey, I googled stock price history and ran across your page.  Pretty cool.  I am interested in finding out how Yahoo and the like come across their stock price information.  To be specific I have an interest in stocks and wanted to experiment with some thoughts I had after reading a book on stocks.  It dealt primarily on chart analysis.  I consider myself fairly decent with excel and wanted to see if I could materialize my ideas.  What I'm looking for is a history on any given stock looking back upto a year, dumping that information into a spreadsheet and then running some formulas on that data.  And then apply this to a rather large sample of stocks with minmal effort on my part maybe just a print out each morning meeting specified criteria.

Is there a fee to obtain this information?  Is it public domain?

Thanks again for any assistance you might be able to give.

Thanks for the stock downloader.  It bombed when I asked it to download "bhp.ax" (an Australian stock).  I fixed it with the following mod to InsertOrUpdateIssue():

symbol = symbol.Replace(".ax", "aus");

symbol = symbol.Replace(".AX", "aus");

I guess SQLlite doesn't like the "." character. 

Hi Peter Bromberg,

If i want to download a list of stocks only the date& closed price from http://ichart.yahoo.com.my to my excel file ...what is the best way? Can you furnish me the coding too?

Thank you.

Thank you all !.

HI Vi

I wonder if you can send the code you made? thanks do much

atisback [at] yahoo.com