Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Large Capital Gain loss due to in kind Transfer of stocks

In 2018 my broker transferred all stocks from one account to another in kind with a different brokerage firm.
To enter these transactions I used the SymbolTransferOut transaction type and specified Qty and Price.
Total TransferOut amount was $361,522. Total Cost Basis was -$296,398 as shown in the TransInfo worksheet.
But when I run a report in PowerBi, it shows me a Capital Gain loss of -$373,653.
Total Value and Cost Basis for that account come out as zero (as expected?).
CapGain in that account before the transfer was correct and is also correct in the new account that I transferred into.

Since the CapGain loss is close to the TransferOut amount, I suspect that somehow the Start Value of the TransferOut Account is wrong. Must be close to zero. In the Yearly Movements table (Yearly worksheet), the amount shows up as a negative deposit of -$379,772 which results in a CapGain Loss of (only) -$12,131.
So why am I getting two different results between PowerSlicer and PowerBI and what could explain the large CapitalGain loss shown in PowerBI?

Thanks for any advice you might have.


  • For SymbolTransferOut transaction you must specify Qty and Price (at the time of transfer).
    For SymbolTransferIn transaction you must specify Qty and Price (same as for TransferOut).
    Can you confirm you have that specified?
  • Yes, they are exactly the same.
  • Would it be possible for you to send me your Excel workbook and PowerBI file? For privacy, you can change any other transaction data, just leave these important transactions so that I could investigate. My email address is posted on this page:
  • I just sent you the two files.
  • Issue appears to be resolved - just source data entered not correctly.
    Additional questions:
    Q: If I select multiple years in the holding or in other workbooks, how do I interpret the data? Especially for Capital Gain and Profit? Will I see the total cumulative gain/loss for both or is this not a legitimate selection in PS or PowerBI and I should select only one year at a time?

    A: Year/Month slicer in Holding report shows mixed data results that might be hard to understand. For example: Qty Held, Total value is value AT THE END of the selected period. But Dividends, Capital Gain, Profit are values FOR the selected period.

    Q: I am trying to track a portfolio or a single Symbol over time. What is the best way of doing that in your view?
    A: In Mthly and Yearly tabs I would add slicer for Symbol and connect it to that worksheet reports. This way would let you select the symbol and see its monthly/yearly "movement".
  • OK; That works. Actually, calculating returns of a portfolio over a multi-year period with various deposits, withdrawals, dividends in and out of the account plus buys, sells of individual securities and share price gains or losses seems quite complicated. But I believe that Portfolio Slicer has the capabilities to do that assuming that all data is entered correctly. After about 1 year of using your spreadsheet I am still learning. Amazing! Thanks very much for providing this tool.
Sign In or Register to comment.