In the attached file I have a simple table consisting of 2 stock symbols and their weekly prices over the last several years.
My end goal is to create a pivot table which shows the percent change for each class and symbol for each year/month/week. I want to then to add slicers to create a column chart displaying the percent change of each class and symbol for a given year/month/week.
I am not sure of the capabilities of a pivot table to know if this is achievable with the current data. Maybe I need to feed the table data the weekly percent changes instead of prices??? I have also attached a picture of what I am trying to do.
Would someone please take a look at my data and give me some advice on how to build these pivot table(s)?
I appreciate your helping a newbie to this area of Excel!!!
Hi Michael,
In the attached file I've created a regular PivotTable with the year on year % change, but you'll see in the Slicer that you need to select at least 2 years for it to calculate and there's no easy way to switch between year/month/date views of the data as it will require editing the 'show values as' setting.
Therefore, it's probably best to use Power Pivot DAX measures with the time intelligence functions for these calculations. You'll also need to use this technique for switching measures to toggle between Year/Month/Week % Change....that said, there's no way to change the Slicer to switch from showing years/month/days. You might be best to create 3 charts with the 3 separate views.
Hope that points you in the right direction.
Mynda
Hi Mynda
Upon opening the workbook you sent, I see a 2022 percent change for SPY as -74.20%. I don't believe this calculation is correct as the S&P 500 did not loose that much in the the last 3 months.
This is my primary question...How do I make a pivot table perform the proper percent change calculation for any year. The proper calculation is (Ending value - Beginning value) / Beginning value.
So for 2022 referring to the data table I provided in my initial post, the excel formula would be (D263 - D251) / D251 or (464.65 - 459.80) / 459.80 = -1.04%
So, step one for me would be to learn how to create a pivot table that makes this calculation for the years provided.
I really appreciate you taking the time to help me and I apologize if I totally missed it in the reply you gave.
Michael
Sorry, I should have set the summarise setting to Average in the PivotTable, however, that still won't give you the calculation you want because it's not year on year, it's year start to year end of the current year and you don't want the average of all values.
In that case you will definitely need to use Power Pivot and write some DAX measures using the time intelligence functions. Be sure to finish the Power Pivot course before attempting this. If you get stuck, then come back with your Power Pivot model and measure attempts and I can help you further.
Mynda
I will check out the power pivot solution if the following cannot be done using the basic pivot table. Attached is new data with a daily returns column and a month column.
Is a regular pivot table able to perform a product calculation of the returns for each month? The proper formula is in cell F62 and I am trying to get the shown pivot table to achieve the same value in cell I8.
I feel it should be easy to use these tools to get a simple monthly return from a stock, but I am new to these and probably still don't know what I don't know and I am just not feeding them the right data???
Thanks again for your help!
Mike
Hi Michael,
You definitely can't do this with a regular PivotTable. In the file attached I've inserted a measure in a Power Pivot PivotTable.
Mynda
Thank you Mynda! Your power pivot table will be super helpful in guiding my way forward in this project.
Thanks again for taking the time to help me.
Mike
