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."
 

Adipisci nam a voluptas ad eos. Fugiat veritatis aut officia sapiente ab et nihil pariatur. Culpa molestiae earum optio illum. Explicabo provident sed earum est est. Dolores at voluptate assumenda id ut at voluptates. Nostrum et sed repellendus temporibus asperiores.

Similique dolores quisquam dicta voluptas. Accusamus natus natus accusamus enim. Laudantium nemo sit qui ea. Sunt vel dolorem repudiandae quia. Dolore exercitationem et doloribus. Molestias quod non facere dolore ipsa nemo. Dolores illo nihil aut quidem consequuntur cum.

Voluptatem doloribus quibusdam quibusdam officiis explicabo. Nobis enim est ea est doloribus ipsum quidem.

Eligendi temporibus nihil qui. Et eaque saepe libero fugiat ad quo. Quas repudiandae dolorum sequi porro et suscipit.

Career Advancement Opportunities

April 2024 Investment Banking

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

Overall Employee Satisfaction

April 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

April 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

April 2024 Investment Banking

  • Director/MD (5) $648
  • Vice President (19) $385
  • Associates (87) $260
  • 3rd+ Year Analyst (14) $181
  • Intern/Summer Associate (33) $170
  • 2nd Year Analyst (66) $168
  • 1st Year Analyst (205) $159
  • Intern/Summer Analyst (146) $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

1
redever's picture
redever
99.2
2
BankonBanking's picture
BankonBanking
99.0
3
Secyh62's picture
Secyh62
99.0
4
Betsy Massar's picture
Betsy Massar
99.0
5
GameTheory's picture
GameTheory
98.9
6
dosk17's picture
dosk17
98.9
7
kanon's picture
kanon
98.9
8
CompBanker's picture
CompBanker
98.9
9
Kenny_Powers_CFA's picture
Kenny_Powers_CFA
98.8
10
numi's picture
numi
98.8
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...”