Report that can filter holdings by Security

gsbaker

The "Holdings" report allows filtering based on Account - i.e. it allows one to see the detail of security holdings in each account (or in multiple accounts). Is there an easy way to filter based on Security (or Symbol) to show what Accounts hold a particular Security?

I have tried modifying a copy of the Holdings tab and adding the "Account" field into the Pivot table and adding a Slicer for Security. However this ends up showing a line for every Account for each Security. So if I have 10 accounts, this report shows 10 rows for every Security. The rows (Accounts) where the quantity held for that security is blank (other than the Account Name). I have not been able to figure out how to show ONLY those accounts where quantiy held > 0.

Thanks.

VidasMatelis

You should be able to filter out rows where qty held >= by using Excel functionality.
Example is in "Sales" worksheet where Symbol is filtered for "Qty Bought>0".
In Excel select Symbol column, then in the header click on Arrow down, Value Filters... and then "Greater than" and select "Qty Held" and condition "is greater than" value 0.

gsbaker

This seems to work, but only if a single Security is selected in the Security slicer. Here is what I tried to do (using a copy of Holdings tab):

1. Added the "Account" field to the ptHoldings pivot table as the last item in the Rows section, and removed the Account slicer (since I want the report to show all Accounts)
2. Added a "Symbol" slicer to enable selecting specific Symbols
3. Selected the "Account" column header in the pivot table and added a Value filter for "Qty Held is greater than 0"

This works fine when a single Symbol (which is held in 2 accounts) is selected in the slicer (see below).





However when all Symbols are selected (or even multiple Symbols) the report includes blank lines - one for each account that holds one of the other Securities - see example below where 2 securities have been selected in the Security slicer. Note the blank line(s) under CA.BIP.UN for the J-CD-Cash-CAD account, and under CA.EMP.A for the R-G-RSP and R-J-RSP accounts (which hold the other selected security).




I also tried adding a "Qty Held is greater than 0" Value filter to the "Security" column in the pivot table, but that did not change anything.

Also note, I am currently using v 2.4.05 of the PS spreadsheet.

VidasMatelis

I tried to reproduce this report. It appears that the problem is when report has "Symbol Price" as one of the columns. If you remove it, then you should see what you want - could you please try and let me know here if it works for you.

VidasMatelis

Also, if above suggestion does not work, instead of applying filter to "Account" column, please try to apply filter to "Symbol" column and see if that helps.

gsbaker

Thanks. Removing "Symbol Price" from the columns did the trick. To further improve the report (and make it reconcilable to the original Holdings report) I changed the value filter on the "Account" column to "Total Value > 0" so that includes "* Cash". Did not need any filter on the "Security" or "Symbol" columns.

Thanks for your help.