How to calculate loss or profit for multiple trades

[Elvis]

Hi,

I'm thinking about seeing if I could use excel to test some trading rules to optimse parameters of an indicator. Probably wont work and I'll need to keep my day jov but I thought I could at leats give it ago. Here's where I've got to.

I have pricing data on a company's stock price in column C ordered with oldest pricing history at the top and newest at bottom.

In column F, I have created trading signals based on the prices in Column C using a moving average, 1 denotes a buy and -1 denotes a sell.

I need to work out the profit/loss I would make buying and selling for example 200 shares/units on the corresponding buy and sell prices in column C.

I would buy every time a cell in column F generates a 1 and continue to hold the stock until a cell column F generates the next -1. Then I would buy again on the next occurrence of 1 in column F and sell on the next occurrence of -1.

So for example,

The first 1 occurs in F8, so I would to buy at the corresponding price in c8 (200 * the price), all subsequent cells continue to have 1 until I get to F13 (i.e. F8 to F12 all contain 1- buy signals in them so I would hold the stock!), the first occurance of -1 appears in F13, so I would sell at the corresponding price in C13 (200* price). My loss or profit for that trade would be the difference in price between C8 and F14 (*200). Then I want to buy at the next buy signal which occurs in F15 and sell at the next occurring -1 (F16).

My pricing data runs from D8: D2948, I want to calculate the profit and loss from all these separate trades and sum the results in say G2 so I can work out what that trading strategy over the entire period would yield in terms of loss or profit.

I know how I could do this for any given single trade but know all trades at once. I'm assuming it's likely I need some kind of continuous formula in column G to work out profit/loss for each trade and then I could simply sum that range in G2 or some VBA solution if that's easier!

Hope someone can help!

Thanks,

Elvis

[Aussiebear]

To assist, please post a workbook so we can see what you currently have to work with.

[Elvis]

Hello again,

I've attached the worksheet I'm using.

Thanks,

Elvis

[Elvis]

Sorry, I forget to mention that I have replaced the actual signals 1 and -1 with the words buy and sell respectively to make it clearer what the direction of the trade would be.