Hello everyone, I hope today was just what you needed. A chance to break from the busyness and endless ladder climbing of life. Interestingly, in Matthew 6:33 we’re instructed, “But seek first His Kingdom and His righteousness, and all these things will be given to you as well.” What a thought, we don’t need to pursue anything? If we seek Him above everything else, He will just throw all the other stuff in as a bonus!
For today’s post I wanted to share how to create interactive diversification graphs in google sheets. Personally, I don’t use my diversification graphs for options trades but that is certainly possible. If you end up creating something awesome to track your options trades with this information I hope you’ll come back and share. 🙂
Before we get to how I create interactive diversification graphs in google sheets, you may also like to view a post I did earlier this year, “Building a Portfolio Tracker in Google Sheets”. I don’t go into specific calculations but I believe it will help build the foundation to a robust tracking spreadsheet in Google Sheets. Additionally, if you prefer to have something already complete and ready for use, you may have a look at my OptionBoxer Portfolio – Stock and Options Journal.
Now, let’s create interactive diversification graphs in google sheets! To begin, here is an image of my personal diversification graphs to highlight what I’ll be walking through in this post.
Post Agenda
Diversification Graphs Overview
Before I start drilling down into how I created the above charts let’s quickly consider why they’re useful. The diversification graph is more than likely the most viewed graph on any tracking spreadsheet or platform. In a few clicks we can see where all our money is and exactly how it’s allocated. Have a look at this live demo from Snowball-Analytics, the only two graphs on the main screen are, you guessed it, diversification graphs. They are a simple but elegant way to showcase some specific data.
For instance, maybe you heard gold is doing well (it is by the way) and you want to know how much you currently hold. Well, the diversification graph will show you how much you hold and compare it to every other holding. Of course, we could just break out the calculator and run a quick portfolio audit but as I mentioned in the first paragraph, we’re kind of busy. So for me, the diversification graph is an absolute must and is generally one of the first graphs I create. I personally want to see exactly where every dollar is. After all, isn’t that the purpose of tracking a portfolio?
Building the Foundation – Create Interactive Diversification Graphs in Google Sheets
Alright, first things first, what do we need to create interactive diversification graphs in google sheets? To keep this post simple, I’ll focus on just creating a sector diversification graph but the steps to create the asset class graph are exactly the same.
- List of Assets (Ticker Symbols)
- List of Assets Value
- List of Each Assets Sector
- List of Total Values by Sector
Not to bad, probably information that is already being calculated if you’re using a spreadsheet. It really just means putting it together in a way you can graph what you’re wanting to see. Which leads us to the calculations required to complete the process.
Calculations – Create Interactive Diversification Graphs in Google Sheets
Let’s assume a blank canvas and build this step by step. Here is a sample spreadsheet that will assist as you follow along.
- A1 – “Symbols”
- A2:A?? – List of Symbols
- B1 – “Investment Value”
- B2:B?? – List of Assets Values
- C1 – “Sector”
- C2:C?? – List of Assets Sector
- D1 – “Sector List”
- D2 – ” =UNIQUE(C2:C14) “
- The unique formula should extend to the end of your list of assets sectors.
- E1 – “Total Value by Sector”
- E2 – ” =SUMIF($C$2:$C$14, D2, $B$2:$B$14) “
- Again, this sumif formula should extend to the end of each list of data. The criterion is the specific sector.
- Drag this formula down to the end of the unique sector list.
Now that we have the data, let’s add 2 more columns so that our diversification graph can be interactive. Basically a lookup function so that only our selected data is extracted from our primary data above.
- F1 – “Symbol by Category”
- F2 – ” =IF(H2=””, FILTER($A$2:$A$14, $A$2:$A$14<>””), FILTER($A$2:$A$14, $C$2:$C$14=H2)) “
- This formula will use the selected sector in cell H2 to find every symbol from that sector.
- G1 – “Symbol Value”
- G2 – ” =IFERROR(INDEX($B$2:$B$14, MATCH(F2, $A$2:$A$14, 0)), “”) “
- Drag this formula down as far as needed so that every symbol from a given sector can be displayed.
- H1 – “Sector Selection”
- H2 – Select Data > Data Validation > Add Rule > Criteria > Dropdown (from a range) > Highlight the list of unique sectors
That’s pretty much it for the worst part of building anything in Google sheets. Nobody likes calculations because they generally require some trial and error to get things functioning appropriately. But now it’s time to put it all together.
Putting it All Together – Create Interactive Diversification Graphs in Google Sheets
Now that we have our data put together the way we need it and we’ve created the sector selection box. We really just need to add the graphs and format them to display the information we want.
- Start by inserting a graph. Select Insert > Chart.
- I like doughnut charts but pie graphs work just the same.
- Data Range – G2:G14
- Data Range – F2:F14
- Label – F2:F14
- Value – G2:G14
- Check to use column F as labels
- Customize > Legend > Position > Labeled
That’s it, every step to create interactive diversification graphs in google sheets. From there, the graph can be customized further to match the look and feel you’ll prefer. In the sample workbook I left everything with the stock settings but Google has plenty of customization options so definitely feel free to make it your own.
With that, if you have any issues or questions about creating your own diversification graphs please reach out in the comments.
Until the next post.
God bless,
Jeff
