In today’s fast-paced financial world, keeping track of stocks and investments is crucial for both individual investors and financial professionals. Spreadsheets have long been used for this purpose, but there are more efficient and dynamic solutions available. In this tutorial, we will explore how to create a daily historical track of your stock portfolio using Google Sheets and Sheetgo, a powerful cloud-based data integration tool. Sheetgo allows you to connect and consolidate data from various sources, enabling you to build a comprehensive and real-time stock tracking system.
I will use Sheetgo to connect my personal Google spreadsheet and track my daily data. Here’s an example of how it works:
- Open the personal stock portfolio spreadsheet and create a copy for yourself
Step 2: A brief explanation of each tab of the Stock Portfolio Tracker
Dashboard: The dashboard tab will display your current portfolio holdings.
My Holdings: This is where you will input your transactions
Daily Historic: This is where Sheetgo will append the portfolio daily change
Daily Historic: Append Settings: This is the information Sheetgo will use to append the daily historic
Monthly History: Your holdings monthly history
TOCK LIST: This is a list with all NASDAQ companies. It’s used on the Dashboard to fetch the name of the company and its industry. You can add any other company as you wish
Step 3: Setting Up Your Workspace
Sign up for a Sheetgo account: Visit the Sheetgo website (sheetgo.com) and create a new account if you don’t already have one. Sheetgo offers a free account.
Install and launch Sheetgo as an add-on in your Google Sheets.
In Sheetgo, create a new connection by clicking on “Import data from file”
Select “Sheet file(s)” to choose the spreadsheet/tab you will need to connect. At this point, if you haven’t done so already, Sheetgo will prompt you to authenticate and authorize access to your Google Drive, required to access your spreadsheet.
Search for and select the Personal Stock Portfolio spreadsheet and select the tab “Daily Historic: Append Settings”, click in Done and then Next Step.
Click in + Add data processor, and choose Filter -> Query
In the query editor type in the following query:
SELECT * LIMIT 1
Click on Done editing
Now let’s configure the Historical behavior by clicking on the Destination spreadsheet
On the FILE TAB type “Daily Historic”. This is the name of the tab where Sheetgo will input the data
Turn on the Append feature under the APPEND DATA section
Click Finish and save.
