SOLVED

Does STOCKHISTORY work for INDICES? Where can I find the symbols?

Occasional Contributor

Hi all experts,

 

Try to download history of DOW, NASDAQ, S&P and HKSE.

 

Thanks,

 

Eric

14 Replies

@Eric9919 

Not sure about other indices, with NASDAQ it could be like

image.png

Hi @Eric9919 

 

If this can help & assuming you run a Windows version of Excel. Attached is a Power Query based "solution" that downloads history from Yahoo Finance. Put this together a year a ago for this thread (the OP was smart enough not to provide any feedback...) and enhanced it in the meantime

 

Just checked ==> still work

Thanks. I was able to find below 3 indices using Stock Data Type search, but only NASDAQ. worked. And I can't find Hong Kong Stock Exchange (Hang Seng Index). Any idea?

S&P 500 INDEX
NASDAQ Composite Index
DOW JONES INDU AVERAGE NDX

Thanks,
Thanks. It is working for Stock Tickers, but I am actually looking for market indices.

@Eric9919 

Re. It is working for Stock Tickers, but I am actually looking for market indices

Not sure I understand but this might be due to my ignorance in investment terms

 

Below is what STOCKHISTORY's function downloads for the NASDAQ Composite Index + on the right  what the query downloads from Yahoo finance for symbol ^IXIC. (symbol from page World Indices). With the exception of the [Volume]* not sure I see where the difference is:

 

_Screenshot1.png

 

* Checked a couple other websites but they don't provide volume information

==> No idea which of the above ones is right

 

Attached file contains a few index/indices, inc. HKSE (Hang Seng Index) but this might not be what you expect...

 

 

 

@L z. Thanks and you understood correctly.

 

I was able to use STOCKHISTORY to download only NASDAQ history, but not the others.

Eric9919_1-1657702814200.png

And thanks for your sample XLSX and I will try it for sure.

 

Cheers,

 

Eric

 

@Eric9919 

 

I searched a bit the Net and it seems that the unability to download (via STOCKHISTORY) the S&P 500 and Dow Jones Industry indexes/indices is a well known issue right now. I tried a bunch of approaches with the 4-character ISO market identifier code (MIC) to no avail. ==> #VALUE!

 

Re. the Power Query query to Yahoo finance

If you want to remove i.e. the [Volume] column and/or change the order of the columns, let me know, this is a one minute change

If the "tool" does what you expect and is an acceptable workaround from your point of you, it would be nice you mark as solution one of the replies with the attached .xlsx ==> Can help those who search - Thanks in advance

@L z. 

 

Some problem, please advise. Thanks.

Eric9919_0-1657714000263.png

 

@Eric9919 

This means you didn't use the workbook I shared where Privacy Levels are disabled (something I'd like to address but pretty hard in this scenario...)

 

Within Excel:

- Go to Data (tab) > Get Data > Query Options

- Under CURRENT WORKBOOK > Privacy

- Select option Ignore the Privacy Levels and potentually improve performances > OK

 

_Screenshot.png

best response confirmed by Eric9919 (Occasional Contributor)
Solution

@Eric9919 

Found a decent way not ignoring Privacy Levels. How To section updated accordingly

File attached

@L z. awesome, worked wonderfully and thanks for doing all the heavy lifting for me. Thanks.

Glad I could help @Eric9919 & Thanks for providing feedback

it works perfectly, but I need adjusted prices on a daily (not monthly) basis. Can your excel model provide them?

@vsoler 

I'm afraid I don't understand what you want/expect as the query already provides daily prices for the Period you select when invoking function NewHistoryDownload

 

_Screenshot.png