Excel to calculate buy and sell prices

normad

Hi there,

Hope I'm posting in the right forum. I am a masters student, and for my thesis I am examining the opportunity for profit using technical trading rules in the stock market. I am concentrating on the filter rule which says "A stock should be bought when it goes up by x per cent from a previous low, and sold and simultaneously shorted when the stock subsequently drops by x per cent from the preceding high."

I am using excel to calculate the percentage changes and to find the buy and sell points. Has anyone done anything similar before? I am finding it quite difficult to do. Does anyone know how I'd go about doing it? Or advise me on an add in that I could download to help me do it?

Thanks in advance

Ideo

I'll give you a hand, post up a excel file with the stock prices the highs and lows

Will this info be updating automatically from a feed or will u be manually entering it?

normad

This attachment is a sample that I've been working on. It's not the whole data set. I have all my data in an excel file already, and won't be adding anything new to it.

Thanks

Ideo

so if the change is greater or less than 5 % of the previous day, you want to signal a cell to either buy or sell respectively?

normad

See, that's when it gets complicated. It's not the change between today and the previous day, its the change between today and the previous high or low.

Say if I have bought a stock, and then the price continues to rise for some time. For example I buy at €100 and the price rises to €120 over the coming days. I want to sell when the price drops 5% from the previous high, so I want to sell if the price drops to €114 or less.

I also only take account of the highs (lows) after I buy (sell), and not the ones which occur beforehand. So in the above example, even if there was a high of say €130 previous to me buying the stock, I would work off the €120 for calculating the percentage change for my sell indicator.

freeze4real

Your best bet is to go to a quant site. http://quant.stackexchange.com/

I get what you are trying to do. Using if as far as I know is the right command but I think your data is missing some stuff.

Why not use matlab or python or R. or some brokers actually have these kind of commands. Open a demo account fill those commands in and backtest those strategies, then copy and save the results into excel format.

normad

Great thanks for the advice, I haven't used matlab etc before so they didn't spring to mind, but will give them a try, thanks again!

TheChizler

If you're getting matlab the student edition is only $100 from the US site! That or learn a bit of JavaScript and Excel macros will be your friend.

Ideo

would you not just do a multiple if formula, "=ifs"

If SP is after data of buy & SP Change % using "MAX function" > or = to 5% then sell

Is that what you mean?

I'll post excel tomorrow

daheff

I've uploaded an amendment to your file.


Basically (Assuming I've understood you correctly) if the close price is more than 105% of the previous high you want to buy and if the close price is less than 105% of the previous low you want to sell, or else take no action?

A (relatively) simple IF function does this for you. All you now need to do is make sure that your Close price, Previous high, Previous Low prices are updated

normad

It's actually slightly different- if the close price is more than 105% of the previous low I want to buy and if the close price is less than 95% of the previous high you want to sell, or else no action.

I'm not sure how to sort my formulas for the highs and lows. How do I get the max of cells between a buy and sell point?

There is one further complication: when I have already bought, I cannot buy again, I must wait until I have sold.

These issues are causing the problems, not the actual if formulas. If you can think of a way to do these, it would be great, if not, I'll have a look at Matlab or something else.

Thanks

daheff

It looks to me that you need to draw out a decision tree (keep it yes/no) to indicate how your plan will work. From your posts, it seems (to me anyways) that you are not 100% sure of how it should work and are trying to work your idea around what you can do in excel.

Once you have this then an excel function(or matlab or anything else) will be easier to construct.


just quickly looking at your last part that you cannot buy again if you already have shares bought, you need to add another field that keeps track that you already hold shares (or are short).

Feel free to PM me if you want to discuss the excel part further.

Ideo

i think an ifs statement or a nested if statement is the way to go, its not that bad to set up
What's your idea behind the whole thing - maybe this will make it easier to understand?

normad

My idea is to test the EMH. It also basically sees if Getting out of a stock after falling a set % will help avoid large losses, and getting in when its rising is just like momentum trading. The % filter rule (above 5% say) just helps filter out small movements in the stock price.

I think I nearly have what I need. I'll attach my most recent excel file. The one thing I'm stuck on is columns F & G, my highs and lows. I'm using a max and min function respectively, and an offset function to keep adding in new cells in each row. What I need it to do is to disregard any max or min before the most recent buy or sell signal. At the moment I have an absolute cell reference, which I manually changed a couple of rows down when I saw a sell signal. Is there any way of doing this automatically? Even by adding another column?