“What are some good APIs for Philippine stock exchange data?”
– Stock Investor
“Is there any API to get historical stock prices on the PSE?”
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 option is for anyone who wants to simplify the process of importing and exporting data. Save time when creating reports.
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-api3.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-api3.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 Information | API URL |
|---|---|
| All stock market data | http://phisix-api3.appspot.com/stocks.json |
| BPI (Bank of the Philippine Islands) stock market data | http://phisix-api3.appspot.com/stocks/BPI.json |
| BPI stock market data on a specific date | http://phisix-api3.appspot.com/stocks/BPI.2021-03-31.json |
| BDO (Banco De Oro) stock market data | http://phisix-api3.appspot.com/stocks/BDO.json |
| BDO stock market data on a specific date | http://phisix-api3.appspot.com/stocks/BDO.2021-03-31.json |
| AC (Ayala Corporation) stock market data | http://phisix-api3.appspot.com/stocks/AC.json |
| AC stock market data on a specific date | http://phisix-api3.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 Information | API URL |
|---|---|
| All stock market data | https://pselookup.vrymel.com/api/stocks |
| BPI (Bank of the Philippine Islands) stock market data | https://pselookup.vrymel.com/api/stocks/BPI |
| BPI stock market data on a specific date | https://pselookup.vrymel.com/api/stocks/BPI/history/2021-03-31 |
| BDO (Banco De Oro) stock market data | https://pselookup.vrymel.com/api/stocks/BDO |
| BDO stock market data on a specific date | https://pselookup.vrymel.com/api/stocks/BDO/history/2021-03-31 |
| AC (Ayala Corporation) stock market data | https://pselookup.vrymel.com/api/stocks/AC |
| AC stock market data on a specific date | https://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 option or Paid option. We’ll go through each.
Option 1: Google Apps Script (Free)
First option is to use the ImportJSON function in Google Sheets. This is through Google Apps Script 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.
3 – At the top, click ‘Tools’. Then click ‘Script editor’.
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.
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.
7 – Go back to the script editor and paste everything. Paste all the line code.
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.
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.
Congratulations! This is a glimpse of what importing APIs into Google Sheets can do. Try other tickers like SMC, BPI, COL, and many others.
However, there are downsides to this option:
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.
Option 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’.
2 – Search for Two Minute Reports. Then install it.
3 – You now have the add-on installed on your Google Sheets file.
Import Stock Market Data Source
1 – Launch the add-on. A navigation pane on the right side appears.
2 – Click ‘Add +’ under Data Sources.
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.
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’.
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 +’.
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.
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)
6 – Click ‘Run query’
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 Information | API URL |
|---|---|
| All stock market data | http://phisix-api4.appspot.com/stocks.json |
| BPI (Bank of the Philippine Islands) stock market data | http://phisix-api4.appspot.com/stocks/BPI.json |
| BPI stock market data on a specific date | http://phisix-api4.appspot.com/stocks/BPI.2021-03-31.json |
| BDO (Banco De Oro) stock market data | http://phisix-api4.appspot.com/stocks/BDO.json |
| BDO stock market data on a specific date | http://phisix-api4.appspot.com/stocks/BDO.2021-03-31.json |
| AC (Ayala Corporation) stock market data | http://phisix-api4.appspot.com/stocks/AC.json |
| AC stock market data on a specific date | http://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 Information | API URL |
|---|---|
| All stock market data | https://pselookup.vrymel.com/api/stocks |
| BPI (Bank of the Philippine Islands) stock market data | https://pselookup.vrymel.com/api/stocks/BPI |
| BPI stock market data on a specific date | https://pselookup.vrymel.com/api/stocks/BPI/history/2021-03-31 |
| BDO (Banco De Oro) stock market data | https://pselookup.vrymel.com/api/stocks/BDO |
| BDO stock market data on a specific date | https://pselookup.vrymel.com/api/stocks/BDO/history/2021-03-31 |
| AC (Ayala Corporation) stock market data | https://pselookup.vrymel.com/api/stocks/AC |
| AC stock market data on a specific date | https://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 +’.
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
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.
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 options above automate accessing data without manual import and export. It avoids spreadsheet headaches.
Option 3: Google Finance Philippines
The Google Finance API is another way to import stock market data. It’s free to use and easy to get started. Since Finance is part of the Google ecosystem, importing to Google Sheets is easy.
Import Google Finance Data To Google Sheets
1 – Go to any empty cell in your spreadsheet.
2 – The formula contains five parameters:
=GOOGLEFINANCE(<TICKER>, <ATTRIBUTE>, <START DATE M/M/YYYY>, <END DATE M/M/YYYY>, <INTERVAL>)TICKER: The ticker symbol. This is required.
ATTRIBUTE: The information from the ticker. This is optional but price is the default.
START DATE: Start date of the stock data. Format is M/M/YYYY.
END DATE: End date of the stock data. Format is M/M/YYYY.
INTERVAL: The data frequency. Either Daily or Weekly.
For example, get the daily stock price of Banco De Oro from January 1, 2021 to August 31, 2021. The parameters should be inside double quotes "" or else it won’t work.
=GOOGLEFINANCE("OTCMKTS:BDOUY", "price", "1/1/2021", "8/31/2021", "Daily")The way Google Finance works for Philippine stocks is that you need to include the market symbol as well. In this case, it’s OTCMKTS. So, the input is OTCMKTS, then a colon, then the stock ticker.
3 – Add the formula to the cell in Google Sheets.
Viola! You imported Google Finance data on the spreadsheet. Note that the results are in US dollars so you need to add an extra formula for conversion.
Other Stock Market Data
Google Finance is a good source for stocks in the Philippines. But I noticed that it lacks other stock companies as well. As for ticker information, the list below is the input for other stock prices that I found. Remember to always add OTCMKTS and put them inside double quotes "".
| Input | Description |
|---|---|
OTCMKTS:ABSOF | ABS-CBN Holdings Corporation |
OTCMKTS:ABTZY | Aboitiz Equity Ventures Inc |
OTCMKTS:AYALY | Ayala ADR |
OTCMKTS:BDOUY | BDO Unibank ADR |
OTCMKTS:BPHLY | Bank of the Philippine Islands BPI Unsponsored Philippines ADR |
OTCMKTS:FSGCY | First Gen ADR |
OTCMKTS:GTMEY | Globe Telecom ADR |
OTCMKTS:JBFCY | Jollibee Foods ADR |
OTCMKTS:MPCFF | Metro Pacific Investments Corp. |
OTCMKTS:MTPOF | Metropolitan Bank & Trust Company |
OTCMKTS:MWTCF | Manila Water Company Inc |
OTCMKTS:MWTCY | Manila Water Company ADR |
OTCMKTS:PSKXF | Philippine Stock Exchange Inc |
OTCMKTS:PXMFF | Philex Mining Corporation |
OTCMKTS:RRETY | Robinsons Retail Holdings ADR |
OTCMKTS:SMGBY | San Miguel Corp |
OTCMKTS:SMPFF | San Miguel Food and Beverage Inc Fully Paid Ord. Shrs |
OTCMKTS:SPHXF | SM Prime Holdings, Inc. |
OTCMKTS:SVTMF | SM Investments Corporation |
OTCMKTS:SYBJF | Security Bank Corporation |
OTCMKTS:UVRBY | Universal Robina Unsponsored Philippines ADR |
The list below is for the <ATTRIBUTE> parameter. Remember to put them inside double quotes "" for them to work.
| Input | Description |
|---|---|
price | Price of the ticker |
high | High price of the date |
low | Low price of the date |
open | Opening price of the date |
close | Closing price of the date |
volume | Stock volume of the date |
shares | Number of outstanding shares |
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.
