What formula must be used to achieve these actions for a trading system in excel?

[hansen]

I want to figure out how to make this trading system work, but my excel knowledge is very limited. I am sure this mechanical system will help a lot of people out there too.

Lets assume a price, say 500. We will call this the base price as this system is expected to give trade signals for volatile stocks. Lets say it either moves up or down, being volatile. Now we will say it should give a long signal if the Price= Base+10 or give a SHORT signal if the Price=Base-10. So signal is BUY at 510 Or Sell short at 490, with stoploss= base price.

Now this is simple but the problem for me begins when trying to put a trailing stoploss for maximum gains. Lets say the action was Buy since the price went to 510. Now the intial stoploss as mentioned above would be 500. But when the price continues to rise the stoploss must be 2 points behind and must stay at that if the price falls below. Let me explain. Say 510 became 512, so stoploss would be 510. Now price became 515 stoploss =513. But now the price fell to 514 from 515 , then the stoploss should hold at 513 of the previous value, not fall to 514-2 = 512. And if it becomes 513 it should give an action to sell stocks (square off).

Now this does not have to trigger any softwares or anything as such. Only one cell in the sheet must give commands to buy or sell or square off when stoploss triggers. The same applies for a trade action to short at 490, with initial stoploss 500. Say it reduces further, the stoploss will trail by 2 points till the lowest, eg say price=475 stoploss would be 473 but if it starts to rise then the stoploss value must hold at 473 and square off the position at 473. Say it did not reach 473 and started falling again from 474 then the trailing stoploss would continue to have maximum gains.

If possible maybe we could have the same solution running with 2 or 3 configurations maybe like stoploss trail of 5 points instead of 2 and maybe say base+20 instead of base+10.

I am not sure if it can be done. I am sure this method will be helpful to a lot of people who have their eyes on volatile stocks but want to keep fear, greed and any other emotions out of the equation. And I am sure that this will work, only need to test out to see the best numbers for different stocks.

One more thing that would be great, is a column or such which specifies how many trades have been actioned say on a day of trading. Please note that this is only paper trading to see if trades hit can offset my brokerages and to see what is the success rate of trades triggered.

I thank everyone who have read this question and sincerely thank and appreciate any responses in this regard.

[teylyn]

Hansen, we're not traders. Talk to us in terms of cell references and processing logic. Tell us what result you would want to see in which cell and explain the logic that leads to it. Long-winded text studded with trader terms like stop-loss does not mean much to me or many others here. Get to the core.

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[Cheeky Charlie]

I'm happy to work on this, I'd like to learn more in this area.

As I understand it, you need something barely more sophisticated than "it's going up", "it's going down" - i.e. moderated by preceding changes. The key question to me is how are you recording the series? If it's just in a column this sounds quite straight forward - although I'm more willing to wrestle with the jargon I totally agree that you should upload an example and explain in terms of what you want to happen, not so much what this means conceptually.

[hansen]

Hansen, we're not traders. Talk to us in terms of cell references and processing logic. Tell us what result you would want to see in which cell and explain the logic that leads to it. Long-winded text studded with trader terms like stop-loss does not mean much to me or many others here. Get to the core.

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

Yes certainly. I was supposed to but did not end up posting a dummy file. So I am now posting the actual file. Thanks for your help Teylyn. Since the cross posting incident, I only ask for help on this forum :-) I think you all are the best.

And I also will make sure to explain what I mean without using words that not clear to all. Thanks once again.

[hansen]

I'm happy to work on this, I'd like to learn more in this area.

As I understand it, you need something barely more sophisticated than "it's going up", "it's going down" - i.e. moderated by preceding changes. The key question to me is how are you recording the series? If it's just in a column this sounds quite straight forward - although I'm more willing to wrestle with the jargon I totally agree that you should upload an example and explain in terms of what you want to happen, not so much what this means conceptually.

Thanks for helping out, Charlie. I have attached the dummy file. Lets see how this should work.

I3 has a price that updates every minute from, say a website or some spreadsheet online.
I5 is the Base price (500), that is the first price and lets assume it will decide what price to buy or sell at, for the next trade.

L3 will give an action signal, one of two. buy or sell.
Now we start calculating. If the current price is base+10 (510) the signal should say buy. If it base-10(490) the signal would say sell.

Any of the two conditions if triggered must register in column P and the price of the transaction in column Q.

Now one thing is if the Buy was triggered, the sell command should show if the price falls 2 points below the current price (Stoploss) to prevent losses in case the price do not go our way. This current, price-2 should hold true when the price rises. For eg, the price 510 gives a buy signal, then the sell signal should be given at 508. Now if the price becomes 511 the sell would be at 509, if it further increased the sell would trail buy 2 points. But lets say it reached 520 ans fell to 519 the sell signal should hold at 518 not 517. And if the price falls to 518 the SELL must show in L3. This phenomenon is for the BUY signal. If the first signal was SELL (base-10) then it would give BUIY signal at the lowest point-2. Exactly opposite the above explanation.

Also the column P and Q should capture all action. Maybe later we could deduct one percent for brokerages, and also try and have 2 or 3 different configurations like 15and 5 in place of 10 and 2 and the like.

And most importantly, once the stocks were bought and sold it should loop back again to have the then current price as the new base value and begin the whole process again.

I hope we can try this formula with certain volatile stocks and see which values trigger most successful trades. I think that 3% gain a month is 36% a year non compounded, but there are quite a few stocks which are range bound and are capable of much higher than 2-3 % a month. I am sure this will work with those stocks, giving good returns.

Thanks once again for your help Charlie. Please inform me if I am still unclear with the explanation. Cheers.

[Cheeky Charlie]

Took a little while, been busy...

[hansen]

Took a little while, been busy...

Thank you very very much.
Sincerely appreciate your time and effort.