Real-time APAC Market Monitoring with Dashboard

The idea

The primary purpose of the dashboard is to provide a quick glance of APAC market movements to the trading desk in the morning meeting with the use of automation. With a simple click of refreshing the data, the teams are able to view the trading data across regions, index movements and major overnight events.

Apart from that, the dashboard is targetting at users who:

Focus on APAC makets

Looking for a quick view of markets without switching between different web pages or terminals

And it is very friendly to users who have the memberships in Financial Times, since they could simply right click any headline in the dashboard, open it in a new tab and get more details in Financial Times.

Data Processed

To automate the dashboard, a google sheet is linked to the dashboard as the data source.

Data Refresh and Recalculation

Considering the importance of the data timeliness, the date and timing is stated in both the sheet and the top of the dashboard, which presents the timing of the most recent refresh and calculation. Additionally, the time zone in the dashboard is Singapore time zone (GMT+8).

Meanwhile, the google sheet is set to refresh automatically every one minute, while the dashboard is automatically updated every 15 minutes. In addition, users are able to click the 3-dot bottom on the top-right corner to refresh the data.

Data Source

The dashboard contains data in indices, bonds, currencies and commodities, as well as the recent news in global markets. The data sources are stated in the table below.

Data	Data Source
Equity Indice Data	Yahoo Finance
Equity Indice Movements	Google Finance
Bond, Currency and Commodity Data	Reuters
News	Financial Times

Data Display

Due to a delay of data in some exchanges in Google Finance as stated from its disclaimer, the real-time related data, such as the spot price of the equity indice, is derived from other data source, such as Yahoo Finance.

To extract the data from the data sources other than Google itself, the HTRL link is used to input the real-time data in the website. Below is a sample of the formula which is to extract the data in the second row and the second column of the table in the website:

=Index(ImportHTML(HTRL Link,"table"),2,2)

Other than the real-time data, the historic data, i.e. the equity indice price movements in the previous 365 days, is captured through the Google Finance. An example is to obtain the Dow Jones price in one year before:

=INDEX(GOOGLEFINANCE("INDEXDJX:.DJI", "price",TODAY()-365),2,2)

By doing so, we can ensure the trading data is captured in time and minimize the delay time as much as possible.

The design

For users who start using the dashboard, the first step is to refresh the data as mentioned before. Once the data source is updated, the date and the timing will be the latest along with the related trading data and information.

Let’s start with the equity indices first. At the top-left corner of the dashboard, as framed below, except the SP 500 index and Dow Jones, the table mainly offers data related to APAC markets, from Nikkei 225 in Japan to Hang Seng in Hong Kong.

For what is worthy to be mentioned is that those indices are linked to the geographic map below. This means that when a user click one purple spot in the map, such as Hong Kong, the related index in this region will be shown in the table above. In this case, the Hang Seng Index will be reflected in the table with its latest price and percentage of change.

Map linked to the equity indices table above. Additionally, a drop-down list as frame in the screenshot below, provide another option for users to select the regions they would like to view.

Moreover, considering the importance of historic data for users when generating the insights, the movements of equity indices in previous 365 days are also provided in the dashboard with the format of line chart.

Through the chart, simply by moving the cursor, the closing prices of index in that date will be shown to users.

As mentioned above, the graph provides historical data in the previous 365 days. In case that users may be more interested in shorter periods, such as 60 days, a slider is created so that the users are able to slide and change the number of days as they want.

Then let’s move on to the data regarding various asset classes. The asset classes includes rates and bonds, currencies and commodities. In this table next to the equity indices, the details, the latest price, and changes are availble to users.

Similar to the map, another drop-down list is created for users to provide different asset class selections.

Last but not least, on the right hand side of the dashboard, the lastest news as captured from Financial Times Markets is provided for users. Users are able to know about the headlines with simply scrolling down the page so that they may know about the highlights of the overnight events.

For more details, only two actions may be required as mentioned above: a right click on the headline interested and open it in a new tab. Users are able to know about more insights or news detail from Financial Times.

To Improve

Of course, as the dashboard is primarily designed for a quick catch-up before the morning meeting in trading floor, there’re several limitations and points may be improved in the future:

Timeliness of data: The dashboard has minimized the delay time of the data source by various selections of data source. But it’s possible that there’s a delay in the original data source itself.

Data Refresh Automation: This is due to the limited refresh frequency of Data Studio and Google Sheet. The dashboard and sheet have been set to updated as frequent as possible. For Data Studio, the most frequent refresh setting is every 15 minutes, while for Google sheet, it’s every one minute. This means that the dashboard cannot be refreshed itself every second, which requires users to refresh data manually by a simple click. To improve on this issue, the date and timing is provided so at least the users can make sure the timing of the data they are viewing.

Detailed information: More detailed information is not provided in the dashboard, such as analyst predictions or economic indicators. This is because terminals, such as Bloomberg Terminal, have provided detailed and deeper information to users on their platforms. Considering the efficiency of dashboard, more details can be viewed in the related databases and terminals.