0

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?

kanna
  • 1,412
  • 1
  • 15
  • 33
  • I can get latest value using, `=GOOGLEFINANCE("VIX")` But I would like to get value on a particular date for example, VIX close value on 26th APR is 12.73, VVIX is different from VIX. – kanna May 05 '19 at 12:25

2 Answers2

3

You can get CBOE Indexes in Google Finance without scraping.

e.g.

=GOOGLEFINANCE("INDEXCBOE:VIX")

or

=GOOGLEFINANCE("INDEXCBOE:VIX3M")
Rich
  • 7,146
  • 1
  • 23
  • 25
0

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.

Christin
  • 46
  • 5