Spreadsheet.com is no longer available as a public service. Read more ...
When you evaluate your stock portfolio, looking at your overall return doesn’t tell the whole story. If you want to optimize your investment strategy for more success down the line, you need a set of tools that help you break down your portfolio in different ways. Like traditional spreadsheet software, Spreadsheet.com supports more than a dozen different types of charts to help you do just that.
In this guide, we’ll take a look at a stock portfolio workbook and see how incorporating charts and graphs can help us better visualize and analyze our returns.
Adding charts and graphs to your workbooks can help you visualize your data and gain new insights that may not be apparent when viewing your data on a spreadsheet grid, especially if your worksheet includes hundreds or thousands of rows.
Data visualizations can help you calculate analytics, aggregate large numbers of records, and give you more ways to present your data to others.
For our stock portfolio workbook, we’ll use charts to visualize changes in our portfolio over a three-month observation period.
To create a new chart, click the New chart button in the toolbar above the formula bar. Or, from the workbook menu, select Insert > Chart. Once you create a new chart, you can select a chart type and configure it from the Chart settings dialog on the right side of the screen.
Spreadsheet.com supports more than a dozen different types of charts. Learn more about selecting a type of chart for your data in our Help Center.
We’ll start exploring our portfolio by visualizing a basic metric: its total value. To do this, we’ll create a new line chart. Take a look at the chart on the left – “Total Portfolio Value” – in the image below:
This chart displays the total daily value of our portfolio over a three-month period. It was configured in the Chart settings dialog on the right.
“X-axis” determines the values used on the graph’s horizontal axis. When visualizing time series data, the X-axis is usually a series of dates. Here, we’ve selected Column A - Date for our X-axis values.
“Series” determines the Y-axis values of the graph’s line component. Because our line chart is displaying the value of our portfolio, we’ve selected Column Q - Total for our series.
Line charts can display different series as different lines on the chart. Take a look at the chart on the right – “Daily Close Price” – below.
Like our previous chart, we’re using Column A - Date as our X-axis values. Now, instead of a single series, we’ve added five series - one for each of the five stocks in our portfolio (Columns B through F).
Each series is plotted as a separate line on our chart. In the Chart settings dialog’s Configuration panel, we’ve added a legend to the bottom of the chart so we know which line belongs to which series.
Let’s take a look at our two charts side-by-side.
By comparing our two charts we can see that, over the observation period, the total value of our portfolio rose despite all component stocks losing value. That tells us that our gains were made through changing the composition of stocks in our portfolio. We’ll explore this further with stacked area charts next.
Next, let’s take a closer look at the composition of our stock portfolio with some stacked area charts. Area charts are similar to line charts, but the area between the line component and X-axis is shaded. In a stacked area chart, series are plotted stacked on top of one another instead of plotted in front of one another.
Take a look at the chart on the left – “Total Shares Held in Portfolio” – below:
This stacked area chart displays the number of shares of each stock held in our portfolio, and the uppermost line represents the total number of shares in our portfolio. Like our previous charts, Column A - Date is used for our X-axis values. The “Series” values are taken from Columns G through K.
Stacked area charts let you see a total value and information about its component parts at the same time. With this chart, we can see that the total number of shares in our portfolio rose during the observation period and the distribution of shares across our five stocks changed as well.
We can get a better idea of how that distribution changed with a 100% stacked area chart. Take a look at the chart on the right – “Distribution of Shares in Portfolio” – below:
This chart has the exact same X-axis and series values as our stacked area chart, but each series is now represented proportionally instead of as raw values. Let’s take a closer look at the charts side-by-side.
By comparing these two charts, we can see that the total number of shares in our portfolio grew over time, but the number of shares of some individual stocks declined. For example, about halfway through the observation period, we completely divested from our Southwest ($LUV) holdings. On the other hand, our portfolio didn’t include any United ($UA) holdings at the beginning of the observation period, but were eventually included.
Ready to get started? Browse Spreadsheet.com’s Template Gallery to find ready-to-use finance templates, as well as templates for construction project management, education, sales, and more. Or, visit our Help Center to learn more about working with charts and graphs.