- How to Balance Your Checkbook or Bank Account Using an Excel-Style Spreadsheet
- 10 Laws of Daytrading
- Advantages and Disadvantages of a Diversified Portfolio
- How to Buy a Silver Stock Direct Investment
- What Is the Role of the Financial Analyst in a Large Organization?
- How to Calculate Beta for Individual Stocks in Excel
Whether you’re a long-term investor or a day trader, you obviously understand your success hinges on investing in performing stocks rather than those that lose money. There’s more to your bottom line than merely correctly picking investments: Maintenance fees and transaction fees can significantly alter the value of your portfolio if you don’t actively take their impact on your brokerage account into consideration. Monitoring your equity curve, a statistical representation of your account’s total value, and may provide you with insight about your investment strategies and a glimpse at money management for traders who may run high levels of additional fees.
Determine the scale of your trading. Long-term traders may only need to monitor their equity curve on a weekly or monthly basis, while day traders and others who are more active in the market should track their equity on a daily basis.
Format the spreadsheet so that one column, Column A, references dates, a second column representing your daily equity in Column B, and the average equity in Column C. Label Column D “Five-Day Rolling Average.” Label the column appropriately.
Enter the formula =Average(B:B) in the first blank cell -- C2 -- in the average column. This will provide an average of your equity for each day you report. Click on this cell, then click and drag the black square in its bottom right-hand corner, extending the selected column across several rows. This copies the formula into every cell that you highlight.
Enter the formula =AVERAGE(B2:B6) in the first blank cell -- D2 -- in the five-day average column. Click on the cell, then click on the box that appears on the right hand of the cell and drag it across several rows.
Enter historical data in your spreadsheet, if it’s available. Your brokerage account may be able to report its value at the close of every trading day if you sort historical data. Going back as far as you wish to monitor the curve -- day traders should monitor a curve of at least 30 days, while longer-term investors may need to track the monthly value of their account for its lifespan -- manually transfer the date and value of your account to the spreadsheet. By tracking your account’s value, your brokerage converts the value of your investments and liquid assets, less any fees you incurred over the course of the day.
Highlight all data in the spreadsheet after you enter your data. Click on the Insert toolbar, then click on the line graph icon. Select the simple line graph.
The table created represents the lifetime equity of your investments, the daily equity in your investments and a rolling five-day average of your investments. This data can then be used to monitor your equity curve.
You must manually enter closing data each trading day into your spreadsheet in order to continuously monitor your equity curve.