How to Load Option Chains from MSN Money to CSV

Contents

• Option Chains at MSN Money
• Undocumented URLs to Download Option Chains from MSN Money
• Loading Option Chains from MSN Money using gsqlcmd

Option Chains at MSN Money

MSN Money publishes US stock option chains.

You can open https://www.msn.com/en-us/money, search a ticker like MSFT, and click the 'Options' tab.

You can use URLs like https://www.msn.com/en-us/money/stockdetails/options/fi-126.1.MSFT.NAS
or https://www.msn.com/en-us/money/stockdetails/options/fi-MSFT to open pages with option chains directly.

MSN Money has no links to download option chains to CSV.

Undocumented URLs to Download Option Chains from MSN Money

MSN Money uses a web service to load option chains into web pages.

The web service returns JSON and has URLs like this:

http://www.msn.com/en-us/finance/stocks/optionsajax/MSFT/?date=1/19/2018

The challenge is getting expiration dates and parsing JSON to CSV.

Loading Option Chains from MSN Money using gsqlcmd

You can use specialized download and parser utilities like gsqlcmd to download and convert JSON data to CSV.

Create a text file like 'tickers.txt' and place ticker and expiration date pairs one by line like:

TICKERS.TXT

AAPL    20180119
AAPL    20180216
AAPL    20180615
AAPL    20190118

Create a batch file like 'load.cmd' with the following commands:

LOAD.CMD

@echo off

@for /F "tokens=1,* delims= " %%i in (tickers.txt) do (
    gsqlcmd download "http://www.msn.com/en-us/finance/stocks/optionsajax/%%i/?date=%%j" src\%%i-%%j.JSON /delay=200 /echoUrl
)

gsqlcmd convert src\*.json csv\*.csv ^
    "/add=Symbol=FileNameWithoutPage();LoadDate=FileDateTime();ExpDate=FileNameDate();Type=C" ^
    /quoteChar= /DateFormat=yyyy-MM-dd /echoInputFileName /rootPath=C

gsqlcmd convert src\*.json csv\*.csv ^
    "/add=Symbol=FileNameWithoutPage();LoadDate=FileDateTime();ExpDate=FileNameDate();Type=P" ^
    /quoteChar= /DateFormat=yyyy-MM-dd /echoInputFileName /rootPath=P /Append

Run it. You will see the result like this:

C:>load.cmd

http://www.msn.com/en-us/finance/stocks/optionsajax/AAPL/?date=1/19/2018 - ok
http://www.msn.com/en-us/finance/stocks/optionsajax/AAPL/?date=2/16/2018 - ok
http://www.msn.com/en-us/finance/stocks/optionsajax/AAPL/?date=6/15/2018 - ok
http://www.msn.com/en-us/finance/stocks/optionsajax/AAPL/?date=1/18/2019 - ok
AAPL-20180119.JSON - 72 rows
AAPL-20180216.JSON - 35 rows
AAPL-20180615.JSON - 37 rows
AAPL-20190118.JSON - 43 rows
AAPL-20180119.JSON - 72 rows
AAPL-20180216.JSON - 35 rows
AAPL-20180615.JSON - 37 rows
AAPL-20190118.JSON - 43 rows

The batch file contains the @for command to download data for every ticker and expiration date pair.

gsqlcmd "knowns" this URL and converts dates in the yyyyMMdd format to the required M/d/yyyy.

Also, the batch file contains two commands that parse loaded JSON data to CSV, for CALL and PUT sections.

To get suitable data for further import to a database, you have to add the symbol, expiration date, option types that are absent in raw data.

In gsqlcmd, you can do this using the /add option.

Below is a result example:

AAPL-20180119.CSV

Symbol;LoadDate;ExpDate;Type;price;last;change;changePercent;ChangeDirectionClass;timeValue;bid;ask;vol;openInterest
AAPL;2017-06-01 19:24:16;2018-01-19;C;2.5;151;-2.13;-0.0139;decrease;1.44;151.15;151.8;6;6
AAPL;2017-06-01 19:24:16;2018-01-19;C;5;147.98;147.98;;increase;1.44;148.65;149.3;8;
AAPL;2017-06-01 19:24:16;2018-01-19;C;7.5;;;;unchanged;;146.15;146.85;0;
...

Downloads

Disclaimer

MSN is a logo and registered trademark of Microsoft Corporation.