Import Financial Modeling Prep Data to Google Sheets

In this guide, we’ll walk through how to pull historical and realtime market data from the Financial Modeling Prep API directly into Google Sheets, using the API Connector add-on for Sheets. The end of this article will provide a Sheets template you can copy to produce interactive reports like this:

financial-prep-modeling-gif1

PART 1: GET YOUR FINANCIAL MODELING PREP API KEY

  1. If you haven’t already, create an account at https://financialmodelingprep.com/register and verify your email address.
  2. Log in to your account at https://financialmodelingprep.com/developer/docs/login/.
  3. Select a pricing plan to get started with the FMP API. For this example, we’ll choose the free plan, which provides 250 requests.
    financialmodelingprep-img1
  4. Now, navigate to the developer docs located at https://financialmodelingprep.com/developer/docs/. Towards the top of the page you will see a block containing your API key. Keep this handy as we’ll need it for all requests. You now have access to the FMP API!
    financialmodelingprep-img2

PART 2: CREATE YOUR STOCK PRICE API REQUEST URLS

We’ll follow the Financial Prep Modeling API documentation to access two types of market data:

1) Historical Prices for a Stock Symbol

  • API root: https://financialmodelingprep.com
  • Historical Endpoint: /api/v3/historical-price-full/TICKER_SYMBOL
    Historical Endpoint Example: /api/v3/historical-price-full/AAPL
  • Query String: ?serietype=line&apikey=YOUR_API_KEY

2) Real-Time Stock Prices

  • API root: https://financialmodelingprep.com/api/v3/
  • Real-Time Endpoint: /stock/real-time-price/TICKER_SYMBOL
    Real-Time Endpoint Example: /stock/real-time-price/AAPL

Putting these together, we get our two API Request URLs:

Historical: https://financialmodelingprep.com/api/v3/historical-price-full/AAPL?serietype=line&apikey=YOUR_API_KEY
Real-Time: https://financialmodelingprep.com/api/v3/stock/real-time-price/AAPL&apikey=YOUR_API_KEY

PART 3: PULL STOCK API DATA INTO GOOGLE SHEETS

We can now enter all our values into API Connector and create a stock tracker in Google Sheets.

  1. Open up Google Sheets and click Add-ons > API Connector > Open.
  2. In the Create tab, enter the API URL we just created.
    financialmodelingprep-img3
  3. This API doesn’t require any headers so just leave that section empty. We don’t need any extra authentication either, so just leave that set to None.
  4. Create a new tab and click ‘Set current’ to use that tab as your data destination.
  5. Name your request and click Run. A moment later you’ll see historical data for AAPL appear in your Google Sheet:financialmodelingprep-img4
  6. Repeat the above steps for real-time prices, substituting in the real-time request URL.
    financialmodelingprep-img5

PART 4: GET REALTIME/HISTORICAL STOCK PRICES WITH IMPORTAPI()

The above is enough to grab data whenever you hit Run in the sidebar, but API Connector also enables more dynamic refreshing via a custom function called IMPORTAPI() (documentation). This function references your existing sidebar queries and lets you refresh them by changing the value in a cell.

The syntax for this function is =IMPORTAPI(requestName, url, cell).
requestName (required) refers to the request name in API Connector,
URL (optional) is the URL you’d like to reference, and
cell (optional) allows you to identify a cell that will trigger fresh API calls.

1) Historical Prices for a Stock Symbol
In this historical prices example, cell A1 is the input field for our stock symbols. Cell C1 will reference this cell with the following formula:

=IMPORTAPI("FMP_Historical","https://financialmodelingprep.com/api/v3/historical-price-full/"&A1&"?serietype=LINE&apikey=YOUR_API_KEY")

fmp-img4
Now, every time you edit your stock symbol in A1, the sheet will immediately retrieve that symbol’s historical data. See the gif at the top of this article for an example of the sheet in action.

2) Real-Time Stock Prices
For real-time stock prices, again we’ll create a sheet with cell A1 as the input field for our stock symbols. Cell C1 will reference this cell with the following formula:

=IMPORTAPI("FMP_RealTime","https://financialmodelingprep.com/api/v3/stock/real-time-price/"&A1&"apikey=YOUR_API_KEY",A2)

Note that this function makes use of the third, optional argument by referencing cell A2. Add a checkbox to A2, so that you can refresh your data by simply ticking/unticking the checkbox in addition to changing the symbol.
financial-prep-modeling-gif2
Tip: you can fully automate this refreshing by adding a function like =GOOGLEFINANCE("CURRENCY:USDEUR") into cell A2, instead of a checkbox. If you do that, the data will refresh every time the Google Finance call runs, approx. every 2 minutes. Just beware that this will quickly exhaust the free plan provided by FMP. In addition, you may run up against Google’s quotas and limits, which can prevent your data from refreshing if you run too many requests.

PART 5: GET THE GOOGLE SHEETS + STOCK PRICE TRACKER TEMPLATE

Click this link to copy the above template for yourself: Stock Price Tracker

8 thoughts on “Import Financial Modeling Prep Data to Google Sheets”

  1. Hi,

    How can you check how many requests you have made on Financial Modeling Prep? I’m using a free API key to test out my app; thus, it’s quite important for me to know the number of requests I made.

    Another question: On Financial Modeling Prep, do we have a rate limit for APIs, such as 10 requests/sec or some other amounts?

    Thank you in advance!

    Reply

Leave a Comment