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

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

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

Second try to upload file

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

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

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

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  

Hi Mike,

MCD resulting as 0.62073 due to Dividend transaction. 

Arun  

Fixed the errors.  Thanks.