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

##### New Member
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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

##### New Member
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?

#### igold

##### Well-known Member
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.

##### New Member
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?

#### igold

##### Well-known Member
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

##### New Member
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.

Replies
5
Views
52
Replies
1
Views
252
Replies
1
Views
426
Replies
19
Views
386
Replies
1
Views
127

1,191,120
Messages
5,984,759
Members
439,909
Latest member
daigoku

### 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.

### Which adblocker are you using?

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

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