The Excel spreadsheet of my US stock trading system


The Excel spreadsheet of my US stock trading system

By  | 

This week I began trading the US markets and specifically the stocks of SP500. I created an Excel spreadsheet where I keep track of the stocks I buy or sell according to my online trading system.  I must confess that the adventure has started quite well and above my expectations, given that it is the first time I am trading online the US stock exchange.

At the time of writing, I am not holding any stock. The Excel spreadsheet consists of 15 or so columns, including stock name, buy or sell price, stop loss limit price, number of shares, capital at risk, risk per trade and P/L. I am also using the spreadsheet to calculate other significant statistical data about the online stock trading system like average win/loss ratio and winning percentage that will help me in the future to evaluate the trading system.

Now, I should note that this Excel spreadsheet is not the first one I have created concerning online trading. In fact it was created when I started trading the Greek stock exchange. I have kept the same layout until now since it has been proven quite helpful. The only things that have changed are the commission calculation and including short orders, something that is not available to my country’s stock exchange. For each stock exchange now that I have been an Interactive Brokers’ customer, I have copied the sheet to different tabs, so that I watch my performance individually.

In the first row there is my available starting capital. The maximum risk per trade is calculated according to that amount of money. Right next to it you can see that I risk maximum 1% of the capital per trade. Next comes the month and beside that my total risk reads 10%. That means that an no point I will be risking more than 10% of my capital in all trades combined. Since my total risk per trade is 1%, I am allowed to trade 10 stocks simultaneously, unless I move up a stop loss. More on that later. Running risk is the amount of money I am risking to all my open trades. Although at this point I am not holding any stock (even the AMAT in the last row), I have kept the data at the last row so that I copy it to my next trades.

The first stock I traded was BMY on Thursday. Maybe I should include a Date column somewhere. Truth is I did have such a column but I wasn’t being disciplined enough to keep track of dates as well! Anyway, I bought BMY at 24.36 and defined my stop loss at 24.00. The spreadsheet then filled in the “Risk/Share”, “Shares” and “Buy” cells, indicating that I should buy 278 shares at 24.36. Upon entering then 24.00 as a “New Stop Loss”, the money at risk would read 100, meaning that if I was to sell the stock at 24.00 I would stand to lose $100, 1% of my starting capital. Well, the stock climbed and I sold it at 25.47, taking advantage of a 4.56% rise. That means that upon selling the stock I got back about $7,000 and the P/L of that trade was about $300 for me. The “Equity” cell went on and added than amount.

How do I use this spreadsheet during trading the US stock market? Given that my trading setup consists of 4 monitors, I load this spreadsheet to the leftmost monitor. When I decide to buy a stock, I enter the stock name, the buy limit price and the stop loss and the Excel spreadsheet tells me how many shares I should buy so that I won’t be risking more than 1%. I then go on and send the order through Interactive Brokers’ BookTrader. If there’s been a price shift, I quickly change the “Buy” price, so that I buy the correct amount of shares.

Now, let’s say BMY moved up a lot and I decide to move up my stop loss at 24.36 making it a breakeven trade in the worst scenario. I no more risk money at that trade, allowing me to trade one more stock instead. So, if I held 10 stocks at one point and wasn’t allowed to trade any more due to the 10% total risk rule, that stop loss move would mean that my total risk would be 9%, making it possible to open up one more trade.

When I trade out, I delete the “New stop loss” cell since I no longer hold the stock and I enter the “Sell” price data, so that it calculated my profit or loss. Depending on whether the trade was profitable or not, I color the row in green or red respectively. You can see that the first 9 trades were profitable, a great way to start up!

At this point I won’t be recalculating my available capital until I trade 200 US stocks and can reach to safe assumptions about the effectiveness of my trading system. When I am confident with my personal trading style, I am going to change the available trading capital when there is a $2,000 gain or loss.

Download my Excel spreadsheet! (25kb)

Jim entered the financial world by trading sports and now invests in US stock markets and forex, trying to buy low and sell high. Connect with Jim: StockTwits | TradingView