• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Stock Trading Profit

AshK

New Member
I am trying to work out the profit on each stock whether sold entirely or partially but I cannot seem to get the number right. In the attached file, the Profit/Loss column is the one I am keen to this right. The result should be as under:

5-Mar-25 Sale:​

  • Sell 8 shares at $160.
    • The first 8 shares come from the 10 shares bought on 01-Feb-25 at $150.
    • Cost of 8 shares = 8 × $150 = $1200.
    • Sale proceeds = 8 × $160 = $1280.
    • Profit = $1280 - $1200 = $80.

15-Apr-25 Sale:​

  • Sell 6 shares at $162.
    • First 2 shares come from the remaining 2 shares at $150.
    • Next 4 shares come from the 5 shares bought at $155.
    • Cost of 6 shares = (2 × $150) + (4 × $155) = $300 + $620 = $920.
    • Sale proceeds = 6 × $162 = $972.
    • Profit = $972 - $920 = $52.
 

Attachments

  • Stock_Tracking_FIFO.xlsx
    12.4 KB · Views: 3

AshK

Could You use something like this in DoIt-sheet?

You'll see totals under 'Stock'.
You could add as many Stock Symbols as needs.
Fill data in columns from I to M as in sample.
Press [ Do It ]-button
N-column shows row 'Buy' profit
O-column shows quantity of row
ref's shows row-number and quantity of selling.


This is still rough sample.
 

Attachments

  • Stock_Tracking_FIFO.xlsb
    29.8 KB · Views: 3
I've tested only with Your given data.
Check this modified version
- eg cannot sell more than has bought.
 

Attachments

  • Stock_Tracking_FIFO.xlsb
    31.8 KB · Views: 5
Thanks a lot. I have nothing but appreciation for your good work. It really works well and can be a great tool. Since I added some data to your previous spreadsheet, I will download the new version and copy it across. A couple of things might make it better. As there is a brokerage cost associated with each trade, a column for that would be great to get a realistic picture of the profit. What is the Value column (F)? Column N and O need to be right aligned. Have a look on the screenshot. Again thank you so much.
 

Attachments

  • Screenshot 2025-02-19 114355.png
    Screenshot 2025-02-19 114355.png
    115.6 KB · Views: 5
Sorry to bother you again. Your improvement was great and it caught one stock where I sold more than what was bought. You can see the TLS stock in the screenshot I sent. The reason was I received some shares as part of dividend reinvestment plan. One option is that we include this as increase on zero value or add an option to reflect DRP. What are your thoughts?
 

AshK

Show Your comments and so
with realistic data with a file.

Here my next sample version.
Click that [ ? ]-cell to hide / show extras.
 

Attachments

  • Stock_Tracking_FIFO.xlsb
    38.3 KB · Views: 3
Last edited:
Thanks. I actually quite liked your previous version. Some formatting and inclusion of profit (alongside cumulative profit you have done will be great)
 
There are all possibilities to do everything.
But
You could get almost same ... after select ?-cell ... as I wrote many days ago.
Screenshot 2025-02-23 at 10.11.29.png
 
Back
Top