How to Use Excel to Keep Track of Stocks

Savvy investors make decisions to buy or sell stock on the basis of data rather than guesses or feelings. There is no better exercise to increase your acumen as an investor than to track your own stock data yourself. In very little time, you can set up Microsoft Excel worksheets to track your stock trades, guide your future investment decisions, and gauge the success of past investments.

Basic Information

Excel is well suited for tracking basic information about a stock. Using one line per type of stock, set up the following columns: stock name, ticker symbol, number of shares purchased, and buying price. Each cell should be easy to fill in based on easily accessible data provided by your brokerage firm. If you buy additional shares of the same stock at a new price, record the new shares in a new row. This will help when you conduct a break-even analysis.

Break-Even Point

Once you have your basic information input into Excel, it's time to begin to use the calculation power of the software to make investing decisions. First, add a column labeled "Break-Even Price." Then, in the cells below for each stock you're tracking, input the following formula: =(((Buying Price*Shares Purchased+(Commission*2))/Shares Purchased)-Tax Rate*Buying Price)/(1-Tax Rate). For each of the variables, you could either put in the exact numbers from the previously gathered data, or you could build the formula using cell references. "Buying Price" is the price you paid per share of the stock. "Shares Purchased" is the number of shares you bought. "Commission" is the amount that you must pay to your brokerage service each time you buy or sell a stock. It is "Commission*2" in the formula, because you will have to pay this commission twice for the one stock: once when you buy and once when you sell. "Tax Rate" will either be the long-term capital gains rate for that year, if you plan to hold the stock for one year or longer, or your federal tax bracket, if you expect to sell the stock before one year is up. You could also add in your state income tax rate as well to this number. Once Excel calculates the solution to this formula for a given stock, it will tell you your break-even point for this stock. The break-even point helps you determine what selling price to target.

Dividends

In addition to profit or loss on a sale of stock, you may want to track over time dividends paid out by stocks. To do this, start a new worksheet with the following columns: ticker symbol, dividend percentage, dividend amount, and date of payment. This will give you a simple reference of all dividends paid that is easily sorted by stock name, date, or amount.

Capital Gains

When you sell your stock, you may wish to calculate the long-term and short-term gains for yourself, rather than trusting solely to the calculations of your brokerage firm. This is not complicated to do in Excel. Start a new worksheet with seven columns: ticker symbol, shares sold, date of sale, selling price, purchase price, capital gains, and long-term/short-term. Purchase price information will come from your original stock tracking worksheet. Your capital gains column will be a simple formula: (Selling Price - Purchase Price)*Shares Sold. This will give you your capital gains totals, or capital losses, if the number is negative. For each sale, mark if the gain was a short-term gain, meaning from a stock held for less than a year, or a long-term gain, from a stock held for a year or more. Verify your calculations against forms sent out by your brokerage firm at the end of the tax year.

Prospective Stocks

Excel is also useful for listing stocks that you may wish to buy in the future. When a stock catches your eye, add it to your prospective stock list, keeping track of the stock name, ticker symbol, current selling price, and your target buy price. You may also wish to include a field of notes, where you list what you like or dislike about the stock. This will give you a handy reference to check periodically when you are ready to invest more.