The most comprehensive educational resources for finance

How to Download Options Prices in Excel

We have earlier discussed about downloading stock quotes in excel. You can do this in many ways such as from yahoo finance, or using the MarketXLS add-in that helps you seamlessly integrate live stock quotes in your excel workbook.

In this post, I’ll describe how the MarketXLS Stock Quotes in Excel add-in can also be used for downloading options related information in excel.

The MarketXLS add-in provides the following functions to retrieve options data:

YHOO_Option_PreviousClose("Symbol")
YHOO_Option_Open("Symbol")
YHOO_Option_Bid("Symbol")
YHOO_Option_Ask("Symbol")
YHOO_Option_StrikePrice("Symbol")
YHOO_Option_OpenInterest("Symbol")
YHOO_Option_Volume("Symbol")
YHOO_Option_ExpirationDate("Symbol")

Let’s say you want the bid and ask for AAPL Mar 2015 85.000 call option. The contract/symbol for this option is AAPL150306C00085000. You get the bid and ask rates for this option, type the following in excel. (Note: ensure that the MarketXLS add-in is installed).

=YHOO_Option_Bid("AAPL150306C00085000")
=YHOO_Option_Ask("AAPL150306C00085000")

This will pull the bid and ask rates for the option in the respective cells. The following image shows the application of other formulas as well.

Options_Quotes

The functions implemented work as normal excel functions and the software has built in intelligence to recognize special information for example if function Change_PercentChange(“MSFT”) returns a % value, the software will treat the result accordingly for easier calculations and proper formatting.

With this kind of live information in hand, you can easily build complex pricing models in excel and keep them always updated without having to worry about downloading the quotes from the web. For example, you could build an options pricing model and compare it with the live quotes to make your trading decisions.

The excel add-in has over 84 functions and gets data for stocks, options as well as mutual funds and can greatly expand your ability to analyze investments with unlimited stocks, options and mutual funds.

Checkout the MarketXLS Add-in

Leave a Reply

Your email address will not be published. Required fields are marked *

Name *