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

[Eric9919]

Hi all experts,

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

Thanks,

Eric

[#2]

@Eric9919 

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

[#3]

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 🙂

[#4]

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,

[#5]

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

[#6]

@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:

* 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...

[#7]

@Lorenzo Thanks and you understood correctly.

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

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

Cheers,

Eric

[#8]

@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

[#9]

@Lorenzo 

Some problem, please advise. Thanks.

[#10]

@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

[#11 · Accepted solution]

@Eric9919 

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

File attached

[#12]

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

[#13]

Glad I could help @Eric9919 & Thanks for providing feedback

[#14]

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

[#15]

@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

[#16]

@Andreas2345 

In reply to your Private Message:

I found your solution to the issue that Excel cannot load S&P 5000 Index data using the STOCKHISTORY function. Your solution helped me a lot but I cannot make the data load in as daily, only quarterly. Do you know how I can load in the daily data instead of quarterly?

Same as to @vsoler, I'm afraid I don't unsterstand. If you select 3 months (= a quarter) in Period to download you get daily data for that period. Do you expect something else? If so clarify please

[#17]

@Lorenzo 

Can you please provide more choices of "period to download" like 3 years, 5 years

Best regards/Ricky

[#18]

@LoRic04122002 

Added 3 years, 4 years &  5 years

If you (or anybody else) need something more specific options please refer to How to Load Historical Stock Prices from Yahoo Finance then, in the attached workbook, update the following section (last 2 lines) in query NewHistoryDownload:

// Yahoo section
    YahooBaseUrl = "https://query1.finance.yahoo.com/v7/finance/chart/",
    YahooUrlEnd = "?range=[Yahoo_Period]&interval=1d&indicators=quote&includeTimestamps=true",      // [Yahoo_Period] isn't valid, replaced later
    YahooPeriods = {"1d", "5d", "1mo", "3mo", "6mo", "1y", "2y", "3y", "4y", "5y", "ytd", "max"},
    UserPeriods  = {"1 day", "5 days", "1 month", "3 months", "6 months", "1 year", "2 years", "3 years", "4 years", "5 years", "YTD", "max"},
//

[#19]

@Lorenzo 

Thank you very much for your effort and prompt reply, its works really well 😉 

[#20]

@LoRic04122002 

Glad I could help & Thanks for the confirmation (can help others...)