I have some (daily) Excel time series data of portfolio values, and I want to convert it into quarterly data, where the value for the quarter should be the value for the last day (last business day, but there are no other in the time series). One can organize things by quarter by the magic of Pivot tables, but none of the summarizing mechanisms (count, sum, max, min...) seem to apply. Am I missing something obvious?
0
votes
1 Answers
2
votes
Add a helper column in the source data that indicates whether the row is for the last day in the month. Quick search for "excel last day of quarter" uncovers this possible formula: =DATE(YEAR(A2),CEILING(MONTH(A2),3)+1,0).
So if you put "Last day of Quarter" as a label in col X1, then the formula in X2 to return a true or false value could be =A2=DATE(YEAR(A2),CEILING(MONTH(A2),3)+1,0).
Now construct a pivot table that uses the helper column X as a filter condition.
