How do I calculate the gains/loses made from buying and selling a stock?

radiotower

New Member
Joined
Dec 29, 2016
Messages
7
If I bought and sold the same stock for many years, how can I calculate the total gains/loses made from all of the transactions assuming that I have a column containing all of the stock's prices, a column containing all of the buy dates, and a column containing all of the sell dates?
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
I attached a sample from my large spreadsheet. Essentially, any time you see a "1," that is the date I bought the stock. I would need to calculate the percent change of the stock price from that date until the date the stock was sold, which is represented by a "-1." As you can see, the stock was bought and sold many times over the years. So I would need to calculate all of the gains/loses to determine whether the decisions would have yielded an overall positive result (gain).

https://postimg.org/image/v72h7xxul/

Also, here are the cross posts: How do I calculate the gains/loses made from buying and selling a stock?
How do I calculate the gains/loses made from buying and selling a stock?
 
Upvote 0
From your image it looks like you are trying to back test a market strategy, which is a little different than what your original post suggests.

Good luck with that.
 
Upvote 0
From your image it looks like you are trying to back test a market strategy, which is a little different than what your original post suggests.

Good luck with that.

Yes, that is exactly what I am trying to do. Are you saying it can't be done?
 
Upvote 0
No I am not saying it can't be done, millions of people do it everyday. There are tons of websites out there that will back test any strategy you input.

However if you are smart enough to come up with a market strategy that you want to back test I would think as a prerequisite you would already know how to calculate gains and losses.

Just my two cents.

igold
 
Upvote 0
No I am not saying it can't be done, millions of people do it everyday. There are tons of websites out there that will back test any strategy you input.

However if you are smart enough to come up with a market strategy that you want to back test I would think as a prerequisite you would already know how to calculate gains and losses.
Just my two cents.

igold
I do know how to calculate gains and loses. However, I do not know how to set up a formula to calculate, in excel, the gains and loses for 18 years.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top