Building your own database through TEJ API(II)

– update your database automatically

Continuing with the TEJ API data collecting/reading/saving instructions which we have learned in the previous episode, this episode focuses on the way to update your database automatically. It is consistent with the previous episode, so friends who have not read the previous episode, please go back and read the introduction (1) ~

link:Building your own database through TEJ API

Highlights of this article

  • TEJ API Data Collecting
  • How to Update the Database
  • Windows Task Scheduler

Links related to this article

TEJ API Data Collecting-Using stock data as an example

First we have to install the packages on Python. (tejapi, datetime, sqlite3)

pip install tejapi
pip install datetime
pip install sqlite3

The next step is to import these packages and set up your own api_key. If you don’t have one, you can apply for a trial on the TEJ API official website. But the trial version has limits on the range of data that could be obtained

import tejapi
import datetime
import sqlite3

The database which will be used in this chapter is contained in 小資方案. If you want to use data without limit, check it on TEJ E-shop

import tejapi
tejapi.ApiConfig.api_key = “your api_key”

Now we can get the data through these codes👀

TSMC = tejapi.get(
'TWN/EWPRCD', # database
coid = '2330', # Stock/Company symbol
mdate={'gte':'2020-01-01', 'lte':'2020-12-31'}, # date
paginate=True, # data can be obtained in stages through this parameter
opts={'columns': ['mdate','open_d','high_d','low_d','close_d']},
# columns select

Saving data to Database:

  1. Grab data through tejapi
  2. Connect to the database (example.db)
  3. Save data into the database (example.db)
  4. Close connection
# 1
TSMC = tejapi.get(
coid = '2330',
opts={'columns': ['mdate','open_d','high_d','low_d','close_d']},
# 2
conn = sqlite3.connect('example.db')
# 3
TSMC.to_sql(name='TSMC', con=conn, if_exists="replace", index=False)
# 4

Remember to check the database after running the code

How to Update the Database

Next, we can add for-loop to the steps above, change stock/company symbols from individual to multi-stocks, and each execution will save data to the database.

  1. Collect data from TWN/EWNPRCSTD.
  2. Select stocks/company symbols of all listed companies.
  3. Using the append method to store data into the database.
  4. Execute for-loop.

Windows Task Scheduler

  1. Search task scheduler on the bottom-left in your windows system.
  2. After entering into the task scheduler, click create a task on your right, then name the task(ef. update database).
  3. Switch to task trigger, create a new one, turn the trigger to daily in first in the “trigger “ field, and then set the start time at 3:30 pm. (Be careful! Today’s data will be updated after 3 pm, so we should set the start time after 3 pm.).
  4. Switch to the “Action” field and create a new one, then choose to start a program👷:
  • First blank ➡️ python location(path)
  • Second blank ➡️ file’s name (.py)
  • Third blank ➡️ file’s location(path)


What we share with you this time is the introduction to get the data through TEJ API and combines with windows task scheduler to complete the goal of automatic database updating. Surely, we will write more articles related to data analysis and applications through Medium in the future!
Finally, if you like this topic, please click 👏 below, giving us more support and encouragement. Additionally, if you have any questions or suggestions, please leave a message or email us, we will try our best to reply to you.

Links related to this article again!