Excel VBA StockHistory To Array - Date Only Error

[ConCat]

Hi

This is more of an observation through trial and error more than a question.


General formula:

• StockHistory(Ticker, Start_Date, End_Date, Frequency, Header, Property[0-5])

Frequency:

• 0 - Daily
• 1 - Weekly
• 2 - Monthly

Header:

• 0 - No Header
• 1 - Header
• 2 - Header + Ticker

Property:

• 0 - Date
• 1 - Close
• 2 - Open
• 3 - High
• 4 - Low
• 5 - Volume

Example:

Sub GetStockHistory()

    Dim Start_Date As Date: Start_Date = "01/01/2020"
    Dim End_Date As Date: End_Date = Date
    Dim Array_Date As Variant

    Array_Date = WorksheetFunction.StockHistory("MSFT", Start_Date, End_Date, 0, 0, 2, 3, 4, 1, 5, 0) 'Frequency: Daily, Header: None, Properties: Open - High - Low - Close - Volume - Date

End Sub

Problem:

Array_Date = WorksheetFunction.StockHistory("MSFT", Start_Date, End_Date, 0, 0, 0) 'Frequency: Daily, Header: None, Properties: Date (Only)

With these parameters, the last zero will only return the dates without the Open/Close/High/Low/Volume data. This gives me a "Run-time error '1004': Application-defined or object-defined error"
As I understand the problem, when it tries to return the dates without any other data. It doesn't know which date to "latch on to" as there are no other data.

First I assumed it wouldn't be a problem as it would latch the dates on to simulated data entries corresponding to which Start_Date and End_Date then populate the array with the dates, but I'm not sure if this is a bug, shortcoming or working as intended.
Anyone has some insight about this issue?


I've been helped by this forum countless times over the years. I just now decided to make an account and write a post about something. Small details in a comment, or hidden in a huge wall of text has been the key to many of my solutions. Maybe this can help someone else in the future.


Thanks for reading my first post. Have a nice day!

 

[DRSteele]

Welcome to the Forum.

I don't know if this will fix your problem, but it might help. I wrote this article to give wider exposure to the topic than just this one thread.

 

[ConCat]

Welcome to the Forum.

I don't know if this will fix your problem, but it might help. I wrote this article to give wider exposure to the topic than just this one thread.

I was having some calculation issues on some spesific dates, and now it all makes sense.
Interesting read, thank you!

 

[jdellasala]

I was having some calculation issues on some spesific dates, and now it all makes sense.
Interesting read, thank you!

It is annoying that STOCKHISTORY can't just ignore non trading days, nor can you ask for just an array of dates so that you can get valid dates. I've posted that annoyance on the Feedback site to no avail.
So I took care of it myself creating a LAMBDA function called MKTDAYS that generates an array of dates the market is open based on the start date provided and a list of days the market is closed. To get that list of dates, I created MARKETCAL which will generate an array of the 10 days the market is closed for the current year if no parameter is provided or whatever year is requested.
I also have a Power Query function that pulls the dates from this website.
Makin' lemonade!

 

[ConCat]

It is annoying that STOCKHISTORY can't just ignore non trading days, nor can you ask for just an array of dates so that you can get valid dates. I've posted that annoyance on the Feedback site to no avail.
So I took care of it myself creating a LAMBDA function called MKTDAYS that generates an array of dates the market is open based on the start date provided and a list of days the market is closed. To get that list of dates, I created MARKETCAL which will generate an array of the 10 days the market is closed for the current year if no parameter is provided or whatever year is requested.
I also have a Power Query function that pulls the dates from this website.
Makin' lemonade!

Wow!
Thanks for the great input Jerry, though it's a little above my head. I'll take the time to read through everything.

In the beginning I output the entire STOCKHISTORY array in a normal sheet.
To remove the invalid data and convert the EXCEL date format I did something like this:

On Error GoTo NextLine    'Go to NextLine as it will always return a Run-time error if no error cells exist
     ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants, xlErrors).EntireRow.Delete    'Delete all invalid dates
NextLine:

ActiveSheet.UsedRange.Columns("F").NumberFormat = "dd/mm/yyyy"     'European style format (Column "F" contained the DATE values)

It's exciting to read up on new concepts as I still have alot to learn!