CODEWITHSUNDEEP
google-sheetsgoogle-finance
kanna
kanna

Reputation: 1450

How to get historic VIX data in Google spreadsheet

I could get price of a stock on a data using 

=INDEX(=GOOGLEFINANCE("AAPL","price","4/26/2019"),2,2)

But same formula doesn't work for VIX

=GOOGLEFINANCE("VIX")

does return current value of VIX.

How can I get VIX on a specific date?

Upvotes: 0

Views: 6163

Answers (2)

Rich
Rich

Reputation: 7214

You can get CBOE Indexes in Google Finance without scraping.

e.g.

=GOOGLEFINANCE("INDEXCBOE:VIX")

or

=GOOGLEFINANCE("INDEXCBOE:VIX3M")

Upvotes: 4

Christin
Christin

Reputation: 46

I've come across a similar issue where =GoogleFinance seems to have missing data for certain stocks, on certain dates.

One alternative free data source is MarketWatch, and I found that below is able to retrieve VIX on 4/26/2019:

=index(importhtml("http://bigcharts.marketwatch.com/historical/default.asp?symb=vix&closeDate=4%2F26%2F19&x=41&y=17","Table"), 3, 2)

For my own issue, it seemed like it had trouble with market closed days (i.e. weekends and NASDAQ holidays), and I was able to force it to approximate price with this formula:

=index(GoogleFinance("AMZN", "price", WORKDAY(E20+1,-1,'Market Closed Days'!Q2:Q10),1),2,2)

Where I specified the date in cell E20, and a list of NASDAQ holidays in a sheet named Market Closed Days in cells Q2:Q10. This does not seem to be the case for 4/26/2019, but I wanted to share it in case it is helpful for others.

Upvotes: 0

Related Questions