Stockhistory formula in Excel-CodePudding

Is there a way to get this formula to return end of the month rates instead of beginning of the month? Even if I put the start and end dates to end of the month it still returns the first of each month.

I have attached a picture of the sample data:

I have tried:

=STOCKHISTORY("EUR/CAD","1/30/2022","12/31/2022",2,1)

This returns the data for 1/1/2022 to 12/1/2022. I need it to calculate month end data.

CodePudding user response：

We can return all the days then filter out the last weekday of the month:

=LET(
    h,STOCKHISTORY("EUR/CAD","1/30/2022","12/31/2022",0,1),
    d,TAKE(h,,1),
    FILTER(h,d=IFERROR(WORKDAY(EOMONTH(d,0) 1,-1),d)))

One note: If you note the close is the same as your return. That is because, even though it says the 1st day the close value is the last value of that month.

For example May: May 1st was a weekend and does not have any daily data. On May 2nd the close was 1.35. But the close on May 31st was 1.36. So the date may be the first of the month, the data is in fact the close on the last day the market was open that month.

What that means is it may be easier to keep the formula you have:

=STOCKHISTORY("EUR/CAD","1/30/2022","12/31/2022",2,1)

then format the date column:

mmm-yy

Then a day would not be distracting: