Log In
Register
Home
Active Member
March 23, 2021
Offline
How to add realized gain/loss to the Stock Portfolio Dashboard?14
See Answer
July 16, 2010
Offline
Hi Mike,
Welcome to our forum!
Unfortunately, your question is too vague. Please provide a sample Excel file that clearly illustrates the data you're working with and your desired result so we know what you mean.
Thanks,
Mynda
Active Member
March 23, 2021
Offline
Hi Mynda-
A sample Excel file is attached. You can see several examples of
Unrealised gain where the formulas follow your example
Realised gain (MCD, NVDA, TSLA, and VEEV) where I made a stab at it by an IF statement that only calculates gain/loss when SHARES > 0. This in turn disclosed that with fractional shares, a add and subtract in the same amount (example: MCD 0.62073) shares doesn't calculate to exactly zero.
And I could figure out a formula for a stock that has both open and closed positions (example AMZN) so I did it by a one-time formula.
-Mike
July 16, 2010
Offline
Hi Mike,
No file attached. Be sure to click the 'Start Upload' button after selecting your file and wait for the green notification under the button to tell you that your file has completed uploading.
Mynda
Active Member
March 23, 2021
Offline
Second try to upload file
July 16, 2010
Offline
Hi Mike,
Thanks for sharing your file.
Not sure how the Amazon shares work. It appears you bought 28 on Feb 4, then sold 32 on March 3, which is more than you had. Then April 16 you bought 32?
Mynda
Active Member
March 23, 2021
Offline
Mynda-
The 32 shares (Lot #1) were bought in 2020, and sold in 2021.
The 28 shares (Lot #2) were bought in 2021
Lot #1
2020-04-16 Buy 32x AMZN
2021-03-03 Sell 32x AMZN
Lot #2
2021-02-04 Buy 28x AMZN
So Lot #1 is a closed position, and Lot #2 is an open position.
-Mike
July 16, 2010
Offline
Doh, I misread the year! I shouldn't answer questions at night after a long day 🙂
You can add a Status column to the ledger that marks shares as sold. Then you can add a criteria to your Unrealised and Realised Gain/Loss SUMIFS that only include transactions marked as open or sold respectively. If you only sell part of a holding, then you'd need to split the transaction into two rows, one for the sold shares and one for the shares you still hold.
Hope that makes sense. See example attached.
Mynda
March 15, 2021
Offline
Hi Mike,
I noticed that your Lot #1 2020-04-16 Buy 32x AMZN is showing as Interest in Col C ( Action) and not Buy.
Arun
Answers Post
1 Guest(s)
