Hi,
In many respects I’m really impressed with PP, though I have several portfolios so wish it featured an option whereby taxonomies could be filtered by just one security account. With this in mind I’ve been playing about with the SQLite version of PP – kudos to the developer of GitHub - pfalcon/ppxml2db: Scripts to import PortfolioPerformance (https://github.com/portfolio-performance/portfolio) XML into an SQLite DB and export back - and have created an SQL query which I reckon overcomes this limitation.
I’m happy to share this with you as is. It’s a relatively complex query, but famous last words, should run on any SQLite version of PP without the need for any modifications or alterations.
Since it caters for the potential of the more than one security account as well as each security having multiple taxonomies at first glance the query output may appear to be full duplicates, but this is specifically intended so as allow for each unique taxonomy. The column headers/field structure of the query is as follows:-
• Account – a listing of each security account
• Security (Name)
• ISIN
• Currency (Security)
• Net Cost
• Taxes/Fees (of acquisitions and any disposals)
• Current Value
• Net Gain/Loss
• ∑ Div (Dividend payments received)
• Dividend Taxes/Fees.
• Taxonomy – a listing of each taxonomy
• Level 1 – the first level of each taxonomy
• Level 2 – the second level, if any, of each taxonomy.
• Assignment Weight
• Weight Value
The process of converting the PP XML file to a SQLite data file is incidentally quick n’ easy. Good guidance is provided by the developer of ppxml2db, and if you’re using Windows a batch file may be needed as explained here Portfolio Performance database – ppxml2db import/export - #21 by flywire – big thanks for that @Flywire. Once ppxml2db has created your SQLite data file you could load it into DB Browser for SQLite (https://sqlitebrowser.org/) or something similar and then execute the query, which again is quick n’ easy.
Finally, just to add that I hope this query elevates the value of using Ppxml2db. Creating the thing has driven me somewhat insane 
, so if there’s any interest in evolving it further maybe someone else will take that forward instead
Anyway, enough said:-
Select
account.name As Account,
security.name As Security,
security.isin As ISIN,
security.currency As Currency,
Sum(Case
When xact.type In ('BUY', 'DELIVERY_INBOUND')
Then Cast(xact.shares As Real) / 100000000.0
Else 0
End) - Sum(Case
When xact.type In ('SELL', 'DELIVERY_OUTBOUND')
Then Cast(xact.shares As Real) / 100000000.0
Else 0
End) As Shares,
Sum(Case
When xact.type In ('BUY', 'DELIVERY_INBOUND')
Then Cast(xact.amount As Real)
Else 0
End) / 100 - Sum(Case
When xact.type In ('SELL', 'DELIVERY_OUTBOUND')
Then Cast(xact.amount As Real)
Else 0
End) / 100 As "Net Cost",
Sum(Case
When xact.type In ('BUY', 'DELIVERY_INBOUND')
Then Cast(xact.fees As Real) + Cast(xact.taxes As Real)
Else 0
End) / 100 - Sum(Case
When xact.type In ('SELL', 'DELIVERY_OUTBOUND')
Then Cast(xact.fees As Real) + Cast(xact.taxes As Real)
Else 0
End) / 100 As "Taxes & fees",
Round(Case
When security.currency = 'GBX'
Then (Sum(Case
When xact.type In ('BUY', 'DELIVERY_INBOUND')
Then Cast(xact.shares As Real) / 100000000.0 * latest_price.value / 10000000000.0
Else 0
End) - Sum(Case
When xact.type In ('SELL', 'DELIVERY_OUTBOUND')
Then Cast(xact.shares As Real) / 100000000.0 * latest_price.value / 10000000000.0
Else 0
End))
Else (Sum(Case
When xact.type In ('BUY', 'DELIVERY_INBOUND')
Then Cast(xact.shares As Real) / 10000000.0 * latest_price.value / 1000000000.0
Else 0
End) - Sum(Case
When xact.type In ('SELL', 'DELIVERY_OUTBOUND')
Then Cast(xact.shares As Real) / 10000000.0 * latest_price.value / 1000000000.0
Else 0
End))
End, 2) As "Current Value",
Round(Case
When security.currency = 'GBX'
Then ((Sum(Case
When xact.type In ('BUY', 'DELIVERY_INBOUND')
Then Cast(xact.shares As Real) / 100000000.0 * latest_price.value / 10000000000.0
Else 0
End) - Sum(Case
When xact.type In ('SELL', 'DELIVERY_OUTBOUND')
Then Cast(xact.shares As Real) / 100000000.0 * latest_price.value / 10000000000.0
Else 0
End)))
Else ((Sum(Case
When xact.type In ('BUY', 'DELIVERY_INBOUND')
Then Cast(xact.shares As Real) / 10000000.0 * latest_price.value / 1000000000.0
Else 0
End) - Sum(Case
When xact.type In ('SELL', 'DELIVERY_OUTBOUND')
Then Cast(xact.shares As Real) / 10000000.0 * latest_price.value / 1000000000.0
Else 0
End)))
End - (Sum(Case
When xact.type In ('BUY', 'DELIVERY_INBOUND')
Then Cast(xact.amount As Real)
Else 0
End) / 100 - Sum(Case
When xact.type In ('SELL', 'DELIVERY_OUTBOUND')
Then Cast(xact.amount As Real)
Else 0
End) / 100), 2) As "Net Gain/Loss",
Round(IfNull(divs."Div Payments", 0), 2) As "∑ Div",
Round(IfNull(divs."Div taxes/fees", 0), 2) As "Div taxes & fees",
Coalesce(taxonomy.name, "Without Classification") As Taxonomy,
Case
When taxonomy_category1.name = taxonomy.name
Then taxonomy_category.name
Else taxonomy_category1.name
End As "Level 1",
Case
When taxonomy_category1.name <> taxonomy.name
Then taxonomy_category.name
Else ''
End As "Level 2",
IIF(taxonomy_assignment.weight > 0, 1.0 * taxonomy_assignment.weight / 100, Null) As "Assignment Weight (%)",
Case
When taxonomy_assignment.weight = 0 Or taxonomy_assignment.weight Is Null
Then Null
Else Round(Case
When security.currency = 'GBX' And taxonomy_assignment.weight > 0 And taxonomy_assignment.weight <=
10000
Then (Sum(Case
When xact.type In ('BUY', 'DELIVERY_INBOUND')
Then xact.shares
Else 0
End) - Sum(Case
When xact.type In ('SELL', 'DELIVERY_OUTBOUND')
Then xact.shares
Else 0
End)) / 100000000.0 * latest_price.value / 10000000000.0 / 100 * taxonomy_assignment.weight / 100
When taxonomy_assignment.weight > 0 And taxonomy_assignment.weight <= 10000
Then (Sum(Case
When xact.type In ('BUY', 'DELIVERY_INBOUND')
Then xact.shares
Else 0
End) - Sum(Case
When xact.type In ('SELL', 'DELIVERY_OUTBOUND')
Then xact.shares
Else 0
End)) / 10000000.0 * latest_price.value / 1000000000.0 / 100 * taxonomy_assignment.weight / 100
Else Null
End, 2)
End As "Weight Value"
From
xact Left Join
security On xact.security = security.uuid Left Join
account On xact.account = account.uuid Left Join
taxonomy_assignment On taxonomy_assignment.item = security.uuid Left Join
taxonomy On taxonomy_assignment.taxonomy = taxonomy.uuid Left Join
taxonomy_category On taxonomy_assignment.category = taxonomy_category.uuid Left Join
taxonomy_category taxonomy_category1 On taxonomy_category1.uuid = taxonomy_category.parent Left Join
latest_price On latest_price.security = security.uuid Left Join
(Select
xact.security,
Sum(Cast(xact.amount As Real)) / 100 As "Div Payments",
Sum(Cast(xact.fees As Real)) + Sum(Cast(xact.taxes As Real)) As "Div taxes/fees"
From
xact
Where
xact.type = 'DIVIDENDS'
Group By
xact.security,
xact.account) As divs On divs.security = xact.security
Where
xact.acctype = 'portfolio' And
security.isRetired = 0
Group By
account.name,
security.name,
security.isin,
security.currency,
taxonomy.name,
taxonomy_category.name,
taxonomy_category1.name,
taxonomy_assignment.weight,
latest_price.value
Having
(Sum(Case
When xact.type In ('BUY', 'DELIVERY_INBOUND')
Then Cast(xact.shares As Real)
Else 0
End) - Sum(Case
When xact.type In ('SELL', 'DELIVERY_OUTBOUND')
Then Cast(xact.shares As Real)
Else 0
End)) <> 0
Order By
Taxonomy,
"Level 1",
"Security Name"