Philippines Stock Exchange API in Google Sheets

“What are some good APIs for Philippine stock exchange data?”
“Is there any API to get historical stock prices on the PSE?”

– Stock Investor

Creating Philippines stock market reports is a lot of manual work. Typing stock prices on a spreadsheet one by one or non-stop exporting a file. This is a tedious and time-consuming task. It’s now possible to avoid manual reporting and automation.

The new way to get Philippines stock market data is through Google Sheets. Automate reports using a Philippine stock exchange API. This method is for anyone who wants to simplify the process of importing and exporting data. Save time when creating reports.

pse 14 2
PSE Daily Quotation Report

In this article, learn how to use an API in Google Sheets for your stock market data needs.

Requirement: Philippine Stock Exchange API

Two Philippine stock exchange APIs came up. These APIs are updated and still functioning. You don’t need to sign up for anything to use the services. Accessing the Philippines stock market data is within reach. Note that you need to have a little bit of JavaScript knowledge to understand this guide.

Option 1: Phisix API

One of the Philippine stock exchange API I came across is from phisix-api4.appspot.com. This API is straightforward. The API data is public that you can access through a web browser.

Go to this URL http://phisix-api4.appspot.com/stocks/BDO.json. This is the BDO ticker (Banco De Oro) stock price information.

{"stock":[{"name":"Banco de Oro","price":{"currency":"PHP","amount":109.70},"percent_change":0.64,"volume":1677210,"symbol":"BDO"}],"as_of":"2021-03-04T12:50:00+08:00"}

What you see here is the JSON format. The API also accommodates XML format. But JSON format is a more supported format when getting requests from APIs. The JSON format is what we’ll use in this blog post and knowing a little bit of JavaScript helps from here.

Examples of API URLs under this stock exchange API are:

Stock InformationAPI URL
All stock market datahttp://phisix-api4.appspot.com/stocks.json
BPI (Bank of the Philippine Islands) stock market datahttp://phisix-api4.appspot.com/stocks/BPI.json
BPI stock market data on a specific datehttp://phisix-api4.appspot.com/stocks/BPI.2021-03-31.json
BDO (Banco De Oro) stock market datahttp://phisix-api4.appspot.com/stocks/BDO.json
BDO stock market data on a specific datehttp://phisix-api4.appspot.com/stocks/BDO.2021-03-31.json
AC (Ayala Corporation) stock market datahttp://phisix-api4.appspot.com/stocks/AC.json
AC stock market data on a specific datehttp://phisix-api4.appspot.com/stocks/AC.2021-03-31.json

The API includes the following stock market data:
– Company Name
– Ticker Symbol
– Currency
– Stock Price Amount
– Stock Percent Change
– Volume

Option 2: PSE Lookup API

Another Philippine stock exchange API I came across is from pselookup.vrymel.com. This API is also straightforward. The API data is also public that you can access through a web browser.

Go to this URL https://pselookup.vrymel.com/api/stocks/BDO. This is the BDO ticker (Banco De Oro) stock price information. The data is also in JSON format. As mentioned, JSON is the format that’ll be used on this blog post.

{"ticker_symbol":"BDO","company_name":"BDO Unibank, Inc.","price":{"trading_date":"2021-03-03","low":107.2,"open":109.4,"close":109.0,"high":109.4,"volume":3139130.0,"timestamp":"2021-03-03T16:05:44+08:00"}}

Examples of API URLs under this stock exchange API are:

Stock InformationAPI URL
All stock market datahttps://pselookup.vrymel.com/api/stocks
BPI (Bank of the Philippine Islands) stock market datahttps://pselookup.vrymel.com/api/stocks/BPI
BPI stock market data on a specific datehttps://pselookup.vrymel.com/api/stocks/BPI/history/2021-03-31
BDO (Banco De Oro) stock market datahttps://pselookup.vrymel.com/api/stocks/BDO
BDO stock market data on a specific datehttps://pselookup.vrymel.com/api/stocks/BDO/history/2021-03-31
AC (Ayala Corporation) stock market datahttps://pselookup.vrymel.com/api/stocks/AC
AC stock market data on a specific datehttps://pselookup.vrymel.com/api/stocks/AC/history/2021-03-31

The API includes the following stock market data:
– Company Name
– Ticker Symbol
– Status
– Currency
– Stock Price Amount
– Stock Percent Change
– High Price
– Low Price
– Open
– Close
– Volume

How To Import Philippine Stock Prices To Google Sheets

Now it’s time to figure out a way on how to import the stock market data from the respective APIs to a Google Sheets file. There are two ways to achieve this: either through a Free method or Paid method. We’ll go through each.

Method 1: ImportJSON Function (Free)

First option is to use the ImportJSON function in Google Sheets. And this is completely free.

Setting Up ImportJSON Function

1 – Go to your Google Drive workspace.

2 – Click ‘+ New’ then create a new Google Sheets file.

tmr 0
tmr 00

3 – At the top, click ‘Tools’. Then click ‘Script editor’.

pse 13

3 – Once inside, name your project with whatever you want. Rename ‘Code.gs’ to ‘ImportJSON.gs’.

4 – Then delete everything that’s inside the text editor. Delete the function myFunction(){ } syntax.

pse 2

5 – To create the ImportJSON function, we need to go to Brad Jasper’s GitHub page.

6 – Once inside, copy the entire text code. Highlight from line code 1 until line code 634. Then copy it.

pse 4

7 – Go back to the apps script editor and paste everything. Paste all the line code.

pse 3

8 – Click the Save icon.

Setup done. At this point, the JavaScript code initializes in the Google Sheets file you’re working on.

Using ImportJSON Function

1 – Go back to your Google Sheet file.

2 – On a cell, type in =ImportJSON(). The function appears. This means the script is working.

pse 11

Inside the parenthesis is where you add the API URL of the API. In this example, we’ll use Phisix and we’ll use BDO (Banco De Oro) stock ticker.

3 – Type in =ImportJSON("http://phisix-api4.appspot.com/stocks/BDO.json"). Your Google Sheet populates the cells with the data.

pse 12

Congratulations! This is a glimpse of what import APIs into Google Sheets can do. Try other tickers like SMC, BPI, COL, and many others.

pse 15

However, there are downsides to this method:
Reports are not automated enough that you can leave the API call and get the data automatically. Manual labor is still required when there are many stock prices you want to track and monitor. The ImportJSON function may not be robust enough to help.

Also, there are no auto-schedule reports. It won’t be enough if you want to track prices on an hourly basis and keep records of them. Stock market trends with overtime data need a more robust scheduling system.

Method 2: Google Sheets with Add-On (Paid)

To import stock exchange data to Google Sheets automatically, you need a Google Sheets add-on. These add-ons are most likely paid add-ons. As mentioned, you can do it manually (for free) to save money. But in perspective, you might want to spend a little bit to save time. You choose.

You’ll use Two Minute Reports for this example. TMR imports data from the API directly to Google Sheets in minutes.

Installing The Google Sheets Add-on

1 – At the top of your Google Sheet, click ‘Add-ons’. Then click ‘Get add-ons’.

tmr 2

2 – Search for Two Minute Reports. Then install it.

tmr 1

3 – You now have the add-on installed on your Google Sheets file.

tmr 3

Import Stock Market Data Source

1 – Launch the add-on. A navigation pane on the right side appears.

tmr 15

2 – Click ‘Add +’ under Data Sources.

tmr 4

3 – Name your data source. Then select ‘API Bridge’ under Type.

4 – For ‘Base URL’, add the stock exchange API of your choice. In this example, we’ll use http://phisix-api4.appspot.com.

5 – For ‘Authorization’, select ‘No Auth’. This stock exchange API doesn’t need any authentication.

6 – For ‘Headers’, keep them empty.

pse 5

You may choose ‘Test before saving’ to verify that the API base URL is valid. Select ‘GET’ for Method.

Add /stocks/BDO.json under Test URL Suffix. If not valid, the tool gives instructions on how to fix it.

If valid, the tool prompts ‘Successfully authenticated’.

pse 7

7 – Click ‘Yes’. And you’re done at this part.

Import Stock Market Data Query

This point in the process is the importing stock price data to the spreadsheet.

1 – Click the burger icon. Then Click ‘Data Queries’. And click ‘Add +’.

tmr 11

2 – Name your data query. Then find the API Bridge data source you imported earlier.

3 – ‘Save on Sheet’ is the location of the sheet where the data is imported to. The location might be ‘Sheet 1’ but it depends if you named your sheet. Then Add A1 under ‘Cell’.

4 – Deselect ‘Append new data’. This means the data overwrites the current spreadsheet.

pse 8

5 – Then follow the screenshot for the next set of inputs.

Method: GET
URL Path/Suffix: /stocks/BDO.json -> We’ll get BDO (Banco De Oro) stock information in this example.
JMES Path: Leave it empty
Pagination: None
Output Format: Grid (default)

pse 9

6 – Click ‘Run query’

pse 10

Viola! You imported a stock price on a Google Sheet. These are the first steps in importing Philippines stock market prices. At this point, you may take the steps on accessing your required data.

Other Stock Market Data

At this point, the stock market data you want to access depends on the API endpoints or API paths. All you need to do is change the URL Path/Suffix. The green color is the Base URL and the red color is URL Path/Suffix.

Stock InformationAPI URL
All stock market datahttp://phisix-api4.appspot.com/stocks.json
BPI (Bank of the Philippine Islands) stock market datahttp://phisix-api4.appspot.com/stocks/BPI.json
BPI stock market data on a specific datehttp://phisix-api4.appspot.com/stocks/BPI.2021-03-31.json
BDO (Banco De Oro) stock market datahttp://phisix-api4.appspot.com/stocks/BDO.json
BDO stock market data on a specific datehttp://phisix-api4.appspot.com/stocks/BDO.2021-03-31.json
AC (Ayala Corporation) stock market datahttp://phisix-api4.appspot.com/stocks/AC.json
AC stock market data on a specific datehttp://phisix-api4.appspot.com/stocks/AC.2021-03-31.json

You can use another Philippine stock exchange API Though it comes with a different kind of API endpoints. Always read the API documentation to understand which data points to use. This dictates the data queries that you add to the Add-on tool.

These are the API endpoints from PSE Lookup. Both API Base URL and URL Path/Suffix are now different.

Stock InformationAPI URL
All stock market datahttps://pselookup.vrymel.com/api/stocks
BPI (Bank of the Philippine Islands) stock market datahttps://pselookup.vrymel.com/api/stocks/BPI
BPI stock market data on a specific datehttps://pselookup.vrymel.com/api/stocks/BPI/history/2021-03-31
BDO (Banco De Oro) stock market datahttps://pselookup.vrymel.com/api/stocks/BDO
BDO stock market data on a specific datehttps://pselookup.vrymel.com/api/stocks/BDO/history/2021-03-31
AC (Ayala Corporation) stock market datahttps://pselookup.vrymel.com/api/stocks/AC
AC stock market data on a specific datehttps://pselookup.vrymel.com/api/stocks/AC/history/2021-03-31

Schedule Your Stock Market Reports

Two Minute Reports has a scheduling function. This means the spreadsheet refreshes the acquired data from the API. Reports are automated even more.

1 – Click the burger icon. Then click Schedule Refresh. And click ‘New +’.

tmr fb5

2 – Once inside, you can set the terms of the report schedule.

-Name
-Repeats -> This is where you set how often the data refresh happens. Two Minute Reports can do hourly refreshes.
-Timezone

tmr li3

3 – You can also set the notification terms. Two Minute Reports can email you a PDF or Excel of the data. You can also decide to have the email contain the sheet content in the email body.

tmr fb7
tmr fb8

4 – Click ‘Save’.

Given the nature of stock exchanges, it’s better to schedule these reports on an hourly basis.

Congratulations! You now know how to access the Philippine stock market through APIs. It’s up to you to create your PSE daily quotation report. The methods above automate accessing data without manual import and export. It avoids spreadsheet headaches.

Conclusion – Build Reports on the Philippine Stock Market

Know how to use an API in Google Sheets for your stock market data needs. Google Sheets is a web-based spreadsheet app that provides an easy way to analyze data. With the help of Google Sheets add-ons, you can use external APIs and automate reporting.

You’re wasting time manually importing and exporting data. If you’re an investor or marketer, automated reporting through APIs makes lives easier.


About The Author

Francis Angelo Reyes

Francis is the owner and runs Lupage Digital. With 5 years of experience, Francis writes everything he knows about digital marketing. There’s a large knowledge gap between developers and digital marketers. Francis is the middleman between where the non-technical and technical meet.