STOCKHISTORY Help Needed

[marshak]

Hello all!

I need help...and fast. I am using STOCKHISTORY for the first time. My client needs the formula to show all the dates in the range (including the weekend and the holidays). On the weekends and holidays, we need the result to be a dash. How can I accomplish this? Please help!

The current formula in Column R is:
=STOCKHISTORY(H7,L56,TODAY(),)

However, I would like to get all of the dates shown in column Q.

Thanks in advance for your help.

K

 

[DRSteele]

Examine this Article here in MrExcel to see if it helps.

I know you want a dash for non-trade days, but I suggest using the last price. That way, you can use the data in calculations over various time periods. For example, what was the price 7 years ago? If that 7-year-ago data happens to be a non-trade day, well then you can at least avoid an error.

 

[marshak]

Thanks a bunch for your response. I did see that explanation and have been trying to use it, but I am still not understanding. Please help!

 

[DRSteele]

Okay, the problem is that we want to fill in the the array of data with dates that STOCKHISTORY lacks, like weekends and holidays. So we use XLOOKUP to report the last existing price. That way we can fill in all the dates from START DATE to today as well as assign prices to the days not reported by STOCKHISTORY.

The INDEX is needed because STOCKHISTORY is a dynamic array function that reports more than one column if we ask for DATE and CLOSE (or more columns).

Try to download that sample worksheet and see if you can see what the deal is.

 

[marshak]

Thanks so much! My client actually wants the weekend and holidays to just show a dash (instead of a price) -- is that possible?

 

[marshak]

The Excel example helped a lot -- thank you!! Now I need the nontrading days to display a dash (–). How can I do that? Any assistance is greatly appreciated!!

 

[marshak]

I used the IFERROR function for the dashes. Thanks again for your help!