Are you struggling in finding tracking your stock in excel sheet. So we have come up with one of the easiest guide to track shares in excel sheet & also included a free template.
All you need to do is to follow these steps to track your stocks & use this file to make the tracking even easy.
We use excel in simplifying our tasks and information, but when it comes to tracking shares in Excel, it looks challenging. And if you get a way to track your shares in excel, having everything in one place is worth the effort.
3. What is a stock template?
Whenever you buy or sell stocks, you need to keep them in sheets to manage them properly. And while managing stocks in a spreadsheet, you must create formulas and perform mathematical calculations to make the whole data flow smoothly.
Here comes the stock template. It is a predesigned spreadsheet that you may use to build new spreadsheets with the same style and specified calculations. You do not need to create formulas and mathematical calculations; everything is already incorporated into the spreadsheet.
Step by step guide to track & manage your stock portfolio:
After completing these actions to track your shares, you will find it easier to manage your stock portfolio.
- Record initial data to start
- Determine the break-even points.
- Track dividends
- Evaluate capital gains.
- Examine potential stock purchases
Record initial data to start
Basic data entry is the first step in creating a valuable Excel spreadsheet for your stocks. It is advisable to put each stock category in a distinct column and utilize one line for each stock category.
- Stock/share name
- Trading Symbol
- Number of shares purchased
- Buying / Purchasing price
As and when you filled the information in each column, each cell will automatically be filled with the information/data provided by your stock broker.
Establish Break-Even Points to Ensure Profitability
The following step is to build a new column to evaluate the data and establish a break-even calculation, which defines the Price of sale required to generate a zero net income.
Now give this column the title “Break-Even Price.” Put all the stocks you’re tracking just below this column and use the algorithm given below:
= ((Buying Price*Shares Purchased+Commission*2)/Shares Purchased)-Tax Rate*Buying Price) (1-Tax Rate).You may either create the formula using cell references or input the precise values from the data you have already collected for each variable.
Here:
Buying Price = Price per share of a specific stock.
Shares Purchased= Total number of shares you bought.
Commission= Fee for the brokerage firm
If you see the formula, we have given “Commission*2” because you will be required to pay the fee twice. Once when you buy the shares and the second when you purchase them.
The “Tax Rate” category will be calculated as follows:
● If you plan to own the stock for a long time, long-term capital gains rate for a year
Establish expectations by tracking dividends
Next, you should strive to track the dividends given out, along with the gains and losses of the stock.
You may begin by making a fresh worksheet and include the subsequent columns:
● Trading symbol
● Dividend Percentage
● Payment date and,
● Dividend Amount
Make a capital gains calculation to explain losses.
A capital gains calculation will be beneficial when you decide to sell your shares.
Pay attention to short and long-term profits. Then create a new worksheet, to begin with, and then add seven columns with the following categories:
● Ticker symbol
● Shares sold
● Date of sale
● Selling price
● Purchase price
● Capital gains
● Long-term/short-term
Formula to calculate your capital gains:
(Selling Price – Purchase Price)*Shares Sold
The purchase amount, P1, which is deducted from the initial selling price, P0, is shown in the graphic below. The capital gains calculation is completed in the table using the proper cell blocks 6 and 7.
-
How do I keep track of investments in Excel?
Filling Out Your Trade Data
To keep track of your investments, put them as a formula in a cell in Excel. Using the =TEXT function, you may text-enter the stock’s current Price into a cell. =TEXT, for instance (B5, “C2”) A string of the format “A1” may be created from any number or text using the =TEXT method.
The current pricing of your stock (ABC) would then be changed to “ABC” in cell C2 with the =TEXT(B5, “C2”) function.
● Use the =NOW() method to input the Price at which you bought the security as text into a field.
The format will be : NOW() + ” dollars” The “mm/dd/yyyy h:mm:ss”
In other words, the =NOW() + “dollars” function would return the current system date and time togethe1r with a dollar sign, followed by the value in column B5, as a string in cell C6.
● Use the =TODAY() method to input the security’s selling price as text into a column.
The format will be: TODAY() + ” $ ” The format of the date and time returned by the =TODAY() method is “mm/dd/yyyy h: mm: ss.”
The =TODAY() + “dollars” method would return the current system date and time along with a dollar sign followed by the value in column B6 as a string in cell C6.
● For-profit calculation of the investments use the formula: =C5-C6
● Calculation of profit from all investments using the formula: =SUM(C5-C6)-SUM(C7-C8)
