Hey ya'll I want to make a database of the S&P500 tickers with excel that will automatically update everyday. Does anyone have any advice or resources on how to do this? I go through boatloads of data right now manuelly with excel so I am trying to make my life easier instead of manuelly dealling with it. My programming ends at excel... I was wondering if anything like VBA/Python/R/or Mysql would be needed for this... I appreciate any advice!
It looks to me like this open source software project deals with data collection into Excel sheets. The source of data can be TWS or Yahoo Finance. I'm guessing you can get tick data from TWS but only one minute data from Yahoo, but don't quote me on that! Maybe this would be a way to not reinvent the wheel. http://trade-manager.blogspot.com/ https://code.google.com/p/trade-manager/feeds But it looks like you may need to know Javascript to lift the part that gets data from Yahoo Finance. I may or may not be able to help with that.
What time frames are you analyzing will determine some of the answer. tick data, 1 minute, hourly, daily? From many brokers/data providers you can simply download historical data using their API and/or free software that supports said broker/data provider.
any programming can be used to get data from yahoo, e.g. c# can very easily download yahoo historical data. for storage i recommend sql server. it's perfect, fast and does all you need.
I am presently exploring something along those lines. Using windows Xp and excel 2010 I have found that thereis a lot of help available to download yahoo historical data. Yahoo has monthly,weekly and daily historical data requested with the g=m,w,d parameter. I have only tried daily so far but do intend to try weekly and monthly. No intraday historical data available at yahoo to my knowledge. I intend initially to keep a year of daily data with each symbol of say the SP500 a separate .csv file with data,open,high,low,close,volume tokens. My intent is to open,process,close all files in my list using VBA. Yahoo has some restrictions on size of request that I will have to deal with possibly with sleep/delay to satisfy the yahoo server. Expect I can schedule the macro enabled spreadsheet to run auto similar to cron/cron tab In the unix world but I a not there yet. I think the main decision on my part was initially whether I wanted one text file sequentially appending data for each symbol daily or one file per symbol and maintaining a list of files to process. I like the 2nd choice but that's just me. Processing time is not an issue. I expect to run daily and have a single result .txt file Listing symbols and prices that I will need to look at in TOS charting tools. Don't know if any of this is relavant to you. Good luck.
Easily done with a tool from Trade Station that makes it quite easy to export any amount of data from any symbol or time frame to Excel in real time.