Elvis
06-22-2009, 11:16 AM
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
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