1. In src worksheet Config table you have set TrachCash to No - please change to "Yes" and refresh. Keep in mind - you will need to make deposit cash transaction before any buy transactions! 2. In Holdings tab you had slicers selected "Year" - 2016 and "Report Currency"- "USD". So Capital Gain represents what was capital gain from Dec 31, 2015 till Dec 31, 2016. Can you give me one symbol that you think should have different Capital Gain - can you provide me what value you would expect there and how did you calculate. THen I'll try to reproduce for you how PS calculated it. 3. Few issues. First it seams that in psConfig.txt you set MinDate to 2014-12-31, but in Excel workbook Config table you set that to 2015-07-01. You need to have consistent values. Set psConfig.txt to 2014-12-31 and delete Quotes and CUrrency (Quotes and CurrExch folders) exchange values up until 2015-07-01.
After you done above, check if results are OK. If not, make a backup/copy of spreadsheet and save it (!!!), leave just deposit transaction and transaction related to one symbol and refresh. Check if results make sense. If the do, then again use spreadsheet from backup, create new version and remove different symbol and test results. Again confirm that results make sense. If you have issue with any of that - send me workbook and I'll investigate .
1. Regarding the capital gain calculation, i'm ok with the calculation method. But is it possible for me to see it based on per stock that I hold? For instance if the holding is from June 2016 - June 2017, can i somehow filter it in such a manner that I am able to view it as profits per holding. Thanks! 2. I have changed the min date to 2015-07-01 and everything seems to be working fine, but I would like to understand the interpretation of profit and YTD capital gain on Dashboard tab because it doesnt make much sense to me what it means. 3. There still seems to be some problem with Yearly tab output, especially profits, since it is derived from Capital gain which I have no idea how I got 28,698 capital gain
1. If you want to see profit per holding, I would suggest that you add Symbol slicer to that pivot table: Select any cell in that Pivot Table, choose menu "Insert", then item "Slicer", then select Symbol table and Symbol column. Now when you will select Symbol in the slicer that report will be filtered just for that symbol data. 2. Profit=CapGain+Dividends.CapGain=Price difference between period start and end. You always have to be aware of period you are looking at. Some reports filter for specific years, other reports show ending value - that mean period is not filtered. 3. Could you please try first to filter report by symbol, identify symbol that has profit that you do not agree with and I can investigate that symbol.
I have a different question but related to the Profit calculation so I'm adding it to this thread. Looking at my total portfolio without any date slicers, adding Dividends % and Capital Gains % doesn't equal Profit %. They are close, but each account within the portfolio is off by about 0.50%. Shouldn't capital gains + dividends = profit? Or is it just a rounding issue?
2. Dividend % was calculated against "Total Value" and not against "Compare to Value". Total value is the value at the end of the period. "Compare to Value" is value at the start of the period plus deposits/purchases during that period. It is a subtle difference that might or might not have an impact depending on the transactions.
3. There are some other issues, that I am investigating and will report back here when/if found.
I already updated next release formulas sot that results would be more consistent.
Comments
1. In src worksheet Config table you have set TrachCash to No - please change to "Yes" and refresh. Keep in mind - you will need to make deposit cash transaction before any buy transactions!
2. In Holdings tab you had slicers selected "Year" - 2016 and "Report Currency"- "USD". So Capital Gain represents what was capital gain from Dec 31, 2015 till Dec 31, 2016. Can you give me one symbol that you think should have different Capital Gain - can you provide me what value you would expect there and how did you calculate. THen I'll try to reproduce for you how PS calculated it.
3. Few issues. First it seams that in psConfig.txt you set MinDate to 2014-12-31, but in Excel workbook Config table you set that to 2015-07-01. You need to have consistent values. Set psConfig.txt to 2014-12-31 and delete Quotes and CUrrency (Quotes and CurrExch folders) exchange values up until 2015-07-01.
After you done above, check if results are OK. If not, make a backup/copy of spreadsheet and save it (!!!), leave just deposit transaction and transaction related to one symbol and refresh. Check if results make sense. If the do, then again use spreadsheet from backup, create new version and remove different symbol and test results. Again confirm that results make sense. If you have issue with any of that - send me workbook and I'll investigate .
1. Regarding the capital gain calculation, i'm ok with the calculation method. But is it possible for me to see it based on per stock that I hold? For instance if the holding is from June 2016 - June 2017, can i somehow filter it in such a manner that I am able to view it as profits per holding. Thanks!
2. I have changed the min date to 2015-07-01 and everything seems to be working fine, but I would like to understand the interpretation of profit and YTD capital gain on Dashboard tab because it doesnt make much sense to me what it means.
3. There still seems to be some problem with Yearly tab output, especially profits, since it is derived from Capital gain which I have no idea how I got 28,698 capital gain
Thanks once again.
Best Regards,
Ray
2. Profit=CapGain+Dividends.CapGain=Price difference between period start and end. You always have to be aware of period you are looking at. Some reports filter for specific years, other reports show ending value - that mean period is not filtered.
3. Could you please try first to filter report by symbol, identify symbol that has profit that you do not agree with and I can investigate that symbol.
I have inserted the slicer and everything works fine now. Thank you so much
With gratitude,
Ray
Looking at my total portfolio without any date slicers, adding Dividends % and Capital Gains % doesn't equal Profit %. They are close, but each account within the portfolio is off by about 0.50%. Shouldn't capital gains + dividends = profit? Or is it just a rounding issue?
I identified few reasons why Profit % <> Dividend% + CapGain%
1. Rounding issue for Profit % calculation. Formula rounding was change to 10 (instead of 4/5). 2. Dividend % was calculated against "Total Value" and not against "Compare to Value". Total value is the value at the end of the period. "Compare to Value" is value at the start of the period plus deposits/purchases during that period. It is a subtle difference that might or might not have an impact depending on the transactions.
3. There are some other issues, that I am investigating and will report back here when/if found.
I already updated next release formulas sot that results would be more consistent.
Thanks for bringing this to my attention!
I wrote a post explaining how Profit % is calculated and why you cannot simply add Capital Gain % and Dividend % to get Profit %. Please read here: http://portfolioslicer.com/docs/how-to-calculate-investment-capital-gain-percent-profit-percent.html