Log In
Register
Home
New Member
April 8, 2020
Offline
Hello !
I'm trying to do some analysis on a trade report from my brokerage. See attached xls. I have created a simple pivot table (tab titled Pivot Table) to list the various buy types and the corresponding profit. When I right click and select "Show Details" on the profit column (cell B5 in the tab titled Pivot Table), it will show the details on a new worksheet (I have renamed this tab to "Current").
But, can I update "Show details" to list each of the matching row and the row immediately below ? I have created a new tab titled "Ask" to reflect my expected results.
Appreciate any feedback or thoughts on this.
Thanks !
July 16, 2010
Offline
Hi and welcome to our forum!
The double click on a PivotTable cell will only display the items specific to that cell. You can't 'update' it as such to show something different. You'd need that data also in the PivotTable and summarised to a cell where when you double clicked it, it showed you the values you want to see in your 'Ask' example.'
If you change the filter on the row labels to include 'Buy' and 'Sell' items, then double click the Grand Total cell, you get close to your 'Ask' example.
Mynda
New Member
April 8, 2020
Offline
Hi Mynda,
Thanks for your feedback !
I'm planning to move the sell row information to the right of the buy row, so that the info for each trade is available in one row. I will run pivot table on this post-processed table.
I'm going to find out how to write a VBA macro for this. This should be easy to write, so that I can run it every time I get a new trades list xls and then kick off the pivot table based analysis.
If you have better ideas on how to do this or if you are aware of a VBA macro that already does this, please point me to the same.
July 16, 2010
Offline
If your # column had the same number for the buy and sell data then you could use that to group the rows in the PivotTable. See attached on the 'Ask' sheet. Note: I would avoid writing VBA to do this. Far better to fix your source data so that it supported doing this in a PivotTable, than try and accommodate the missing data.
Mynda
1 Guest(s)
