How to Build a historical database with Google or Yahoo Finance

(Last Updated On: April 14, 2014)

How to Build a historical database with Google or Yahoo Financee

The following command line (or command prompt) instructions work mostly for Windows but theoretically could be used for Mac OSX or Linux.

1. Use Yahoo Stock Symbols (YSS) to generate file with 34,000 symbols or however needed.
http://sourceforge.net/projects/yss/
You need Python language installed but use: python get_yahoo_stock_symbols.py -v -c 5
This is for 5 columns so it will do the full 34,000 symbols. Read the readme file for instructions.
Should generate file name like yahoo_stock_symbols_5
2. Download wget for Linux or mac or wget for Windows. From:
http://gnuwin32.sourceforge.net/packages/wget.htm
Use something like

wget http://ichart.finance.yahoo.com/table.csv?s=ABB&d=4&e=30&f=2005&g=d&a=3&b=6&c=2001&ignore=.csv
where ABB is symbol  and generated needed file but could have a program loop through generated YSS file yahoo_stock_symbols_5.
3. Install SQL Server or SQL Server Express. For each symbol generated
CSV file, use bcp to import directly into SQL Server. More into at:
http://www.cryer.co.uk/brian/sqlserver/howtoimportcsv.htm

Note you could use any other database really but I like SQL Server.

You could also Cron job to initiate wget process for desired symbols.

Notes about wget downloading from Google Finance:

It is better to download from Yahoo as there seems to be NO download frequency limit.

Notes about Google and Yahoo Finance services
(http://stackoverflow.com/questions/2980601/is-there-an-api-that-i-can-use-to-get-historical-and-recent-eod-stock-quotes)

Google

Posts the EOD data earlier than Yahoo (2-3 hours before Yahoo posts the data).
Sample URL: http://www.google.com/finance/historical?q=NYSE:GOOG&output=csv
Has a limit on how many requests you can make per minute (don’t know
the exact figure, but if you request data too often google will block
you for a period of time).
Good if you download the data manually and automatically (at low frequency).
Yahoo

Posts the EOD data a little later (around 7:00 p.m. CST).
Sample URL: http://ichart.finance.yahoo.com/table.csv?s=YHOO&d=3&e=23&f=2010&g=d&a=3&b=12&c=1996&ignore=.csv
Has no limit on how many requests you can make at all!
Good if you download the data both manually and automatically (no
frequency limit).

Use http://www.gummy-stuff.org/Yahoo-data.htm from more details on Yahoo data.

NOTE I now post my TRADING ALERTS into my personal FACEBOOK ACCOUNT and TWITTER. Don't worry as I don't post stupid cat videos or what I eat!
This entry was posted in Quant Development and tagged , , , , , on by .

About caustic

Hi i there My name is Bryan Downing. I am part of a company called QuantLabs.Net This is specifically a company with a high profile blog about technology, trading, financial, investment, quant, etc. It posts things on how to do job interviews with large companies like Morgan Stanley, Bloomberg, Citibank, and IBM. It also posts different unique tips and tricks on Java, C++, or C programming. It posts about different techniques in learning about Matlab and building models or strategies. There is a lot here if you are into venturing into the financial world like quant or technical analysis. It also discusses the future generation of trading and programming Specialties: C++, Java, C#, Matlab, quant, models, strategies, technical analysis, linux, windows P.S. I have been known to be the worst typist. Do not be offended by it as I like to bang stuff out and put priorty of what I do over typing. Maybe one day I can get a full time copy editor to help out. Do note I prefer videos as they are much easier to produce so check out my many video at youtube.com/quantlabs