Graphing is one of the most important features of spreadsheets. When you need to present your findings, whether as a written report or a presentation, summarizing your data in graphs is the best way to quickly communicate large amounts of data.
This guide will walk through taking your raw portfolio data, making some simple calculations, and transforming the data into graphs that you can include in reports.
Line Graphs
Your Daily Portfolio Value
First, we want to make a line graph showing our daily portfolio value. First, open your spreadsheet that has your daily portfolio values, it should look something like this:
Portfolio values are calculated at the end of the day when the market is closed and all your assets (Stocks and Mutual Funds strictly on this site) are summed together which shows your ending portfolio value.
To insert a basic line chart of your portfolio data, highlight your data and click “Insert” in the Office Ribbon, or “Charts” in Google Sheets:
And that is it! Your new chart is ready for display. You can even copy the chart and paste it in to Microsoft Word (if using Excel) or a Google Doc (if using Google Sheets) to make it part of a document, or paste it into an image editor to save it as an image to be used for any reports you might have it to use.
Microsoft Excel
Google Sheets
Portfolio Percent Changes
Next, we want to make a graph showing how much our portfolio has changed every day since the tournament has began. To do this, first we need to calculate the daily % change, instead of just our raw portfolio value.
Basic Calculations – Using Formulas
In the next column we will calculate our daily portfolio percentage change. First, in the next column, add the header “% Change”.
Now we need to make our calculation. To calculate the percent change each day, we want to take the difference between the most recent day’s value minus the day before, then divide that by the value of the day before.
Percentage Change = (Day 2’s Value – Day 1’s Value) / Day 1’s Value
To do this, in cell C3 we can do some operations to make the calculation for percentage change. To enter a formula, start by typing “=”. You can use the same symbols you use when writing on paper to write your formulas, but instead of writing each number, you can just select the cells.
To calculate the percent change we saw between day 1 and day 2, use the formula above in the C3 cell. It should look like this:
Now click on the bottom right corner of that cell and drag it to your last row with data, Excel will automatically copy the formula for each cell:
You now have your percentages! If you want them to display as percentages instead of whole numbers, click on “C” to select the entire column, then click the small percentage sign in the tools at the top of the page:
Selecting Certain Columns For Your Graph
Now we want to make a graph showing how our portfolio was changing each day, but if we try to do the same thing as before (selecting all the data and inserting a “Line Chart”, the graph doesn’t tell us very much:
This is because it is trying to show both the total portfolio value and the percentage change at the same time, but they are on a completely different scale!
To correct this, we need to change what data is showing. If you are using Excel, right click on your graph and click “Select Data”:
This is how we decide what data is showing in the graph. Items on the left side will make our lines, items on the right will make up the items that appear on the X axis (in this case, our Dates).
Uncheck “Portfolio Value”, then click OK to update your graph:
For Google Sheets, this is done similarly, right click on your graph and select “Data Range” (the letters for this example will be the same as Excel, C2:C6)
This is closer to what we’re looking for, but the axis labels (the dates) are right in the middle of the graph, making it hard to read.
Formatting Your Line Graph
Now we want to move the dates to the bottom of the graph (here they are along the “0” point of the Y axis).
To do this in Excel, right-click on the dates and select “Format Axis”:
A new menu will appear on the right side of the screen. Here, click “Labels”, then set the Label Position to “Low”.
The method is similar on Google Sheets as well, start by selecting “Axis” then “Horizontal” or “Vertical” Axis to edit them.
With this feature you change the axis titles and add different features to it.
Congratulations, your graph is now finished! You can now easily see which days your portfolio was doing great, and which days you made your losses.
Bar Charts and Pie Charts – Your Open Positions
Next we would like to make a bar chart showing how much of our current open positions is in each stock, ETF, or Mutual Fund.
Directions for Excel
First, open your spreadsheet with your Open Positions. It should look something like this:
Since we want to make a bar chart, we can only have two columns of data – one for the X axis, and one for the Y axis of our chart.
We want one column showing the symbol, and a second column showing how much it is worth. The “Total Cost” column is the current market value of these stocks, so that is the one we want to keep. However, we don’t want to delete the quantity and price, since we might want it later. Instead, select the columns you don’t want, and right-click their letter (A and C in this case). Then, select “Hide”.
Now the columns that we don’t want in our chart are hidden. We can always get them back later by going to “Format” -> “Visiblity” -> “Unhide Columns”.
Now select your data and insert a “Bar Chart” instead of a “Line Chart”:
Before you’re finished, your chart will say “Total Cost”. You can change this by clicking on “Total Cost” and editing to say whatever you would like (like “Portfolio Allocation”).
This graph is now finished, but you can also try changing the Chart Type to try to get a Pie Chart.
Switching Chart Types
Sometimes, our first chart type is not the best way to display our data. For example, a bar chart will show me how much of each symbol I am holding. A better choice might be a pie chart, which will show how much each symbol is as a percentage of my total holdings.
To change our bar chart to a pie chart, right click your graph and select “Change Chart Type”:
Next, find the “Pie” charts, and pick whichever chart you like the best.
Last, now we don’t know which piece of the pie represents which stock. To add this information, click your pie chart, then at the top of the page click “Design”. Then select any of the options to change how your pie chart looks.
Congratulations, you’ve converted your bar chart into a pie chart! This one should look almost the same as the one you have on the right side of your Open Positions page. You can now copy and paste these charts directly into your Word document, or save it as an image to use elsewhere.
Directions for Google Sheets
To create a Bar Graph, select “Insert” then “Chart”, the same as we did for our previous line charts.
When clicking this, one of the options in “Chart Editor”, will be “Chart Type”, their you can select the bar graph or pie chart.
In this section you will also need your Data Range, which will be the same as the previous example (Symbol and Total Cost). To edit the axis and other information, is the same method as the previous type.
The pie chart will look very similar to that on your Open Positions Page as well!
Financial Times
