【Introduction(6)】- Obtain Financial Data

Demonstrate how to get data from TEJ API financial database

TEJ 台灣經濟新報

Published in

TEJ-API Financial Data Analysis

7 min readAug 10, 2021

Preface

Financial data is the information firms disclose each season, or financial indicators based on that information. Common financial data mainly comes from balance sheets, income statements or cash flow statements. Besides, TEJ API financial database also includes more comprehensive information such as financial ratios, related party transactions, inventory detail and so on. With the standardized data type TEJ offers, we can do financial analysis, stocks selection and empirical research in a more efficient way. Therefore, in this week, we’re about to introduce how to get financial data and demonstrate the basic financial data processing.

The Editing Environment and Modules Required

We use Windows OS and Jupyter Notebook in this article

import tejapi
import pandas as pd
tejapi.ApiConfig.api_key = "Your Key"
tejapi.ApiConfig.ignoretz = True

The Highlights of the Article

The procedure of obtaining data under different conditions
Common data processing methods

Trial Database vs. Paid Database

Note 1: For instance, if you want to get 2000 amounts of data from a trial database, it will automatically stop working as 1000 amounts of data obtained. There, we suggest to add paginate = True in each call to make sure the completeness of the data
Note 2: The upper limitation of data amount for each tejapi.get() call. To handle relevant issue, please refer to the devil in details part of 【Introduction(4)】TEJ Rest API Document. If the enormous data amounts are expected, we suggest to use for loop to continuously combine the outcome of each call
Note 3: Pivot function will make financial accounts more readable, and more convenient for data processing and analysis. We will provide a detailed introduction later on.

Paid Database Used

Financial accounts document: The code of the database is ‘TWN/AIACC’, and it covers IFRS financial accounts classification, code, name and units.
IFRS consolidated cumulative reports of all industries: The code of the database is ‘TWN/AIM1A’, and it contains cumulative financial information of firms in all industries

Note: For example, Q2 (single season) means the data calculated only in Q2 ; while Q2 ( cumulative season) indicates the data calculated from Q1 to Q2.

Case 1: Obtain All Financial Data for a Company

au_no_pivot = tejapi.get('TWN/AIM1A',
                    coid = '2409',
                    paginate = True,
                    chinese_column_name = True)

Take the financial information of AU Optronics Corporation ( 2409 ) for example

There are only four columns. The date column 03/01, 06/01, 09/01 and 12/01 signify Q1, Q2, Q3 and Q4 respectively. The financial accounts are presented in the form of codes. Overall, it’s not intuitive and not easy to process. Therefore, we must use pivot function, exclusive for paid database users, to make financial accounts manifest themselves in independent columns

au_pivot = tejapi.get('TWN/AIM1A',
                    coid = '2409',
                    paginate = True,
                    opts = {'pivot':True},
                    chinese_column_name = True)

With the inclusion of opts = {‘pivot’:True}, we can clearly see each account name and corresponding value

Case 2: Obtain Specified Financial Data for Multiple Companies

panel = ['2409', '3481', '6116']

panel list contains the codes of several firms. Here we select AU Optronics Corporation ( 2409 ), Innolux Corporation (3481) and Hannstar Display Corp. (6116)

finance = pd.DataFrame()

For starters, we create an empty table for storing the data from each loop

for firm in panel:
    finance = finance.append(tejapi.get('TWN/AIM1A',
                    coid = firm,
                    paginate = True,
                    opts = {'pivot':True},
                    chinese_column_name = True))

With the usage of for loop and append() to pile up the data in finance table, we get all financial information for these three firms

However, we find out it takes a bit longer to obtain this data. Thus, we must only select the financial accounts that we need in advance to speed up the obtaining time. To achieve that, the first thing we do is to look up the code of financial accounts

accounting = tejapi.get('TWN/AIACC',
                       id = 'AIM1A', 
                       paginate = True,
                       chinese_column_name = True)

Here we adopt financial accounts document database. It’s worth noting that we add id = ‘AIMIA’ here to indicate we only need accounts from IFRS consolidated cumulative reports of all industries (TWN/AIM1A)

Then all information of the accounts are presented, such as the codes, names and the financial reports they come from. We can use the data filtering method to find out the specific code of the accounts. For instance, if we are looking for depreciation related accounts, we can use the Chinese column and str.contains() to achieve it

depreciation = accounting[accounting['中文全稱'].str.contains('折舊')]

If we cannot find anticipated accounts, we can turn to searching in the English column by its English name. If there are still tons of data after the first filtering, we can do the second or third filtering in the same way. For example, if we want that depreciation in the cash flow statement, we can utilize Chinese report column to filter.

depreciation[depreciation['中文表次'].str.contains('現金流量')]

Now we can confirm the code of depreciation expense is 7211. If we want all the financial accounts from balance sheet, we directly seek for them in Chinese report column with the key words of balance sheet

balance_sheet = accounting[accounting['中文表次'].str.contains('資產負債')]

Then all accounts from balance sheet will be obtained

BS_list = balance_sheet['會計科目'].tolist()

In terms of the code column, we store all the values of it in a BS_list list

finance = pd.DataFrame()
for firm in panel:
    finance = finance.append(tejapi.get('TWN/AIM1A',
                    coid = firm,
                    paginate = True,
                    opts = {'pivot':True, 'columns': ['coid','mdate'] + BS_list},
                    chinese_column_name = True))

Next, we attach BS_list list to the columns selection in opts parameters, so those financial accounts will appear after the code of the firm and the date.

Basic Financial Data Processing

Backup

fin = finance.copy()

The purpose of this step is to avoid obtaining data repetitively because of unintentional modification of the raw data during data processing. It’s especially important when dealing with tremendous amounts of data, not only because it wastes time to re-obtain, but also because we might exceed the daily data amount limitation. After the backup, we proceed with fin variable, and if we need to reset it to the original content of raw data, we can just re-run the code from this line

Transform seasonal data to yearly data ( the following method is only limited to cumulative financial reports)

fin['月'] = fin['財報年月'].dt.month

First of all, we employ dt.month to extract the month figure from date column, and form an additional column

fin = fin[fin['月'] == 12]

Since those are cumulative financial data, we just need to select data in Q4 to get our desired yearly data

Sort

fin = fin.sort_values(by = ['財報年月'], ascending = True)

We use sort_values() and ascending = True to sort the date in a ascending way

If we want to further sort the assets amounts in a descending way within the same period

fin = fin.sort_values(by = ['財報年月','資產總額'], ascending = [True, False])

We can see after sorting those data, the index is disordered, so we normally reset the index after sorting

fin = fin.reset_index(drop=True)

Conclusion

Financial data is helpful for evaluating the financial structure and operating performance of a company, and further impacting the investors’ investment decisions. Therefore, the more comprehensive and intuitive the financial data is, the more convenient for us to do quantitative analysis. Today we demonstrate how to use pivot function of TEJ API paid database, obtain certain financial accounts and carry out simple data processing. If readers are interested in our database, we recommend you to go E-shop to find out the optimal plan that suits your needs, so that you can discover more valuable information in the huge pool of financial data