Build Your Own Realtime Stock Tracking Dashboard in Sheets

In this new section of the site, I'd like to introduce some projects related to APIs and Google Sheets. All can be used as-is or extended into something even bigger and better. This first project will present a simple interactive, realtime stock portfolio dashboard, which pulls in fresh data on the click of a button. The final result will look like this:

If you just want to get the dashboard, feel free to skip to the final section for the download, otherwise read on for how to build it yourself.

Contents

• Ingredients
• Step 1: Design the Dashboard
• Step 2: Find your API URL(s)
• Step 3: Set up the request in API Connector
• Step 4: Pull stock API data into Google Sheets
• Download stock tracking dashboard for Sheets

Ingredients

1. An instance of Google Sheets. A link to Sheets probably isn't necessary, but nevertheless here it is.
2. A Finance API as our source for stock and financial data. We'll be using the excellent Financial Modeling Prep API.
3. An installation of API Connector. This will connect the Financial Modeling Prep API to Google Sheets.

Design the Google Sheets Stock Tracking Dashboard

We'll create 10 stock blocks, each containing the stock symbol and space for key metrics underneath. Our dashboard should look like this:

For now, just fill in the names of ten stocks you're interested in tracking (it doesn't have to be the stock symbols shown in the screenshot). Later on you can go wild and add more or customize the design, but for this first round let's keep it simple. In addition, click Insert > Checkbox in cell A1. We'll need that later. Click the tab and name this sheet 'Summary'.

Find your Stock API URL(s)

The Financial Modeling Prep API documentation is located at https://financialmodelingprep.com/developer/docs/. You will need to get an API key for this (note that the free version of this API is limited to 250 requests total).

There are a lot of endpoints that could be useful, but the /api/v3/quote/ endpoint looks right for our dashboard as it returns all the stock metrics we're looking for: the price, the change percentage, the day's low and high, and so on.

So this will be our API request URL, where X represents each stock symbol we're interested in:

https://financialmodelingprep.com/api/v3/quote/X?apikey=YOUR_API_KEY

Set up the request in API Connector

We next need to add this request into the API Connector sidebar.

1. Click Add-ons > API Connector > Create New API Request
2. In the Create Request interface, enter the Request URL we just created. Don't worry about entering the right stock ticker symbol or API key here, because we'll be using the IMPORTAPI() function, which lets us dynamically add different stock tickers and parameters to the end of the URL.
3. We don't need any headers, so just leave that section blank.
4. Create a new tab. We'll call it 'Data'. While still in that tab, click 'Set' to use that tab as your data destination.
5. Under Output options, let's choose a couple options to make the output more convenient for our dashboard. We'll choose "Append mode" so that we don't print out the header values each time. We'll also choose to add in a timestamp so we can check when our dashboard has last updated.
6. Name and save your request. Again we'll call it 'Data'.
7. That's it! Since we're using the IMPORTAPI() function to trigger our requests, let's now head over to the Data sheet.

Pull stock API data into Google Sheets

1. The IMPORTAPI() function takes the following arguments: request name, url, and refresh cell (documentation). Our request name is "Data", we'll be using the /api/v3/quote endpoint, and cell A1 holds the checkbox that we'll use to trigger our requests. For convenience, we'll also include our API key into cell B1 of a sheet called 'apikey'. Therefore, in the Data sheet, cell A2, enter the following formula:

   =importAPI("Data","https://financialmodelingprep.com/api/v3/quote/"&Summary!$C$6&"?apikey="&apikey!$B$1,Summary!$A$1)

2. Copy down the request 9 times (for a total of 10). In each formula, update the find endpoint segment that references your stock symbols, so that your URLs look like this:

   =importAPI("Data","https://financialmodelingprep.com/api/v3/quote/"&Summary!$C$6&"?apikey="&apikey!$B$1,Summary!$A$1)

   =importAPI("Data","https://financialmodelingprep.com/api/v3/quote/"&Summary!$E$6&"?apikey="&apikey!$B$1,Summary!$A$1)

   =importAPI("Data","https://financialmodelingprep.com/api/v3/quote/"&Summary!$G$6&"?apikey="&apikey!$B$1,Summary!$A$1)

   =importAPI("Data","https://financialmodelingprep.com/api/v3/quote/"&Summary!$I$6&"?apikey="&apikey!$B$1,Summary!$A$1)

   =importAPI("Data","https://financialmodelingprep.com/api/v3/quote/"&Summary!$K$6&"?apikey="&apikey!$B$1,Summary!$A$1)

   =importAPI("Data","https://financialmodelingprep.com/api/v3/quote/"&Summary!$C$21&"?apikey="&apikey!$B$1,Summary!$A$1)

   =importAPI("Data","https://financialmodelingprep.com/api/v3/quote/"&Summary!$E$21&"?apikey="&apikey!$B$1,Summary!$A$1)

   =importAPI("Data","https://financialmodelingprep.com/api/v3/quote/"&Summary!$G$21&"?apikey="&apikey!$B$1,Summary!$A$1)

   =importAPI("Data","https://financialmodelingprep.com/api/v3/quote/"&Summary!$I$21&"?apikey="&apikey!$B$1,Summary!$A$1)

   =importAPI("Data","https://financialmodelingprep.com/api/v3/quote/"&Summary!$K$21&"?apikey="&apikey!$B$1,Summary!$A$1)

3. You should immediately see data populate into the Data tab, like this:<
4. Back in the Summary tab, set up VLOOKUP (or HLOOKUP, or INDEX/MATCH) formulas to reference these cells and return them into the Summary sheet. For example, these are formulas you can use to populate all the cells for the first stock:
   =iferror(hlookup("price",Data!$1:$11,2,false),"")
=iferror(hlookup("changesPercentage",Data!$1:$11,2,false)/100,"")
=iferror(hlookup("change",Data!$1:$11,2,false),"")
=iferror(hlookup("previousclose",Data!$1:$11,2,false)/100,"")
=iferror(hlookup("open",Data!$1:$11,2,false)/100,"")
=iferror(TEXT(hlookup("dayLow",Data!$1:$11,2,false),"$#.00")&" - "&text(hlookup("dayHigh",Data!$1:$11,2,false),"$#.00"),"")
=iferror(TEXT(hlookup("yearLow",Data!$1:$11,2,false),"$#.00")&" - "&text(hlookup("yearHigh",Data!$1:$11,2,false),"$#.00"),"")
=iferror(hlookup("volume",Data!$1:$11,2,false),"")
=iferror(hlookup("avgVolume",Data!$1:$11,2,false),"")
=iferror(hlookup("pe",Data!$1:$11,2,false),"")
=iferror(hlookup("eps",Data!$1:$11,2,false),"")
5. Final step: let's also reference the time stamp so we can see when the report was last refreshed. Add that to cell C1 using the following formula: =TEXT(Data!A2,"YYYY-MM-DDTHH:mm:ss.SSS")

Now when you either change your stock ticker symbols OR click the checkbox in the top left, your stock data will automatically pull in fresh data from the Financial Modeling Prep API.

Pro tip: if you populate cell $A$1 using =GOOGLEFINANCE("CURRENCY:USDEUR") instead of a checkbox, every time the Google Finance function runs (about every 2 minutes), it will force your API request to refresh at the same time. Just beware of Google's quotas and limits as your data will stop refreshing if you run too many requests.

Download stock tracking dashboard for Sheets

You can grab the template at this link: Stock Portfolio Template.
Notes:
*it's read-only, so click File > Make a Copy before using
*Since the IMPORTAPI() function references requests from the sidebar, make sure to set up an API request (Add-ons > API Connector > Create New API Request) called 'Data' before running, and to enter your own API key into the 'apikey' sheet. See step 3 for details.