Hi all,
I am currently in the process of developing some simple volatility surface models in excel and am looking for either an API or an add in for excel from which I can call equity option prices with minimal latency. Do any of you guys know of any free software I can use to do this? I've looked everywhere but can't find anything that works for free. I use Intrinio to call equity prices and this works great but lacks options prices and other derivatives. I need something I can use to automate the spreadsheet as I don't want to have to copy information from a site like Yahoo finance or download a CSV every time I analyze a new security.
Thanks!
Torero
Couldn't you write something in VBA that automatically downloads the information (surely Yahoo has an API?) and parses it into your spreadsheet? Ditto CSV files
You're totally right and thats a valid alternative but the issue with this is that Yahoo's data source has a low call limit so if I use them as a data source and automate my model to update frequently, I run the risk of having my IP address blocked from their site. Also their API changed a while back and I feel my whole system would be at the mercy of the current API not changing again. I'm just trying to create a robust model that isn't exposed to this risk.
For free I do not think anything is available to download option prices.
You can download historical data as csv format from CBOE's site. Though they have specifically mentioned not to use scripts to auto download but you can try.
Also I'm not sure, but maybe Yahoo finance provides option chain quotes as well.
TD ThinkorSwim (TOS) is free and has a free RTD excel feed, includes US stocks, Options, CME futures, major FX pairs, indices, and a few other things. Just open a free TD Ameritrade account to access it...and request free market data (market data is free for retirement accounts...so just open a retirement account...you don't need to put any actual money into the account top open it)
steps 1-download and open TOS (download link inside your free TD Ameritrade account) 2-login to TOS 3-open excel 4-take tickers from TOS, and put them into excel sheet -formula to pull data into excel is
=RTD("tos.rtd", , "BID", "AAPL") =RTD("tos.rtd", , "ASK", "AAPL") =RTD("tos.rtd", , "LAST", "AAPL") =RTD("tos.rtd", , "MARK", "AAPL")
You should use "mark" for options price, because often the bid/ask spread is wide...and sometimes the last trade in an option is outside the bid/ask spread because many options do not trade as actively as the underlying stock.
To get the ticker for an option in TOS, you need to goto the option chain, and select the option you want to look at.
For example, the AAPL 170 June Call ticker is ".AAPL180615C170" which can be broken down into Ticker + Expiry (YYMMDD) + (C or P) + StrikePrice
So the excel formula would be =RTD("tos.rtd", , "MARK", ".AAPL180615C170")
i've found that you need to close and reopen the excel spreadsheet in the evening to re-establish he connection to the RTD server (it seems to reset everyday after the close)
Brilliant!!! I had no idea this could be done. I have TOS so this is perfect. Thank you!
Can I create a demo portfolio using TOS and check its returns? I was talking to a sales guy from a BB and he said something like that can make you stand out.
I'll have to see if they have call limits as well. Thanks!
Stock/Finance APIs? (Originally Posted: 10/18/2017)
Hi everyone,
Anyone use finance/stock APIs as a resource for trading? If so, what do you use? Yahoo Finance used to offer a free API but it seems they discontinued it several months ago... Thank you in advance.
-Deo
I know little about APIs, but I remember Ally offering use of APIs on their trading platform.
https://www.ally.com/api/invest/documentation/getting-started/
Use google finance api in python with numpy and pandas. The api is not maintained but it still works and as far as i know yahoo api provides quotes with a 15 min delay but google finance api does not suffer the disadvantage. How ever google finance api does not provide the volume information as part of the data frame as far as i remember.
Enim quia deserunt neque consequatur. Quisquam eos sit culpa.
Similique ab reprehenderit possimus exercitationem qui ipsam non. Pariatur ut id aspernatur et eius sit eveniet. Itaque unde cum natus. Impedit voluptatem sit incidunt quia.
Et vel ullam totam tempora. Eos qui adipisci aut officiis voluptatem error voluptatem dolore. Laudantium nihil quidem eligendi consequuntur rem.
See All Comments - 100% Free
WSO depends on everyone being able to pitch in when they know something. Unlock with your email and get bonus: 6 financial modeling lessons free ($199 value)
or Unlock with your social account...