Tracking Investments In Excel – Trading Using Spreadsheets

Home / Featured / Tracking Investments In Excel – Trading Using Spreadsheets

Excel sheet trading is new method for trading stocks or index; you can get the levels for the stocks in a particular day. Tracking investments in excel is very popular these days because browsing several websites takes a lot of time. Traders use this excel sheet method unknowingly along with OHLC; levels are in words represent the bull zone or bear zone which works based on trading psychology. But if you don’t understand the psychology, you will get only stoploss taken every time. So how does this excel sheet trading work?

Google finance is interesting and you can get the OHLC data for almost all the stocks or index around the world. This method can also be calculated in paper as well, this is the only method which I follow every day in my trading. Have around 5 stocks in your radar; this is based on news in that stock, every day make only 2 to 3 trades. If you are getting 1 percent profit, close the trading terminal and relax. We need around 10-20 points everyday only and not more than that.

First let me explain the psychology of trading and how the levels which are numbers works!  The open price decides the trend for the day, when the open price is greater than previous close; you can proceed to buy the stock. When the price opens lesser than previous close, you can sell that stock. Because open price is the strong psychological factor, when the LTP is green then traders would love to buy the stock in greed and when the LTP is red, traders would rush to sell the stock in fear.

Step 1: Open price > previous close — buy; open price < previous close – sell


If you are looking for an automated stock trading system then it doesn’t exists. You can do the work manually and create extra-ordinary results than any algorithm,. 70 percent of the time when the open price is above previous close, the day would be trending up, meaning the probability of stocks closing higher. It depends on the news in the market for the particular day; you should keep the news of stock market in your fingers by scanning through top finance portals daily.

Choose the timeframes to trade in a day, Market open (10am), 11.30am and lunch (1.30pm) are the three timeframes to watch out if you are trading in stocks (day trading). If you are an investor then you should wait for quarterly results and seasons. Every December people would sell because of the holidays (Christmas and New Year) and mostly funds flow would be higher during the month of April (Investing time). So if you are an investor, look out for best stocks in April and plan to sell them in December.

Now back to trading stocks, take the 30min bar of 9.30am (High represents the Resistance, Low represents Support). When open price greater than previous close, buy above the first hour high price, when the open price lesser than previous close, sell below the first hour low price ( SEE step 1); you can use the resistance and support static or dynamic depending on the market trend.

Step 2: Find the first hour High and Low (Resistance and Support for the day)


Around 11 am after you had made a trade, it’s time to check whether to hold or book profits.  When the previous day high is not broken yet, it’s time to book profits and wait for sell levels below the first hour low price which would be day’s low.

If you have taken the sell trade, your stop-loss would be day’s high and when the today’s low didn’t break yesterday’s low then it’s time to book profits. Wait for the buy call to reverse the trade, sometimes I don’t recommend it because when the market is falling not many traders would buy again.

Step 3: At 11am, if previous high is broken with volume, continue to hold the stock which was bought else book profit.


At 1.30pm, if the stock continues to hold above the first hour high and above/below previous high there are more chances for the stock to go higher. Book profits if you had made 1 percent or 1.5 percent from your buy price or sell price. 70 percent of the times, when the market is trading higher it would make higher prices after 1.30pm till market close. If the stock keeps failing from morning, then after 1.30pm, the stock would continue fall lower. Book profits whenever you feel uncomfortable with the trade. Trade with consistency and complete every month with gains.


If you looking to add technical indicators in your spreadsheet, please follow this link

Facebook Comments