{"id":14309,"date":"2021-03-08T02:03:37","date_gmt":"2021-03-07T18:03:37","guid":{"rendered":"https:\/\/www.tejwin.com\/?post_type=insight&#038;p=14309"},"modified":"2023-08-10T14:35:06","modified_gmt":"2023-08-10T06:35:06","slug":"building-your-own-database-through-tej-api","status":"publish","type":"insight","link":"https:\/\/www.tejwin.com\/en\/insight\/building-your-own-database-through-tej-api\/","title":{"rendered":"Building your own database through TEJ\u00a0API"},"content":{"rendered":"\n<figure class=\"wp-block-image aligncenter\"><img decoding=\"async\" src=\"https:\/\/www.tejwin.com\/wp-content\/uploads\/1bcpl7ieca2giOUYiIhAW4Q.jpg\" alt=\"\"\/><\/figure>\n\n\n\n<p>&#8211; how to grab data from TEJ&nbsp;API?<\/p>\n\n\n\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_81 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">Table of Contents<\/p>\n<label for=\"ez-toc-cssicon-toggle-item-69f1d4faa5274\" class=\"ez-toc-cssicon-toggle-label\"><span class=\"ez-toc-cssicon\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewBox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewBox=\"0 0 24 24\" version=\"1.2\" baseProfile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/label><input type=\"checkbox\"  id=\"ez-toc-cssicon-toggle-item-69f1d4faa5274\"  aria-label=\"Toggle\" \/><nav><ul class='ez-toc-list ez-toc-list-level-1 ' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/www.tejwin.com\/en\/insight\/building-your-own-database-through-tej-api\/#What_is_TEJ_API\" >What is TEJ&nbsp;API?<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/www.tejwin.com\/en\/insight\/building-your-own-database-through-tej-api\/#Highlights_of_this_article\" >Highlights of this&nbsp;article<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/www.tejwin.com\/en\/insight\/building-your-own-database-through-tej-api\/#Links_related_to_this_article\" >Links related to this&nbsp;article<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/www.tejwin.com\/en\/insight\/building-your-own-database-through-tej-api\/#SavingReading_the_data\" >Saving\/Reading the&nbsp;data<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/www.tejwin.com\/en\/insight\/building-your-own-database-through-tej-api\/#Conclusion\" >Conclusion<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/www.tejwin.com\/en\/insight\/building-your-own-database-through-tej-api\/#Links_related_to_this_article_again\" >Links related to this article&nbsp;again<\/a><\/li><\/ul><\/nav><\/div>\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"What_is_TEJ_API\"><\/span>What is TEJ&nbsp;API?<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>Would you find it very difficult to find a high-quality and stable source of data before doing an analysis on it? Or is it time-consuming to maintain your own web-crawling program after spending a lot of time creating it?<strong> Now, TEJ (Taiwan Economic Journal) has launched the TEJ API tool for you to solve this problem!<\/strong> You are very welcome to apply for a trial.<\/p>\n\n\n\n<p><strong>TEJ API will be mainly used as the source of data<\/strong>, and we would like to share some financial data analysis and applications in Python with you in our Medium. For rookies, you could get a preliminary understanding of how to analyze data through programming, and for experts, you can access a stable and fast source of data!<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Highlights_of_this_article\"><\/span>Highlights of this&nbsp;article<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li>TEJ API Data Collecting<\/li>\n\n\n\n<li>Saving\/Reading the data into\/from CSV, Xlsx, and SQLite.<\/li>\n<\/ul>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Links_related_to_this_article\"><\/span>Links related to this&nbsp;article<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>1\ufe0f\u20e3 API Official Website: <\/strong><a href=\"https:\/\/api.tej.com.tw\/\" rel=\"noreferrer noopener\" target=\"_blank\"><strong>TEJ API \u5b98\u7db2<\/strong><\/a><\/li>\n\n\n\n<li><strong>2\ufe0f\u20e3 The Product Package: <\/strong><a href=\"https:\/\/eshop.tej.com.tw\/E-Shop\/Edata_caseIntro\/1\" rel=\"noreferrer noopener\" target=\"_blank\"><strong>TEJ E SHOP<\/strong><\/a><\/li>\n\n\n\n<li><strong>3\ufe0f\u20e3 Source Code: <\/strong><a href=\"https:\/\/github.com\/tejtw\/TEJAPI_Python_Medium_Rookies\" rel=\"noreferrer noopener\" target=\"_blank\"><strong>TEJ GITHUB<\/strong><\/a><\/li>\n<\/ul>\n\n\n\n<p>* TEJ API Data Collecting\uff0dUsing stock data as an example<\/p>\n\n\n\n<p>It is very simple to grab the data from TEJ API.\ud83d\udcaa<br>First we have to install tejapi package in Python.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">pip install tejapi<\/pre>\n\n\n\n<p>The next step is to import this package and set up <strong>your own api_key<\/strong>. If you don\u2019t have one, you can apply for a trial on the TEJ API official website. But the trial version <strong>has limits on the range of data that could be obtained!<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">import tejapi<br>tejapi.ApiConfig.api_key = \u201c\u4f60\u7684api_key\u201d<\/pre>\n\n\n\n<p>Now we can get the data through this code\ud83d\udc40<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">TSMC = tejapi.get(\n'TWN\/EWPRCD', \ncoid = '2330',\nmdate={'gte':'2020-01-01', 'lte':'2020-12-31'}, \nopts={'columns': ['mdate','open_d','high_d','low_d','close_d']}, \npaginate=True\n)<\/pre>\n\n\n\n<pre class=\"wp-block-preformatted\">TSMC<\/pre>\n\n\n\n<p>Parameters\uff1a<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Name of the database\uff0dTWN\/EWPRCD<br>\u2757\u2757if you are a trial account user, then the name of the database may diverse for the trial version.<\/li>\n\n\n\n<li>Stock\/Company symbol\uff0d2330<\/li>\n\n\n\n<li>Range of the data\uff0dfrom 2020\u201301\u201301 to 2020\u201312\u201331<\/li>\n\n\n\n<li>Optional\uff0donly select OHLC columns<\/li>\n\n\n\n<li>paginate \uff0ddata can be obtained in stages through this parameter<\/li>\n<\/ol>\n\n\n\n<p>** What we used in this example is from the product package in TEJ E SHOP. You can use the E Shop website to understand more information about TEJ\u2019s current products.\ud83d\udc4d\ud83d\udc4d Then we can see the output below:<\/p>\n\n\n\n<figure class=\"wp-block-image aligncenter\"><img decoding=\"async\" src=\"https:\/\/www.tejwin.com\/wp-content\/uploads\/1Mcu2tNzTh6pAEtzv9YBeWg.png\" alt=\"2020\u53f0\u7a4d\u96fb(2330)\u80a1\u5e02\u65e5\u8cc7\u6599-\u958b\u9ad8\u4f4e\u6536\"\/><figcaption class=\"wp-element-caption\">2020 TSMC(2330) Stock price data\u200a\u2014\u200aOHLC<\/figcaption><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"SavingReading_the_data\"><\/span>Saving\/Reading the&nbsp;data<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>\ud83d\udcd3Numpy, pandas, and datetime are very important packages in Python data analysis. We can do most operations, just like what we are going to do, through them.<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">import pandas as pd<br>import numpy as np<br>import datetime<\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Excel CSV<\/strong><br>We only need to use these lines of code to save or read our data into CSV file\uff1a<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-preformatted\">#save<br>path =\u2019path you want to save\/{name}.csv\u2019.format(name = \u201cTSMC\u201d)<br>TSMC.to_csv(path , index=False )<\/pre>\n\n\n\n<pre class=\"wp-block-preformatted\">#read<br>TSMC = pd.read_csv('TSMC.csv')<\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Excel Xlsx<br>In addition to a CSV file, Excel has another storage file format called Xlsx<\/strong>. If there are many data tables you want to save in the same file, we can store them through Xlsx. The only thing you may have to pay attention to is that<strong> because the date format of the TEJ API is different from this to_excel() function.<\/strong> Therefore, we will have to process the date format before saving the data.<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-preformatted\">#path <br>path =\u2019path you want to save\/{name}.xlsx\u2019.format(name = \u201cStock_List\u201d)<br>writer = pd.ExcelWriter(PATH , engine='xlsxwriter')<\/pre>\n\n\n\n<pre class=\"wp-block-preformatted\">#data type process<br>TSMC['mdate'] = <br> TSMC['mdate'].apply(lambda x: pd.to_datetime(x).date())<\/pre>\n\n\n\n<pre class=\"wp-block-preformatted\">#save<br>TSMC.to_excel(writer, sheet_name='2330' ,index = False)<br>writer.save()<\/pre>\n\n\n\n<pre class=\"wp-block-preformatted\">#read<br>TSMC = pd.read_excel('Stock_List.xlsx', sheet_name = '2330')<\/pre>\n\n\n\n<pre class=\"wp-block-preformatted\">#there are more functions in read_excel()\uff0cyou can do through its <a href=\"https:\/\/pandas.pydata.org\/pandas-docs\/stable\/generated\/pandas.read_excel.html\" rel=\"noreferrer noopener\" target=\"_blank\">Document<\/a> to get the parameters you need\u3002<\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>SQLite<\/strong><br>What if the amount of data is getting larger and larger and it will take you much time when opening the excel? <strong>Now you may need SQLite at this time!<\/strong> SQLite is very convenient to use in Python due to its characteristics. We will briefly introduce how to connect to the database and save\/read the data. For detailed instructions, please refer to the <a href=\"https:\/\/www.sqlite.org\/lang.html\" rel=\"noreferrer noopener\" target=\"_blank\"><strong>SQLite official website<\/strong>.<\/a><\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-preformatted\">#import module<br>import sqlite3<\/pre>\n\n\n\n<pre class=\"wp-block-preformatted\">#connect the sqlite db<br>conn = sqlite3.connect('mydatabase.db')<\/pre>\n\n\n\n<pre class=\"wp-block-preformatted\">#save<br>TSMC.to_sql(name='TSMC', con=conn, if_exists=\"replace\", index=False)<\/pre>\n\n\n\n<pre class=\"wp-block-preformatted\">#read<br>sql_query = \"SELECT * FROM TSMC\"<br>TSMC = pd.read_sql(sql_query, con=conn, coerce_float=False)<\/pre>\n\n\n\n<pre class=\"wp-block-preformatted\">#close the db<br><code>conn.close()<\/code><\/pre>\n\n\n\n<p>** Using the database management software(we use DBeaver) to use whether we have successfully imported:<\/p>\n\n\n\n<figure class=\"wp-block-image aligncenter caption-align-center\"><img decoding=\"async\" src=\"https:\/\/www.tejwin.com\/wp-content\/uploads\/1_12iScup8K64dokPe-iK9_ug.png\" alt=\"\"\/><figcaption class=\"wp-element-caption\">After importing into the&nbsp;SQLite<\/figcaption><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Conclusion\"><\/span>Conclusion<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<p>What we share with you this time is the introduction to get the data through TEJ API and how to save\/read it. <strong>Surely, we will write more articles related to data analysis and applications through Medium in the future!<\/strong><br>Finally, if you have any questions or suggestions after reading this article, please leave a message or email us, we will try our best to reply to you.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"Links_related_to_this_article_again\"><\/span>Links related to this article&nbsp;again<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>API Official Website: <\/strong><a href=\"https:\/\/api.tej.com.tw\/\" target=\"_blank\" rel=\"noreferrer noopener\" class=\"ek-link\"><strong>TEJ API <strong>Official Website<\/strong><\/strong><\/a><\/li>\n\n\n\n<li><strong>The Product Package: <\/strong><a href=\"https:\/\/eshop.tej.com.tw\/E-Shop\/Edata_caseIntro\/1\" target=\"_blank\" rel=\"noreferrer noopener\"><strong>TEJ E SHOP<\/strong><\/a><\/li>\n\n\n\n<li><strong>Source Code: <\/strong><a href=\"https:\/\/github.com\/tejtw\/TEJAPI_Python_Medium_Rookies\" target=\"_blank\" rel=\"noreferrer noopener\"><strong>TEJ GITHUB<\/strong><\/a><\/li>\n<\/ul>\n","protected":false},"excerpt":{"rendered":"<p>&#8211; how to grab data from TEJ&nbsp;API? What is TEJ&nbsp;API? Would you find it very difficult to find a high-quality and stable source of data before doing an analysis on it? Or is it time-consuming to maintain your own web-crawling program after spending a lot of time creating it? Now, TEJ (Taiwan Economic Journal) has [&hellip;]<\/p>\n","protected":false},"featured_media":14335,"template":"","tags":[2904,2572,2371,2640],"insight-category":[690,50],"class_list":["post-14309","insight","type-insight","status-publish","has-post-thumbnail","hentry","tag-beginners-guide","tag-data-analysis","tag-python","tag-tej-api","insight-category-data-analysis","insight-category-fintech"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.tejwin.com\/en\/wp-json\/wp\/v2\/insight\/14309","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.tejwin.com\/en\/wp-json\/wp\/v2\/insight"}],"about":[{"href":"https:\/\/www.tejwin.com\/en\/wp-json\/wp\/v2\/types\/insight"}],"version-history":[{"count":0,"href":"https:\/\/www.tejwin.com\/en\/wp-json\/wp\/v2\/insight\/14309\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.tejwin.com\/en\/wp-json\/wp\/v2\/media\/14335"}],"wp:attachment":[{"href":"https:\/\/www.tejwin.com\/en\/wp-json\/wp\/v2\/media?parent=14309"}],"wp:term":[{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.tejwin.com\/en\/wp-json\/wp\/v2\/tags?post=14309"},{"taxonomy":"insight-category","embeddable":true,"href":"https:\/\/www.tejwin.com\/en\/wp-json\/wp\/v2\/insight-category?post=14309"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}