IFERROR with STOCKH (Accounting for Weekends)

TimDanaher wrote:

I couldn't for the life of me, though, work out why STOCKH doesn't return Friday's closing price for Saturday and Sunday, because that's what the stock price is on Saturday and Sunday, but I see now that It allows me to preserve Friday's price change across the weekend.

Note how the 'My Stocks' template at File > New in your menu so neatly charts historical prices by using a filter on the the table for days that do not have price data.

For your specific problem, where you want historical prices on a specific dates, note that you can also use nested IFERRORs to "crawl back" if necessary to the latest previous day with price data.

All IFERROR does is try another calculation if the first one results in an error, as when STOCKH finds no price data.

For example, to crawl back from Saturday or Sunday to the previous Friday, you can do this:

=IFERROR(STOCKH("AAPL",0,B2),IFERROR(STOCKH("AAPL",0,B2−1),STOCKH("AAPL",0,B2−2)))

Or if you have a long weekend, this will crawl back from Monday to the previous Friday:

=IFERROR(STOCKH("AAPL",0,B2),IFERROR(STOCKH("AAPL",0,B2−1),IFERROR(STOCKH("AAPL",0,B2−2),STOCKH("AAPL",0,B2−3))))

If for some reason you have to handle cases where trading is shut down for longer than three consecutive days then you could simply nest more IFERRORs.

If you want it all in one formula you could substitute the relevant EDATE expression for B2.

SG

You don't need IFERROR to get the last working day for STOCKH.

You can use WORKDAY then Numbers will do all the calculations

B2 = WORKDAY($A2+1,−1) This will return the last workday, even the date is Saturday or Sunday

C2 = STOCKH("AAPL",0,WORKDAY($A2+1,−1))

Based on your region the , or the ; will be used to separate the different sections of a formula. If you write one thousand as 1,000.00 then the , is used as your formula separator. If you write one thousand as 1.000,00 then the ; is used as your formula separator.

Hope this will solve your question, please let me know if this worked for you or if something in unclear.

Ralf

This will return the last stock price

STOCKH("AAPL",0,WORKDAY(TODAY()+1,−1)) even today is Saturday or Sunday

Ralf

The lower table should work on Tuesday / Monday night :-)

How would numbers know the stock price for Monday 08 Aug. ?

Monday is a workday, therefore it will not display the value for Friday.

But it will not get a result for this day and therefore it will give you an error.

I would recommend that you display the date that you want, with name of the date.

Also display the workday that is really used to pull the stock price.

This will help to understand if there is an error in your formulas.

As soon as everything is working you could hide the additional information.

Ralf

Clarification:

08 Aug is a workday, therefore it will not change.

WORKDAY will check if the date is Saturday / Sunday and then pick Friday as the return date.

STOCKH will try to return the stock price for the date that is used.

If no stock price can be found it will return an error.

Well, have you tried my nested IFERRORs solution posted above?

It is generalized to handle any day with no trading, whether on the weekend or not.

I think you will find it to be a more robust approach than one involving WORKDAY.

SG

No more complicated than a formula trying (and probably failing) to get WORKDAY to do what you want!

Suggest starting with a table exactly as in the example. Once it’s working, you can incorporate the same approach in your table.

SG