Tracking your trades is a critical part of being a successful trader. Not only does it allow you to keep track of your profits and losses, but it also allows you to analyze your trading strategies to see what works and what doesn’t.
However, just tracking your data is not enough—you also have to analyze it to make better trading decisions in the future. This article will show you how to track and analyze all your trades using Excel spreadsheets.
Table of Contents
Investing vs. Day Trades
Investing
Investing involves buying and selling securities to hold for a long time, such as a month or years. The goal of investors is to keep their securities until they increase in value enough to generate a positive return.
For example, if you bought a stock for $100 and it increases to $200 in 1 year, you would make a profit of $100 ($200 – $100). For this example, the stock would be called “up” since it was up $100 a year.
Day Trading
Day trading is a short-term strategy that involves buying and selling daily stocks to make small profits on each trade.
The idea behind day trading is that you buy and sell stocks many times in a single day, so even a small profit adds up quickly.
For example, if you bought Apple at $100 and sold it at $101, you made a profit of $1 on your investment. If you traded that stock several times in one day, you would make several small profits that add up by the end of the day.
Since the stock was up $1 a day, it would be called “up.”.
The difference
It takes emotional discipline to be successful at day trading and investing. In other words, you must be able to deal with loss and excitement during the timeframe you’ve given yourself.
Different skills and qualities are required when deciding on a day trade than when deciding on a long-term investment.
Investing requires less monitoring and extreme patience than day trading, which requires more daily attention.
Excel spreadsheets can keep track of both types of investments and allow you to compare results over different periods to see which is more profitable for you.
Related » What Is a Day Trading Journal
In addition, Excel courses for data analysis are available to help traders analyze their portfolios and create more efficient strategies for the future.
How to Track Your Trades in Excel
With Excel Business Intelligent tools, day traders can analyze their portfolios and compare their returns against various benchmarks and markets to make better future decisions.
Entering Your Trade Data
The first step to keeping track of your trade is to enter it into Excel as a formula in a cell. To enter the current price of a stock as text into a cell, use the =TEXT function. For example, =TEXT(B5, “C2”) The =TEXT function converts any number or text into a string in the format “A1”.
So, the =TEXT(B5,”C2 “) function would convert the current price of Apple into “Apple” in cell C2.
To enter the price when you purchased the security as text into a cell, use the =NOW() function.
For example: =NOW() + ” dollars” The =NOW() function returns the current system date and time in the format “mm/dd/yyyy h :mm:ss”.
So, the =NOW() + ” dollars” function would return the current system date and time plus a dollar sign followed by the value in cell B5 as a string in cell C6.
To enter the price when you sold the security as text into a cell, use the =TODAY() function.
For example: =TODAY() + ” dollars ” The =TODAY() function returns the current system date and time in the format “mm/dd/yyyy h: mm: ss”.
So, the =TODAY() + ” dollars” function would return the current system date and time plus a dollar sign followed by the value in cell B6 as a string in cell C6.
To calculate the profit from securities as text into a cell, use the formula: =C5-C6.
To calculate the total profit from all investments as text into a cell, use the following formula: =SUM(C5-C6)-SUM(C7-C8)
Note: If this formula returns a #VALUE! error, when executed, means that one or more cells contain errors. Double-check the formulas entered and ensure there are no typos or the wrong function in a cell.
Viewing Your Trade Data
After entering your trades into Excel, you can either view them all at once or in separate worksheets.
If you want to view the information all at once, enter a blank worksheet at the top of a new workbook and copy/paste the data from your various cells into the sheet.
Then, save the workbook as a CSV file (comma-separated values) or tab-delimited file (.txt). You can then open or import the file into a statistical program to view the data in more detail.
How to view your trades in Excel with PowerPivot
Create a PowerPivot data model if you want to view your trades in separate worksheets. PowerPivot is a feature of Excel 2010 and Excel 2013 that is installed with Excel and can be used to create relationships between data in multiple workbooks and format the data for analysis.
To create a PowerPivot data model, select Data > Get External Data > From Other Sources > From Microsoft Excel Files > From Existing File.
In the From Microsoft Excel Files window, browse to the file you want to create a data model from and then click Select. If the workbook contains more than one worksheet, select the worksheet containing the data you want to create a database. Click Close.
In the Import Data window, click Next to see the options for creating a PowerPivot data model. Click Finish and then click Finish again.
If the worksheet you selected contains multiple sheets, you may see the following statement in the Import Data window: The workbook contains multiple sheets. Would you like to create a PivotTable and PivotChart for each of them? Click Yes.
A PowerPivot window opens. Select New from the PowerPivot window menu and click the + symbol next to the PowerPivot window. The New Data Model window opens.
In the Data Model Connections window, select Multiple Workbooks and click Next. Select your Excel file in the Select Data Source window and click Finish.
A data model shows the trades you made in your Excel file. If you then click the PowerPivot window, you’ll see a data model that lists your trades.
To create a new sheet in your workbook that displays your data, click the Create tab and the PivotTable button. In the Create PivotTable window, select New Worksheet (to create a new sheet in your workbook ) from the PivotTable Type dropdown and click Finish.
Note: PowerPivot is an object-based data modeling tool that uses the data contained in your workbook to analyze and display the data in multiple ways.
Related » How to Track Your Progress with a Trading Journal
Conclusion
By using a spreadsheet to keep track of your investments and calculate profit and loss, you can more easily keep track of all your assets, including stocks, mutual funds, ETFs, options, bonds, and other securities.
You can also use the spreadsheet to create charts of your portfolio’s performance over time and use the results as the basis for making investment choices in the future.
And by using a spreadsheet to record transactions in your account, you can easily see the value of your account at a glance and can use this information to help set your goals for the future.
