Online volatility index access

[mookyon]

Dear Members,

I am trying to access (using Excel 365, r1c1 reference mode) online historical and current volatility index values (published by CBOE under the VIX ticker symbol).

Unfortunately, for current day Microsoft stock data type returns values similar to Yahoo finance, but the STOCKHISTORY function (which I wish to use for this project) returns wrong information. See the following demonstration.

Imported from CSV: [Yahoo finance CBOE Volatility Index (Vix) Historical Data Import value is 21.27
2023-10-27​	20.39​	22.07​	19.72​	21.27​	21.27​	0​

vix [Data\stocks] value is $21.27	=RC12.Price
CBOE MKT VOLATILITY IDX	$ 21.27

[Stockhistory]: Value is 13,600.00 ₫	Despite Format (Ctrl/1) is "General" it repeatedly generated this special d-stroke-underscore character.
"=STOCKHISTORY("VIX",RC1,RC1,0,0)"
2023-10-27​	13,600.00 ₫

Please advise.

 

[DRSteele]

You have to be careful when putting the stock symbol into function STOCKHISTORY, especially without the exchange; it is wiser to refer to the cell. What you've picked is a Vietnamese company. The currency is the Vietnamese đồng (₫) (VND).

MrExcel posts20.xlsx
	B	C	D	E	F	G	H
2	start	10/20/2023
3	end	10/27/2023
4
5		Security	Price	Ticker
6		CBOE MKT VOLATILITY IDX	$ 21.27	VIX
7		VIX Securities Joint Stock Company (XSTC:VIX)	$ 13,600.00	VIX
8
9			Date	Close		Date	Close
10			10/20/2023	$ 21.71		10/20/2023	15,000.00 ₫
11			10/23/2023	$ 20.37		10/23/2023	14,400.00 ₫
12			10/24/2023	$ 18.97		10/24/2023	14,700.00 ₫
13			10/25/2023	$ 20.19		10/25/2023	14,650.00 ₫
14			10/26/2023	$ 20.68		10/26/2023	13,650.00 ₫
15			10/27/2023	$ 21.27		10/27/2023	13,600.00 ₫
Sheet1 (2)

Cell Formulas
Range	Formula
D6:D7	D6	=C6.Price
E6:E7	E6	=C6.[Ticker symbol]
D9:E15	D9	=STOCKHISTORY(C6,C2,C3)
G9:H15	G9	=STOCKHISTORY(C7,C2,C3,)
Dynamic array formulas.

 

[mookyon]

Appreciated. 1000 THX