Being able to fetch real-time stock data in Google Sheets can help you build your trading strategies.
Here in this article, we’re going to learn how to import real-time stock data to Google Sheets using a real-time stock API called Marketstack. The Marketstack API allows us to retrieve information such as intra-day data, EOD data, dividends data, and real-time updates among others.
Using Amigo Data, a data retrieval and automation tool, we can easily import real-time stock data into Google Sheets with Marketstack API.
Below is a snippet of EOD data for Apple imported into Google Sheets.
Steps get real-time stock data in Google Sheets.
- Install the Amigo Data add-on
- Get the Marketstack API key
- Choose the data endpoint
- Import data to Google Sheets
How to get real-time stock data in Google Sheets
1. Install the Amigo Data add-on
Get the Amigo Data add-on from Google Workspace Marketplace.
Click here to install.
2. Get the Marketstack API key
There are several stock quotes API that let us import real-time stock data into Google Sheets. We’ll use the Marketstack API.
Go to Marketstack API documentation and click “GET API KEY”.
Fill in the details and click “Sign Up”.
After successfully signing up, you will be redirected to a new page where you can see your API access key. This is the key we’ll use to get real-time stock data in Google Sheets.
3. Choose the data endpoint
Navigate to the Documentation page by clicking Documentation
On the left side, under the Features section, choose any of the categories of stock data to import to Google Sheets. We’ll choose End-of-day Data.
Note: Some of these data are restricted to the paid plans. So if you’re on the free plan, then you won’t be able to access them.
An example endpoint URL is given with Apple ticker symbol as the default.
We can modify the URL using any or all of the objects under HTTP GET Request Parameters.
We can append the query parameters either in the endpoint URL or enter them in the “Query” field in Amigo (on this later).
For example, to fetch Apple EOD data from 1 Dec 2022 to 31 Dec 2022, we’ll modify the URL as follows.
To add additional parameters, use the ampersand and append the parameter names and their values.
Having got the endpoint URL and modified it with our preferred values, we can now begin to import the stock data from Marketstack to Google Sheets.
4. Import data to Google Sheets
Open a new spreadsheet and launch Amigo Data.
Select Custom API from the list of sources.
Enter the stock API endpoint URL API URL field.
In the Details section, delete the access_key as it contains a dummy API key placeholder and add your stock API key obtained from Marketstack.
We need to add the original API key to make a valid request. Click on the “+Query” option
Enter the “access_key” as the name and your API key in the value section. Click “+ADD QUERY”
Instead of appending the parameters in the URL, we can enter them here as queries. Enter the parameter name and the values and click “Add Query”.
Click the PREVIEW button to retrieve the data and preview them.
Then flatten and expand the dataset so that they are organized suitably. Once that is done, click the IMPORT button.
A few moments later, you’ll have a spreadsheet populated with the data from Marketstack with the high, close, volume, etc.
Shown below is a snippet of the real-time stock data in Google Sheets.
You can likewise import dozens of other types of data using the Marketstack real-time stock data API. Following are some other examples.
Real-time stock quotes API endpoint
This end-point gives you real-time updates about the Apple stock. The key-value pair is separated by “&” sign. In the symbols parameter, you can add a symbol of any stock to fetch the data of that particular stock. You can also set the interval (1min, 5min, or 10min) as per your requirements by simply changing the numeric value.
You can track multiple stocks by adding the tickers you want to track in the symbols parameter separating the tickers by commas.
Note: Replace YOUR_ACCESS_KEY in the URL with your key.
Historical data endpoint
Along with the symbols parameter, you can also change the date_from (YYYY-MM-DD) and date_to (YYYY-MM-DD) parameter according to your requirements.
Dividends data endpoint
In the Marketstack documentation page, you can explore more endpoints and try them out.
After importing the data, you can also set a refresh schedule so that the data are updated automatically at regular intervals. To learn how to set a refresh schedule, follow this link.
There are several other types of data that you can import into Google Sheets using Amigo Data such as Spotify data, Wikipedia data, Reddit data, and many more.
Some related questions
How to automatically update stock prices in Google Sheets?
Import stock data to Google Shets using Amigo Data and then set a refresh schedule. Amigo will then automatically update the stock prices in Google Sheets at the interval you’ve set–every minute, hour, day or on certain hours or days.
What are some stock data APIs to get real-time stock data?
Besides Marketstack, you can use APIs such as Twelve Data, Yahoo Finance, Polygon.io and TradingView to fetch real-time stock data in Google Sheets.
Why do I get “Invalid JSON” error?
You get this error either because you’re trying to retrieve data that the stock API plan you’ve subscribed to doesn’t allow or the data you’re trying to pull is in another format, eg XML. If the latter, then you need to specify the format as JSON specifically.
Some related articles you may be interested in: