qosmioamit - I love PowerBI too, as it refreshes faster because 1. Engine is more up to date and optimized and that means FASTER 2. Refreshes just the reports you see on that page, so feels much faster than Excel that refreshes ALL reports in ALL workbooks any time you hit "Refresh". But Excel has its own benefit, so I am often using it too.
Vidas, I copy/pasted the data by mistake. I went back to your original worksheet and copy/paste special values the data to solve the issue. The highlighting is now gone. Thanks again and have a great weekend!
I have a couple of gotchas for users that I observed: 1) The spreadsheet allows splitting a symbol to multiple allocations in the "SymbolAllocation" tab, e.g. VT allocates about 50% to USA and only 2.5% to Canada. The caveat is that if this tab is used, then one must ensure that the sum of the allocations is 100%. There is no warning on this sheet if the 100% is not done. 2) The spreadsheet now allows comparing with multiple global benchmark indices in the "CompareTo" tab, but the caveat is the index must be listed in the "Symbol" and "SymbolSector" tab. However, if one does not, then there is no warning in the "CompareTo" tab.
An enhancement request with respect to "Compare To" in PowerBI is to use a bar graph like this for each of the last 5 years. The table below is from the Excel Report, but I think it would be awesome to do a table like this for 2021, 2020, 2019, 2018, 2017, 2016..etc. Previously this was done with just one bar graph but it became very busy. By splitting it year-by-year, it can even fit into phones!
@qosmioamit - Are you looking for 5 separate bar graphs or one table that has the same data by year? if you are looking for 5 separate bar graphs, then simply attach a slicer to this one graph, remove other Date filters, and then by selecting the year in the new slicer you will see results for a year you want. Keep in mind that I provide just initial reports - you can build your own report any way you want!
Yes, I am looking for 5 separate graphs, one for each year. While I have a basic understanding of charts, PowerPivot and PowerBI are out of my prowess for the time being. I will look into it though because if the data is available, it shouldn't be too difficult to chart.
@VidasMatelis I have made an observation that I hope you can help me understand. It has to do with quantities of partial shares that are typical with mutual funds. To follow along, please use the source excel sheet in the demo folder of the v3 beta package.
First: In the Transactions tab for W-RRSP-CAD account: 1) Add a DivTA transaction - TDB8150 - Qty 4580.58 - Price $1000 2) Add a BuyTA transaction - TDB8150 - Qty 100.112 - Price $350 3) Add a Sell transaction - TDB8150 - Qty 4680.69 (as shown in QtyHeld column) - Price 10.50 After doing so, QtyHeld should state 0.
Second: If you refresh all and check the SrcReview tab - TDB8150 will have 0.88 units
Now, go back and modify the Sell transaction to Qty 4681.572 (4680.69+0.88) and refresh all and you will notice 2 things: 1) Qty Held is -0.88 in Transactions 2) srcReview tab shows current qty 0 but if you click on the cell C7, it will show 9.09e-13 in the formula bar
Third: Obviously, 4580.58 + 100.112 = 4680.692 If you change the qty of the sell transaction to 4680.692, the qty held should state 0 but the srcReview tab will still have 0.88 units after a refresh.
First I assume your first transaction is BuyTA and not DivTA. After first 2 transactions your actual qty held is 4680.692 QtyHeld column round values when it shows them. So even it shows 4680.69, it is actually is 4680.692. If you deal with partial shares, select that column and then from home menu click on the button "Increase decimals". Note - that just changes decimals you see, actual stored value is exactly as you entered!
You need to track your qty precise, otherwise you will see not 0 qty held values after the sale and that should not be happening. If you do as per your "Third" example, you should see 0 after refresh. Increase decimals, confirm numbers and then do refresh and see if that works.
I am using the demo file, so it already has several preloaded transactions. There are buy transactions for the symbol. I don't think increasing the decimal is the problem. Here's a screenshot:
1. that first 4580.58 transaction is actually 4580.582 2. After you sold in 2021-01-21, there is another transaction in 2021-02-01 - buy 0.878 - you have to add that to your transaction list and that is why you see CurrentQty not 0. So remove transaction from 2021-02-01 (Buy/Drip, Qty: 0.878) and you will see correct numbers. Note - you might have some "Visual" filters if you do not see last buy transaction - please remove column filters and check.
Yes, you were right about that transaction on 2021-02-01 for buy 0.878. I deleted that transaction. I have also sorted by date. But SrcReview still shows some qty.
Hello Vidas Thank you for another great update. I updated to V3 and continue to run V2.4.08 along side V3 for comparison. I am seeing the % fees calculating a different number. On V2 it was 0.79% and V3 it is 1.19% for my total portfolio. When I manually calculate, it seems that in V3 it does not include cash accounts in the calculation. I also don't see a %profit for a cash account that gets interest (saving account)
Another difference is in V2 (for total portfolio) V2 shows a profit % of 2.08%. V3 show a profit % of 4.38%. If I go through account by account the profit % matches in both V2 and V3 but not on total portfolio...all tabs selected.
One other item I noticed is the % profit calculated for a specific symbol does not match V2 vs V3 in the monthly symbol tab. V2 shows profit of -0.56% and V3 show profit of 0.70%....I attached the screen shot
This may be operator error, but I can't figure out the differences I am seeing.
@qosmioamit - I see this rounding issue, but but is just Excel "issue" in this Pivot table. But this is very tiny rounding error and should be OK for most people. When this data is loaded into PowerPivot model (Portfolio Slicer) there are some rounding applied, so it should behave better.
@randywc - Profit % was totally rewritten - in v2 it was calculated monthly and then aggregated, in v3 it is calculated using transaction date points of each transaction that impacts profit and then aggregated. In v3 calculation should be more accurate but as I said, it is expensive. Still, Profit % is OK for a short period comparison when there are not a lot of changes, but for any year+ duration just "Annlzd RoR" should be used. As I changed Profit % formula, it is very possible that I introduced errors, so thank you very much for reporting them. Can you please clarify your issues: 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? 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? 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?
Comments
But Excel has its own benefit, so I am often using it too.
1) The spreadsheet allows splitting a symbol to multiple allocations in the "SymbolAllocation" tab, e.g. VT allocates about 50% to USA and only 2.5% to Canada. The caveat is that if this tab is used, then one must ensure that the sum of the allocations is 100%. There is no warning on this sheet if the 100% is not done.
2) The spreadsheet now allows comparing with multiple global benchmark indices in the "CompareTo" tab, but the caveat is the index must be listed in the "Symbol" and "SymbolSector" tab. However, if one does not, then there is no warning in the "CompareTo" tab.
Are you looking for 5 separate bar graphs or one table that has the same data by year?
if you are looking for 5 separate bar graphs, then simply attach a slicer to this one graph, remove other Date filters, and then by selecting the year in the new slicer you will see results for a year you want. Keep in mind that I provide just initial reports - you can build your own report any way you want!
I have made an observation that I hope you can help me understand. It has to do with quantities of partial shares that are typical with mutual funds.
To follow along, please use the source excel sheet in the demo folder of the v3 beta package.
First:
In the Transactions tab for W-RRSP-CAD account:
1) Add a DivTA transaction - TDB8150 - Qty 4580.58 - Price $1000
2) Add a BuyTA transaction - TDB8150 - Qty 100.112 - Price $350
3) Add a Sell transaction - TDB8150 - Qty 4680.69 (as shown in QtyHeld column) - Price 10.50
After doing so, QtyHeld should state 0.
Second:
If you refresh all and check the SrcReview tab - TDB8150 will have 0.88 units
Now, go back and modify the Sell transaction to Qty 4681.572 (4680.69+0.88) and refresh all and you will notice 2 things:
1) Qty Held is -0.88 in Transactions
2) srcReview tab shows current qty 0 but if you click on the cell C7, it will show 9.09e-13 in the formula bar
Third:
Obviously, 4580.58 + 100.112 = 4680.692
If you change the qty of the sell transaction to 4680.692, the qty held should state 0 but the srcReview tab will still have 0.88 units after a refresh.
Thanks
After first 2 transactions your actual qty held is 4680.692
QtyHeld column round values when it shows them. So even it shows 4680.69, it is actually is 4680.692. If you deal with partial shares, select that column and then from home menu click on the button "Increase decimals".
Note - that just changes decimals you see, actual stored value is exactly as you entered!
You need to track your qty precise, otherwise you will see not 0 qty held values after the sale and that should not be happening.
If you do as per your "Third" example, you should see 0 after refresh. Increase decimals, confirm numbers and then do refresh and see if that works.
2. After you sold in 2021-01-21, there is another transaction in 2021-02-01 - buy 0.878 - you have to add that to your transaction list and that is why you see CurrentQty not 0. So remove transaction from 2021-02-01 (Buy/Drip, Qty: 0.878) and you will see correct numbers. Note - you might have some "Visual" filters if you do not see last buy transaction - please remove column filters and check.
Thank you for another great update. I updated to V3 and continue to run V2.4.08 along side V3 for comparison. I am seeing the % fees calculating a different number. On V2 it was 0.79% and V3 it is 1.19% for my total portfolio. When I manually calculate, it seems that in V3 it does not include cash accounts in the calculation. I also don't see a %profit for a cash account that gets interest (saving account)
Another difference is in V2 (for total portfolio) V2 shows a profit % of 2.08%. V3 show a profit % of 4.38%. If I go through account by account the profit % matches in both V2 and V3 but not on total portfolio...all tabs selected.
One other item I noticed is the % profit calculated for a specific symbol does not match V2 vs V3 in the monthly symbol tab. V2 shows profit of -0.56% and V3 show profit of 0.70%....I attached the screen shot
This may be operator error, but I can't figure out the differences I am seeing.
When this data is loaded into PowerPivot model (Portfolio Slicer) there are some rounding applied, so it should behave better.
As I changed Profit % formula, it is very possible that I introduced errors, so thank you very much for reporting them.
Can you please clarify your issues:
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?
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?
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?