Thread: IF function

Hi there,

I was wondering if anyone knows if it is possible to create a forumla for the following information:

I have daily share prices for a company for 10 years and have the following criterion to know when to buy and sell the share:

If the share price moves up at least x% from a subsequent low, buy and hold the share until its price moves down at least x% from a subsequent high and at that time sell. Moves less than x% in either direction are ignored.

I really want a formula to be able to do this as it has been taking me ages to do this manually and I've tried various IF functions but they don't work. Maybe it isn't possible but I thought I'd ask anyway.

Thanks for anyone that gives it a go!

If the share price moves up at least x% from a subsequent low,

Assume you mean from a prior low? You don't know what it will do susequently. Ditto for the next sentence.

Maybe an example would help.

Sorry yes I realise the first part of that was a bit misleading. Should have been:

If the share price moves up at least x% buy and hold the share until its price moves down at least x% from a subsequent high (from when you bought it) and then sell it. Then buy it again when the price moves up at least x% from a subsquent low (from when you sold it).

I've attached an example of what I want to be able to do with a formula.

The idea behind the criterion is that the share price will move up and beyond the price that you buy it at and below the price which you sell it at. Therefore after you have bought a share you calculate the fall in price using the highest price it achieved and the drop from that, if it is bigger than your filter (of say 1%) then you sell it and vice versa.

I start by just buying the share at the start of the period I want.

Hope that helps!

Make sure that you put the word "Buy" in E3, then put this formula in E4 and copy down

=IF(ISBLANK(C4),"",IF(AND(C4>0.01,COUNTIF($E$3:E3,"SELL")=COUNTIF($E$3:E3,"BUY")),"BUY",IF(AND(C4<-0.01,COUNTIF($E$3:E3,"SELL")<COUNTIF($E$3:E3,"BUY")),"SELL","")))

hth

Have a look at the attached.

I've created three new columns
- column D finds out if the price goes up or down
- column E applies consecutive numbers
- column F calculates the percentage change since the last up/down change

The Buy/Sell column now looks at column F instead of column C

You can hide columns D to F if you want.

I find they now match your manually entered markers, except for row 57-58, where I believe you sold too late