Airtable API
Learn how to automatically track stock prices with the Airtable API
This is a step-by-step tutorial to build an app that tracks stock prices in Airtable. We will set up an app to run automatically once per day, and each day it will fetch the latest price for a chosen NASDAQ stock, and add that price to a new row in an Airtable table.
The tutorial is divided into two parts, with the first part covering how to fetch stock prices every day and the second part covering how to add stock prices to Airtable using the Airtable API.
If you’re only interested in writing records to Airtable skip to Part B.
Part A – Fetching stock prices every day
Setting up an API key
Deploying an app to run every hour
Fetching stock price information
Selecting the latest price
Part B – Adding stock prices to Airtable
Setting up a table
Getting ready to use the Airtable API
Adding stock price records to the table
Part A – Fetching stock prices every day
There are many different APIs to choose from for current stock price data, for this example we’re going to use the YH Finance API. It’s free to use if you’re making less than 100 requests per day.
1. Setting up the API key
This API is free to use, but you will need to create an account to get an API key: https://financeapi.net/dashboard
As soon as you log in, you’ll be shown your API key.
Now that you have your API key, copy it to your clipboard and head over to SplootCode.
In a new SplootCode project, paste your API key into the Environment Variables so that it’s stored securely and won’t be accidentally shared with anyone else.
(An API key is like a password, you don’t want someone else finding it and using up all your free requests.)
To use that API Key in our project, there are a few lines of code to add. You don’t need to type it out, just drag and drop the example usage from below the environment variables. While you’re here, you’re going to need to import the requests module too, so you can send API requests.
2. Deploying an app to run every day
The YH Finance API can give you historical price information going back months but if you copy that data into Airtable, it won’t stay up to date. What you want is an app that runs every day after trading closes and updates Airtable with the latest price so that the table is always automatically up to date.
To set up your app to run on a schedule, find the Deployment panel on the left of your SplootCode project.
Select the “on a schedule” trigger
Click the “Create handler function” button – this will create a new function called ‘handler’ which you’ll be able to select as the handler function for your app.
Then choose the time that you want to run your app each day.
The rest of the code we’re going to write should go inside this new handler function. This is the function that will get executed when the scheduled trigger happens.
Also add a print call to the handler function, so you can check that it’s set up correctly.
Once you’ve done that, you are ready to click ‘Deploy’.
Once your program is deployed, click the “Test run” button and check that your message shows up in the logs.
(Logs are to the output messages from your deployed app)
Congratulations! Your app is now set up to run every day at the same time. We just need to make it fetch the stock prices and add them to Airtable.
3. Fetching stock price information
Firstly we need to know which stock price to fetch, let’s use Apple as an example.
The URL endpoint for fetching past prices is [<https://yfapi.net/v8/finance/spark>](<https://yfapi.net/v8/finance/spark>) and it requires a few pieces of information:
The API Key you created before – as a special request header
X-API-KEYSome URL parameters for which Stock symbols to fetch prices for, and over what time period
The data in the response should look something like this:
The API will return prices to a 5-minute granularity, and the last prices in the “close” list is the most recent price.
4. Selecting the latest price
Part B – Adding stock prices to Airtable
So, now that you have a program that can fetch recent stock prices, you need to set up an Airtable Table to have somewhere to keep all this information.
Let’s dive into it.
1. Setting up a table
In Airtable either select an existing base if you have one that you’re using for financial data, or create a new one.
The name of the base and the name of the table don’t matter – choose whatever you like, and you can always change it later.
Each base and table in Airtable have a unique ID that stays the same when you change the names. We can use use those IDs to identify the correct table for adding stock price records.
For this example, the new table should have four columns. You can duplicate this template to get started, or rename each field:
An ID field with an Autonumber so that each row has a unique ID
A Symbol field for the stock symbol, e.g. “AAPL”
A Date field which automatically adds the time when a new record is created
A Price field with type “Currency”
With the fields configured like this, the ID and Date fields will be automatically filled by Airtable, we only need to worry about adding information for the Symbol and Price fields.
2. Getting ready to use the Airtable API
In order to allow SplootCode to write data into your Airtable base, you are going to need an Airtable personal access token.
You can create one in the Airtable developer hub at this link: https://airtable.com/create/tokens.
On the next page you’ll be asked to fill out some settings for this new token.
A name – give this token any name that will help you remember what it’s used for.
Scopes – for this example you will only need the
data.records:writescope since our app will only write new records, not read the existing records.A base – The token will only allow access to the Airtable bases you select, make sure you include at least the base that contains the stock price table you created.
When you’re done, **copy the token from Airtable and jump back into your SplootCode project.
You’ll then want to paste the token as an environment variable back in your SplootCode project and make it secret.
Now you can add the Airtable token to your code at the top of your program with your other environment variables.
There’s just a few more bits information we need to be able to add records to Airtable. Let’s jump back to the table you created in Airtable.
Notice the URL in the browser. It includes a unique ID for the base and for the table, it should look like this.
There are two parts to this that we need to copy: the base ID (which starts with “app”) and the table ID (which starts with “tbl”). The last section of the URL is for the view ID which we don’t need today.
Copy those two sections from the URL and create variables in your code for each one.
With the access token, the base ID and the table ID, we finally have everything we need to use the Airtable API. Hooray!
3. Adding stock price records to the table
Last step! We just need to send an API request that adds a new row to the Airtable table with the stock symbol and the price that we found earlier.
The URL that we send a request to needs to include both the base ID and the table ID.
As for the Airtable access token, that should be included in an Authorization header like so:
The API lets us add more than one record at a time to the table, so we need to create a list of records to add. Each record must be a dictionary with a ‘fields’ key containing the information we want to add.
It’s important that the names of the fields exactly match the names that you gave them in Airtable. In this example they are named “Symbol” and “Price” and even the capitalization must match exactly, so be careful whether you gave the names capital letters or not.
Once the request is sent, print the response so that you can see if it worked.
A successful response will list exactly what fields were added.
Back in Airtable, the latest price is added to the table. (And don’t forget to delete the empty rows that were there before!)
Once you app is working, you’ll also need to update the deployed version in the Deployment panel.
Congratulations! Now the latest stock price will be automatically added to Airtable every day at the time of your choice.
Troubleshooting
Here are some of the errors that you might see from the Airtable API, and what they are likely to be caused by.
INVALID_PERMISSIONS_OR_MODEL_NOT_FOUND
There are a few things that could cause this:
Either the base ID or table ID are incorrect – check that the base ID and table ID in the code match what is in the URL bar when you are looking at the table in Airtable. The base ID should start with “app” and the table ID should start with “tbl”.
The Access Token does not have permission for the specific base that the table is in. Go to the developer hub and check that your access token lists the correct base. You can add access to a base without creating a new access token.
AUTHENTICATION_REQUIRED
This error happens when the access token hasn’t been included in the request correctly. Check that the environment variable with the Airtable access token has been included in the request headers correctly.
UNKNOWN_FIELD_NAME
If you see this error message from the Airtable API, double and triple check that the field name exactly matches the columns in Airtable – even the capital letters.
Last updated
