1. Did I understood correctly - "savings" account is an account where there are just deposits, withdrawals and interest payments. There are no dividends, to symbols held, right? Correct, I track a savings account. V2 shows the interest in the dividend column and calculates a profit %. V3 shows the interest in the dividend column but does not calculate the profit %. 2. What report (worksheet+report) you sa 4.38% vs 2.08% profit? Did both versions had the same filter - example "Mths-Initial"=No? If no, can you match filters between report versions? In V2 holdings tab if I select all account the profit is calculated to be 2.08%. In V3 Reports worksheet holdings tab with all accounts selected the profit calculates to be 4.48%. The filters appear to be the same yet the two version do not calculate the same profit. In the holdings tab, if I select each individual account one at a time and compare the two versions, the profit % matches (except for the savings cash account as noted above). Only when I select all accounts to roll the entire portfolio up do I see the two different numbers for profit %. If I select all the accounts and deselect the two cash only accounts and an accounts that I sold all the assets the V3 and V2 profit % match. It seems that when I add cash accounts and an account that all assets were sold off in February the calculations do not match. At all time the ROR calculations match in V2 and V3.
3. In the monthly report mismatch is most likely due to difference in my calculation method. Did you try to see if v3.0 is more accurate, or v2.x? When I calculate manually it seems that V3 is more accurate.
Quick Question - The doughnut charts on 'Holdings' tab are not filtered by the 'SymbolGrp1' nor the 'Portfolio' slicers. I've added them in now, but have I unknowingly broken something??
Hello nice improvements to this version. One thing that I noticed is that the Holdings tab on Power BI doesn't allow for selections by Sector. It doesn't seem to change anything on the table of securities. It works well when selecting pies within Account and Symbol charts. I even checked the demo file and see that same issue. Appreciate your feedback.
@randywc - #1 - it appears to be my bug and I'll investigate #2 - there is a change how this is calculated in v3. In v2 old filter was for "Total Value">0. So symbols with value 0 were not included. In v3 new filter is Qty Held <> 0 and there is an adjustment for "* Cash" symbol to always have qty 1. In v3, when Profit% is calculated , I added additional logic for the All level to ignore that filter. So in V3 your Profit % in total line shows profit % for all symbols, where in v2 it shows Profit % just for symbols that currently have Total Value >0. But in V2 there was also a bug when not always this value was properly calculated. #3 - as v3 is more accurate this should be considered an improvement.
@FoodIsMyPriority - you are right, these doughnut charts ignore slicer selections. I will fix that. You can quickly fix this on your workbook: 1. Select slicer. 2. Select menu Options, then "Report Connections" 3. Select checkbox next to all pivot tables/charts in Holdings sheet: Thanks for reporting this!
Regarding 64/32bit office. I just had to setup new PC and took me a while to notice that default Office installation was 32bit. That was very much Microsoft "feature"/"bug" - during installation they never asked what version I want. I had to reinstall everything. But have you also tried to install this: https://www.microsoft.com/en-gb/download/details.aspx?id=13255 ? But I am not sure if that would help.
@thedesi - you are right about this behavior, but this is by design. As each symbol can be assigned to multiple sectors, for performance reasons I created a separate measure (calculations) "Total Value by Sector" and this way broke "backward" relationship to Symbol, so Symbol is not filtered when SymbolSector is selected. I would suggest using SymbolGroup1..3 attributes on the Symbol table for additional symbol groupping.
Thanks Vidas I've used PortfolioSlicer but I'd admit I'm not as loyal user as others on the board but I see that previous version (v2.2) allows for this selection where the stock holdings are presented as a ratio of the allocation to the selected sector. Is this new for version 3?
Cash Balance is off in version 3, what i found was that Accrued interest is not be added.
Accrued Interest in V3 is not working the same as in V2. In V2 the accrued interest is added to the cost of the transaction and the amount is deducted from * cash when buying. In V3 the amount is not adding to the cost making the cost lower by that amount therefore cash balance is off by the amount of the accrued interest.
In both version the transaction table are the same. CashBalance are the same V2 V3
In the Data Model they are different. V2 V3
The Data Model in V3 does not have a column for Accrued Interest.
Further in the PSData files there are two files one Transactions and One TransactionsinExcel, not sure of the reason or how they work. In the TransactionsInExcel, row one there is a header AccruedInterest and in the Transaction file there is no AccruedInterest header.
Hi, first BIG THANK YOU for all the efforts, Vidas. Great job! I have tried the V3 Power BI and came to one "show stopper", probably with easy solution:
In Spain we use "," as separator. After running the batch file, some csv file were exported with "," instead of "." and then powerbi did not read it correctly. I replaced on several .csv "," by "." and solved. The affected files were: SymbolSector, SymbolAllocation, Symbol, CompareTo, Allocation. Changing the delimiter in batch file did not help.
@wgh239 - it was stated in the release notes here (3rd bullet point) that AccuredInterest is not functional in the v3.0. My understanding was that this was used by very few people but was causing performance issues, so this was not implemented in v3.0
@thedesi - sorry, I missed your question before. This was functionality changed in v3.0 as many users required the option to have one symbol participate in multiple allocations.
Comments
2. What report (worksheet+report) you sa 4.38% vs 2.08% profit? Did both versions had the same filter - example "Mths-Initial"=No? If no, can you match filters between report versions? In V2 holdings tab if I select all account the profit is calculated to be 2.08%. In V3 Reports worksheet holdings tab with all accounts selected the profit calculates to be 4.48%. The filters appear to be the same yet the two version do not calculate the same profit. In the holdings tab, if I select each individual account one at a time and compare the two versions, the profit % matches (except for the savings cash account as noted above). Only when I select all accounts to roll the entire portfolio up do I see the two different numbers for profit %. If I select all the accounts and deselect the two cash only accounts and an accounts that I sold all the assets the V3 and V2 profit % match. It seems that when I add cash accounts and an account that all assets were sold off in February the calculations do not match. At all time the ROR calculations match in V2 and V3.
3. In the monthly report mismatch is most likely due to difference in my calculation method. Did you try to see if v3.0 is more accurate, or v2.x? When I calculate manually it seems that V3 is more accurate.
I just wanted to highlight a powershell gotcha I ran into.
I am currently running on 64bit OS & 32bit Office (don't ask me why...)
Running the 'UpdatePSDataFromExcel.bat" gave me the following error:
In order to fix it I had to change your script to execute the 32 bit version of powershell rather than the 64bit
Changed: "powershell" to "%SystemRoot%\syswow64\WindowsPowerShell\v1.0\powershell.exe" as seen in the next 2 images.
Great work on v3. Thanks again.
#1 - it appears to be my bug and I'll investigate
#2 - there is a change how this is calculated in v3.
In v2 old filter was for "Total Value">0. So symbols with value 0 were not included.
In v3 new filter is Qty Held <> 0 and there is an adjustment for "* Cash" symbol to always have qty 1.
In v3, when Profit% is calculated , I added additional logic for the All level to ignore that filter. So in V3 your Profit % in total line shows profit % for all symbols, where in v2 it shows Profit % just for symbols that currently have Total Value >0. But in V2 there was also a bug when not always this value was properly calculated.
#3 - as v3 is more accurate this should be considered an improvement.
Thanks again for the feedback!
1. Select slicer.
2. Select menu Options, then "Report Connections"
3. Select checkbox next to all pivot tables/charts in Holdings sheet:
Thanks for reporting this!
Regarding 64/32bit office. I just had to setup new PC and took me a while to notice that default Office installation was 32bit. That was very much Microsoft "feature"/"bug" - during installation they never asked what version I want. I had to reinstall everything.
But have you also tried to install this: https://www.microsoft.com/en-gb/download/details.aspx?id=13255 ? But I am not sure if that would help.
I've used PortfolioSlicer but I'd admit I'm not as loyal user as others on the board but I see that previous version (v2.2) allows for this selection where the stock holdings are presented as a ratio of the allocation to the selected sector. Is this new for version 3?
Accrued Interest in V3 is not working the same as in V2. In V2 the accrued interest is added to the cost of the transaction and the amount is deducted from * cash when buying. In V3 the amount is not adding to the cost making the cost lower by that amount therefore cash balance is off by the amount of the accrued interest.
In both version the transaction table are the same. CashBalance are the same
V2
V3
In the Data Model they are different.
V2
V3
The Data Model in V3 does not have a column for Accrued Interest.
Further in the PSData files there are two files one Transactions and One TransactionsinExcel, not sure of the reason or how they work. In the TransactionsInExcel, row one there is a header AccruedInterest and in the Transaction file there is no AccruedInterest header.
first BIG THANK YOU for all the efforts, Vidas. Great job!
I have tried the V3 Power BI and came to one "show stopper", probably with easy solution:
In Spain we use "," as separator. After running the batch file, some csv file were exported with "," instead of "." and then powerbi did not read it correctly. I replaced on several .csv "," by "." and solved. The affected files were: SymbolSector, SymbolAllocation, Symbol, CompareTo, Allocation. Changing the delimiter in batch file did not help.
Thanks,
Antoni