Hello,
I have a long list of over 10 years of daily data across several accounts. The data is in two columns [A = Date, B = Value]. I was wondering if its possible to create a quick and easy pivot report to calculate the % change on a monthly basis. For example, say the beginning value on 1/1/2013 = 1,000 and on 1/2/2013 = 1,927 then the monthly change is calculated as (end of month / end of previous month -1). I can calculate all this manually, but it is a bit tedious across so much data.
INPUTS
<tbody>
</tbody>
The final output I wanted was a table with columns of years and rows of the month and the monthly change tabulated and calculated by a pivot table.
OUTPUTS
<tbody>
</tbody>
Many thanks for your help.
qazqazie
I have a long list of over 10 years of daily data across several accounts. The data is in two columns [A = Date, B = Value]. I was wondering if its possible to create a quick and easy pivot report to calculate the % change on a monthly basis. For example, say the beginning value on 1/1/2013 = 1,000 and on 1/2/2013 = 1,927 then the monthly change is calculated as (end of month / end of previous month -1). I can calculate all this manually, but it is a bit tedious across so much data.
INPUTS
Date | value | performance |
31-Dec-2012 | 1,000 | |
01-Jan-13 | 997 | |
02-Jan-13 | 1,000 | |
…..etc….. | …..etc….. | |
30-Jan-13 | 1,912 | |
31-Jan-13 | 1,927 | 92.7% |
01-Feb-13 | 1,931 | |
02-Feb-13 | 1,943 | |
…..etc….. | …..etc….. |
<tbody>
</tbody>
The final output I wanted was a table with columns of years and rows of the month and the monthly change tabulated and calculated by a pivot table.
OUTPUTS
| |||||
Year | Jan | Feb | Mar | …………. | Dec |
2013 | 93% | 13% | -7% | etc. | etc. |
2014 | etc. | etc. | etc. | etc. | etc. |
<tbody>
</tbody>
Many thanks for your help.
qazqazie