Probably Moving Average based trading systems are most popular among the traders across the globe. These trading systems work very well in Trending markets. We introduced one such trading system in our AFL of the week section:
AFL of the week: 14-73 EMA crossover system
Most of the beginners might not have subscription to Amibroker with continuous data feed. So we have attempted to develop a semi-automated Moving Average trading system in Excel. The only manual task would be to copy closing price for the security selected in this excel.
Strategy Overview
| Paramter | Value |
| Preferred Timeframe | Daily |
| Indicators Used | 3 Days EMA, 5 Days EMA |
| Buy Condition | 3 Days EMA is above 5 Days EMA |
| Sell Condition | 3 Days EMA is below 5 Days EMA |
| Stop Loss | No fixed target, Stop and reverse when excel gives opposite signal |
| Targets | No fixed target, Stop and reverse when excel gives opposite signal |
Excel Sheet
| Parameter | Description | |
| Worksheet Name | MA Trading System | |
| Inputs | Date (Column A) | Calender Date |
| Close Price (Column B) | Closing price on specified Calender Date. | |
| Faster EMA Period (Cell C2) | Faster Exponential Moving Average. Default is 3. | |
| Slower EMA Period (Cell D2) | Slower Exponential Moving Average. Default is 5. | |
| Outputs | Faster EMA Values | Faster EMA values based on period defined at input. |
| Slower EMA Values | Faster EMA values based on period defined at input. | |
| Crossover Price | The exact price at which EMA crossover will take place. | |
| Signal | Buy/Sell signal based on conditions mentioned at strategy overview. | |
Screenshot
Download Link
Thanks. Tried this system long ago. For NF it works, but for BNF it resulted in consecutive stop loss hit & negative returns. So if we need to alter the system to suit the volatility of BNF, what will be the new ema values? & how to do it?
Hi Gautam,
It may lead to consecutive losses but would be profitable in the long term. This is the characteristic of every trend following system.
Signal calculation in the sheet is wrong
Hi Mihir,
Can you please let us know what did you find wrong in the calculation. We curiously verified the EMA values with Google provided values and everything looks correct!
when you are giving buy signals on ema crossover CALCULATION for 01/01/2016 shown by you is =IF(C3>=C5,”BUY”,”SELL”) instead it should be =IF(C3>=D3,”BUY”,”SELL”)
and due to this nifty going down from 6/01/2016 values declining but your signals instead of sell are showing buy.
pls rectify the formula as is buy when ema3>ema5
Hi Mihir,
We have corrected the file. Thanks a lot for pointing this out.
Cheers!
pls change the image also
Need an automated Exel sheet for commodities also.
You can also make sheets for camrilla intraday levels.
Hi Kunal,
We are working on a Trading setup for commodities. Will share soon.
Thanks
Please let me know once you have done with that.
You guys are doing awesome work.
Waiting for you excel tutorial also.
Thank you Kunal!
SIR, KINDLY EXPLAIN HOW EXACTLY WE ENTER THE TRADE AS PER MOVING AVERAGE EXCEL SHEET.
I HAVE ENTERED THE CLOSE PRICE OF TODAY 28/4/2016 WHICH IS 7847 AND THE SHEET IS SHOWING 7901 AS CROSS OVER PRICE AND SIGNAL AS SELL. THE CLOSING OF TODAY IS 7847, AM I SUPPOSE TO ENTER THE TRADE TOMORROW 29/4/2016 AT THE OPENING OF THE DAY.
KINDLY EXPLAIN IN DETAIL AS HOW & WHEN TO ENTER THE TRADE AS PER THIS MOVING AVERAGE EXCEL SHEET.
THANKS & REGARDS
Hi Karan,
In this particular scenario you mentioned, you need to Short at next day’s open and put a stop loss at 7901. You can also buy one fresh lot at 7901 or wait till EOD for confirmation about next signal from Excel sheet.
sir
I am Interested in trading n totally beginner. Once I have tried FOREX but as it happens with most of beginners I suffered losses but after some time I got a zeal to so it again.It was always inherited in me but I want to do with some knowledge and practice . Please guide
@Admin: Can u please post excel sheet for crossover calculation of Simple Moving Average with Exponential Moving Average? (Crossover point for SMA & EMA)
Thanks in advance.
Any specific parameters for SMA and EMA?
20 SMA & 3 EMA.
Thanks in advance.
Instead of 3 and 5, is it possible to change the ema values on excel.
Dear Admin, The Excel file posted in your site contains 61 entries. Do we have to maintain this data endlessly OR the first 6 days counting back from today are sufficient to get the correct signal ? Why I am asking is this because, when we delete the data prior to 6 days, the Cross over price changes with the deletion of each day’s entry. Please advise.
Hi Vijayan,
Ideally 5 days is enough to determine crossover price
Can this be used for intraday trading ? Or it is a short term trading strategy ??
Hi Dhamodaran,
Yes, it can be used in any timeframe
Does this file work in 2021?
Yes Vikram, it does work in 2021
Do you recommend any intraday stop loss if crossover level is very far? If yes then I assume SL should be calculated from the day’s open price? What’s should be the ideal SL points for Nifty and BankNifty respectively?
Hi,
We do not have any official recommendations on the SL level. That truly depends on your risk appetite and trading system rules