Table of Contents
Have you ever wanted to have up to date prices of your investments in Notion? Or maybe a chart that is synchronized with your data?
If yes, then your are in the right place. If no, but you are still curious about how to keep your Notions databases in sync with Google Sheets, create charts and use Sheets formulas with your data, then you are welcome to continue reading.
What we will be using
First thing first, duplicate the Stock Template in your Notion account.
Install Notion2Sheets and connect the workspace that contains the template you’ve just duplicated. Remember to select the page when giving permissions.
For step by step details please take a look at the next Youtube video:
Sync the Assets database
Once you have your workspace connected with Google Sheets and the template in your account, is time to sync the database. You should end up with a sheet that looks like this:
We can hide and move columns if we want and the sync process will still work as each column is uniquely identified. You can also change the names of the headers as well. Now we can clean the table a little bit, hiding Created, Updated and Page ID and reorder the columns.
Using GOOGLEFINANCE formula
Now we have the table in sync with Notion, records will be updated every 6 hours or you can update them manually when you want. Having the data available in Google Sheets will allow us to do two nice things, create charts and add additional information like the current price and the percentage of each investment.
The first thing we are going to do is to add a new column, Trend One Year. We will use two handy formulas that are available in Sheets, =GOOGLEFINANCE() and =SPARKLINE(), one to fill the Current Price column and the other to show a trend line in Trend One Year. I have added the links to the documentation for each if you want to take a look.
In Trend One Year we will plot a spark line with the last year of pricing information for the specific asset, the formula looks like this
=SPARKLINE(GOOGLEFINANCE(C2, "price", TODAY() -365, TODAY())) and for Current Price as the name indicates, the current market price of the asset
C2 in this case is the Symbol column.
GOOGLEFINANCE allows you to fetch current or historical securities information from Google Finance, including price, volume, market cap and more.— Notion2Sheets 🔄 (@Notion2Sheets) August 6, 2021
🔄 We can use it to get the current price of our assets and keep it in sync with Notion!
We will also go ahead and calculate the percentage of each stock for the portfolio with
=H2 / SUM($H$2:$H$12). Investment is a formula property but for now, we will do the calculation ourselves to avoid dividing by 0,
Creating the synced columns
We want to have the values of Current Price and Percentage automatically updated in Notion, to make this happen we will use synced columns, which allow us to make a column be Sheets → Notion, take a look at this short video to see how to create them:
Bonus track: How to set dynamic page titles in Notion
Creating a chart
With our database in sync, now we will create a pie chart to show the type distribution of our assets and embed it back into Notion.
Select Type and Percentage columns and then go to Insert -> Chart. For this example we will create a pie chart, so in the right sidebar change the type to Pie chart. You can add a title and modify the options as you prefer.
Embedding the chart in Notion
Now we will embed the chart in Notion using the Embed block.
Go to File -> Publish to web. A modal will appear where we need to select if we want a link or an embed. Select Embed and the chart that we previously created from the options. Copy the code, we will need it now to embed the chart in Notion.
Back into Notion, add an Embed type of block and paste the code, you should now be able to see the interactive chart! Notion updates the content every time you open the page and Google Sheets updates it every 5 minutes.
Ok that was a lot, we were able to sync a Notion database with Google Sheets, use formulas to display additional information for each asset, create a chart and embed both back into Notion with the help of the publishing feature of Google Sheets and the Embed block in Notion.
Do you have other ideas where this setup could be useful? We would love to hear from you ❤️