In this article I will explain step by step how to use the template.
1. Enter a baseline stock
To ensure that we obtain the correct holidays in the stock exchange, please enter a stock that belongs to the exchange that you will be interested in tracking.
2. Enter Simple Moving Average (SMA) Periods to track.
The template can handle 3 different SMA periods/lengths.
In the example above, I have entered 20, 50 and 200.
3. You can also change the timeframe between Daily, Weekly and Monthly.
4. Enter stocks to track in the table
For example, for entering Microsoft stock, I will type in XNAS:MSFT and then press Enter.
If Excel does not automatically recognize as Stocks data type, please select Stocks Data type from DATA ribbon, while selecting the cell.
To learn more about the method to enter the stock symbol, please check
Once you enter the stock symbol, all the calculations will automatically update.
You can enter up to 500 stocks.
5. Refresh to update stock market data
If you have the file open for some time and want to get live market data, please click REFRESH ALL in DATA ribbon.
Depending on your Refresh settings, it may automatically refresh every 5 minutes already.
You can check the settings using the method shown below.
6. View Results
The three SMAs, their Signals and an overall SMA Rating are automatically calculated.
Signal Logic: If SMA > price, then SELL. If SMA < price then BUY.
Rating Logic: Average of 3 SMA Signals; SELL signal is given -1 and BUY signal is given +1 value.
Filter to see specific Rating (Sell, Strong Sell, Buy, Strong Buy, Neutral)
Customize the Conditional formatting rule
We also have a column at the end that you can customize to pull from a list of attributes/properties of the stock.
In this case, we have chosen INDUSTRY.
You can also add more columns by just typing in a new column name. Please unlock the spreadsheet using indzara as password before adding more columns.