Forum Discussion
Pull Yearly CAD/USD Averages Into Simple Table
I'm looking to pull in the monthly average of CAD to USD exchange rates into a nice monthly table without having to update it. I can currently pull in the daily numbers, going back a year using TODAY...
It depends on what is the data source and what is desired output. If take exchange rate with STOCKHISTORY and take average only for the trading dates in the month
it could be
=AVERAGE(STOCKHISTORY($B3, EOMONTH(C$2,-1)+1, EOMONTH(C$2, 0), 0, 0,1))- Is there a way to write this formula so that it only covers specific dates. For instance I wanted to get the average from 8/1/2024 thru 8/23/2024? Thanks
- I think I got it. =AVERAGE(STOCKHISTORY($B4, DATE(2024,8,1)+1, DATE(2024,8,23), 0, 0,1))
Yes, something like this. The only DATE(2024,8,1) if first date is Aug 1st. Please not, only trading dates are returned. If, for example, you'd like to take exchange rate from Sep 01 to Sep 01 and error will be returned since 01st is not the trading date.
From Sep 01 to Sep 08 only exchange rates from Sep 02 to Sep 06 are returned.
If weekends and bank holidays are to be covered, that's also possible but formula will be more complicated.