Welcome back! I trust your weekend is off to a restful start. As a teacher these last few weeks of school are pretty exhausting and to state it plainly, I’m tired. If you’re finding yourself in a similar position today, keep the faith. Jesus is waiting at the end of that rope!
With that, I thought someone out there may find value knowing how to create an equity graph in Google sheets. For years, I struggled to find a method that would compile all of my data to ACCURATELY graph an equity curve. In this post I’ll walk through what finally worked for me to create an equity graph that’s actually useful and above all, accurate.
Let’s get into it…
Post Agenda
How to Create an Equity Graph in Google Sheets
Likely, if you’ve attempted this feat before you’ve hit a few hurdles. Some of the challenges I faced when I started looking to create an equity graph in Google sheets were reconciling new or closed positions, making contributions, or simply accounting for a dividend. No matter what I tried, the numbers just didn’t add up to create a comprehensive equity curve.
That led me to Google’s App Script functionality and in turn, to AI tools such as Perplexity or Google Gemini. I’ve grown accustomed to these two AI tools but there are others that may be better suited to this task. Regardless, with the help of AI I was finally able to create a code that met my requirements.
No more trying to create a ridiculous formula that accounted for everything. Instead, each day as my “total portfolio value” was determined, I would simply have the script copy/paste that value to create the graph. However, creating that value may also be a conundrum for those less initiated. Here are the calculations I use to create an equity graph in Google sheets.
Uninvested Cash from all Accounts
If, like me, you’re tracking multiple accounts you’ll first want to add up the uninvested cash in each of those accounts. This could be done in a separate cell or within the “total portfolio value” calculation itself. I prefer to have it separated so I can see how much cash I have on the sidelines but either way, it’s equity and should be included.
Market Value of All Investments
Somewhere within your spreadsheet you’re going to need a column to add up the market value for each investment. This isn’t overly complicated and is usually as simple as a Google Finance formula. For example, if I wanted the current market price for SPY, that formula would look like this; [ =googlefinance(“SPY”, “Price”) ]. More information about Google finance functions can be found here.
That said, I determine my own market value for each investment by taking the “Amount Invested” and adding it to the current “Unrealized P/L”. Either way is sufficient but others may arrive at ‘Market Value’ differently by including ‘Realized P/L’, ‘Dividends Received’, or any other relevant metric. In my case, those calculations are already included in my cash balance so accounting for it again would create a wildly inaccurate equity graph. Just be careful when pulling multiple calculations together that the same data isn’t being compiled more than once.
My Stock & Options Portfolio Sample
In the second image you’ll see the highlighted area with “Portfolio Value” and once that number is derived, you’re ready to get to work on the Google app script code. With that code you’ll be able to create the “Portfolio Value” graph shown in the first image. AKA, the equity graph.
Google Sheets App Script for the Equity Graph
Below is the exact Google App Script code I used to create the sample equity graph spreadsheet you can access. I’ll let you know now, Google really makes App Script look dangerous. They make it seem like your computer is about to explode with their warnings and permissions. That said, I have no desire to harm you or your computer. The spreadsheet is 100% safe to use but in case you decide against it, the entire code is below.
Sample File App Script Code
function myFunction() {
var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getSheetByName("EquityGraph");
var lastRow = sheet.getLastRow();
var rangeL = sheet.getRange("L2:L" + (lastRow + 1));
var valuesL = rangeL.getValues();
//-------------------------------------------------------------
//Begin Column L
var firstEmptyRow = -1;
for (var i = 0; i < valuesL.length; i++) {
if (valuesL[i][0] === "") {
firstEmptyRow = i + 2;
break;
}
}
if (firstEmptyRow !== -1) {
sheet.getRange("L" + firstEmptyRow).setValue(sheet.getRange('M1').getValue());
}
//End Column L
//-------------------------------------------------------------
}Final Thoughts
As you get started using Google app script, I strongly recommend using an AI tool to help. Admittedly though be prepared because those tools will throw off plenty of errors. However, the code found above is 100% functional so that will provide a great assist as you begin. You’ll probably need to alter the code slightly to account for your own criteria or at the very least to call the proper page, cells, etc.
I’m not overly fluent with app script code so while I know this works there may very well be a simpler approach. Someone with extensive knowledge may be able to create the code more efficiently. Still, this hasn’t failed me in over a year so it’s good enough for me.
Lastly, and something I didn’t mention in the post, is app script triggers. I got tired of logging in each day to run the script so I eventually set a trigger. Triggers are accessed from the apps script page on the far left menu. Hover over the icons to get it to expand, from there you can set it to run anytime you prefer.
Until the next post.
God bless,
Jeff
