Demonstrate how to get data from TEJ API financial database
Table of Contents
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.
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
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 = Truein each call to make sure the completeness of the dataNote 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 useforloop to continuously combine the outcome of each callNote 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.
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.
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
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.
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
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
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)
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
