Do you manually import data from Bloomberg every day to track stocks & financial metrics? Want to automate the entire process by setting it up one time and getting updated data everyday? Read on.
We’ll use Amigo Data, a data automation tool to fetch and update data directly with Bloomberg stock API without needing any code.
Shown under is a snippet of the market movers data imported from Bloomberg into Google Sheets.
Steps to import Bloomberg data to Google Sheets
- Install the Amigo Data add-on
- Get the Bloomberg API key
- Choose the data endpoint
- Import data to Google Sheets
How to import Bloomberg data to Google Sheets
Here’s the step-by-step guide to import Bloomberg data to Google Sheets using Bloomberg stock API.
Step 1: Install the Amigo Data add-on in Google Sheets
Install the Amigo add-on from Google Workspace Marketplace to import Bloomberg data to Google Sheets and automate stock data imports to Google Sheets.
Alternatively, you can open a Google Sheets document, then go to Extensions→Add-ons→Get add-ons. Search for Amigo Data and install it.
Once the installation is complete, we can move on to the next step: getting the Bloomberg Stock API key.
Step 2: Generate a key
In order to access the Bloomberg database, we’ll need a key called the API key. It is a code used to identify and authenticate access.
Since Bloomberg doesn’t have an official API, we will use a third-party Bloomberg API on Rapid API – an API marketplace.
Sign up to RapidAPI.
Once you’re logged in, go to the Bloomberg API documentation page in RapidAPI. With this API, we can retrieve stock data from Bloomberg with the stock API, news with the Bloomberg news API, and market data using the market API.
Choose a subscription plan from the Pricing menu to retrieve data from the Bloomberg API. We’ll choose the Basic option (you can always upgrade later should you need).
Upon subscribing to a plan, an API key will be generated and we’ll be able to use the Bloomberg API to retrieve data from Bloomberg into Google Sheets.
Go back to the Bloomberg API documentation by clicking Endpoints. Scroll down a bit and you will find your API key.
Note the key and keep it confidential. We’ll use it to pull data from Bloomberg to Google Sheets. But first, let us get the endpoint of the data to export.
Step 3: Choose an endpoint
On the left of the Bloomberg API documentation page is a list of all data types that can be imported into Google Sheets with the Bloomberg API.
Select any of the endpoints. To import stock data, select the Bloomberg stock API. For this tutorial, we will import the data about market movers in Dow Jones Industrial Average. So, expand the market menu option and select market/get-movers.
The header parameter consists of the API key and API host. In the required parameters we have to input the name of the index according to the symbols used by Bloomberg.
In the id block under the required parameters section, nky:ind is a symbol for Nikkei Index. Our objective is to import market movers for Dow Jones Industrial Average. Therefore, we will enter indu:ind as the id string in the required parameters where indu is the symbol used by Bloomberg for Dow Jones; and ind is used to indicate that the symbol is for index.
After filling in the parameter, click the Test Endpoint button to run the query.
If the parameters are valid, you should see a success message along with the data under Results. The Bloomberg API endpoint URL and the API headers are found under Code Snippets.
Change the (Node.js) Axios to (Shell) cURL from the dropdown option.
Copy the Bloomberg API endpoint URL excluding the apostrophes and also note the headers. This is the endpoint URL for the data to import to Google Sheets with Bloomberg API.
Step 4: Import Bloomberg data to Google Sheets
Go back to Google Sheets and launch the Amigo Data extension to import the market data from Bloomberg to Google Sheets.
Select Custom API from the list of sources.
Paste Bloomberg API endpoint URL the API URI bar.
Then expand the Details tab and enter headers mentioned in the Code Snippets as headers:
Name: X-RapidAPI-Host, Value: bb-finance.p.rapidapi.com
Name: X-RapidAPI-Key, Value: your_key
Replace your_key with your RapidAPI key.
Click the PREVIEW button to retrieve and preview the Bloomberg data to import to Google Sheets.
Then flatten the nested datasets and then click IMPORT.
In a short while, the Bloomberg market data will be imported into Google Sheets.
A snippet of the imported data.
You can likewise import other types of data from Bloomberg using other endpoints from the Bloomberg stock API, and Bloomberg news API. Following are some other examples.
Financials of stock
Under the Bloomberg stock API section, select stock/get-financials to get the market fundamentals data for a particular stock.
In the required parameter section, type in the Bloomberg symbol of the stock and country code separated by a colon. You can go through the Nasdaq stock screener to find the symbol of the desired stocks.
Here are two examples (the parameter is case-insensitive):
aapl:us (aapl is for Apple and us for the United States)
tcs:in (tcs is for TCS stock and in for India)
Copy the URL link from the code snippet by changing the code language to Shell -> cURL and also note down the headers to add them in the details section of the Amigo Data. The steps are quite similar to the ones we discussed above.
Statistics of stock
Under the stock section, select stock/get-financials. Type in the Bloomberg symbol of the stock and country code separated by a colon inside the textbox against the id parameter.
In the Bloomberg news API section, select news/list. In the required section, we can specify the type of news you are interested in. We can choose from a wide range of types of news mentioned in the documentation.
You can go through the Bloomberg API documentation page, explore more endpoints and try using them to retrieve data from Bloomberg and import them into Google Sheets.
After importing the data, 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 Marketstack data, Coinmarketcap data, Nasdaq data, and many more.
Some related articles you may be interested in: