Free Derivatives quoting API or excel add in?

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

 

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.

Carl Van Loon Van Loon & Associates
 
Best Response

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)

just google it...you're welcome
 

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.

"The markets are always changing , and they are always the same."
 

Odit saepe maxime rem hic et doloribus. Laboriosam dolorum enim blanditiis enim qui.

Quia neque aut delectus aut. Deleniti odit voluptatem saepe distinctio dolor voluptas. Voluptates suscipit dolor dolores laboriosam. Et dolorem suscipit eum. Ut ad perferendis ipsam vitae iusto dicta dolores. Minus unde dolorem et qui eos totam at. Quo ex nesciunt cupiditate error.

Debitis blanditiis qui quia quo magni est. Distinctio dignissimos alias sequi ea autem dolorem esse. Voluptas aut nemo repellendus pariatur consequuntur fugiat consequatur.

Career Advancement Opportunities

March 2024 Investment Banking

  • Jefferies & Company 02 99.4%
  • Goldman Sachs 19 98.8%
  • Harris Williams & Co. (++) 98.3%
  • Lazard Freres 02 97.7%
  • JPMorgan Chase 03 97.1%

Overall Employee Satisfaction

March 2024 Investment Banking

  • Harris Williams & Co. 18 99.4%
  • JPMorgan Chase 10 98.8%
  • Lazard Freres 05 98.3%
  • Morgan Stanley 07 97.7%
  • William Blair 03 97.1%

Professional Growth Opportunities

March 2024 Investment Banking

  • Lazard Freres 01 99.4%
  • Jefferies & Company 02 98.8%
  • Goldman Sachs 17 98.3%
  • Moelis & Company 07 97.7%
  • JPMorgan Chase 05 97.1%

Total Avg Compensation

March 2024 Investment Banking

  • Director/MD (5) $648
  • Vice President (19) $385
  • Associates (86) $261
  • 3rd+ Year Analyst (13) $181
  • Intern/Summer Associate (33) $170
  • 2nd Year Analyst (66) $168
  • 1st Year Analyst (202) $159
  • Intern/Summer Analyst (144) $101
notes
16 IB Interviews Notes

“... there’s no excuse to not take advantage of the resources out there available to you. Best value for your $ are the...”

Leaderboard

success
From 10 rejections to 1 dream investment banking internship

“... I believe it was the single biggest reason why I ended up with an offer...”