Thanks Jeff. You could adapt the formulas used to calculate the Quarterly performance seen here. For example, H1 2021 would be as follows:
|
1 2 3 4 5 6 7 8 9 10 11 |
//Get monthend; M1 = MONTHNUM() == 6 and YEARNUM() == 2021; //Get previous monthend; M2 = MONTHNUM() == 12 and YEARNUM() == 2020; //Get closing values; V1 = VALUEWHEN(M1); V2 = VALUEWHEN(M2); //Calculate % change; ((V1/V2)-1) |
For H2 2021 change M1 to MONTHNUM() == 12 and YEARNUM() == 2021; and M2 to MONTHNUM() == 6 and YEARNUM() == 2021;
The Current Half would be M1=CLOSE(); and M2 = MONTHNUM() == 12 and YEARNUM() == 2021; before the end of June, and then M2 = MONTHNUM() == 6 and YEARNUM() == 2022; afterwards.
Here’s the SPDR ETFs (workbook attached):
