Building Stockprice Database 3 (adjusted stock price)

So far I’ve created a self updating stock prices of KOSPI and KOSDAQ listed prices. However, simply computing stock returns based on closing prices can lead to erroneous returns since stock splits, mergers, and dividends can alter the scale of closing prices. In this post, I will work on computing adjusted stock prices. Despite the importance of adjusted stock prices in financial research, I was able to find surprising little information on how adjusted prices are computer. To my knowledge, there are two data sources that reliably offer adjusted stock prices: KOSCOM and FNGuide. Unfortunately data from both of the companies come with a fee and my experience examining the dataset suggests that adjusted prices provided by these venues are error-free. Certain portals such as Yahoo Finance provides adjusted stock prices for certain stocks, but the data is not comprehensive as well. I will talk about some of these examples later in the post. In this post, I will ignore dividends, which require additional webscraping, but will attempt them when I have more time.

There are a number of reason for which number of outstanding stocks change. It can include stock mergers, splits, and buybacks which can matter for computing adjusted stock prices, but it can also include stock option exercise and new issuances, which should not affect the adjusted stock prices. Since public companies file reports regarding mergers and splits, it is possible to scrape the data from DART website and manually implement them. However, given the amount of work involved, I will focus on trading suspensions as a signal for price adjustment and reverse-engineer FNGuide’s adjustment algorithm. Consider an recent filing for stock mergers filed by KR Motors (000040.KS).

The report specifies that trading will be suspended between 2020-02-04 ~ 2020-02-18 for 4 to 1 stock merger. As can be seen from the scraped data, KR motors stock trading was suspended during the period, and can be recognized by 0 opening price.

Based on the data I’ve already collected (opening price, closing price, and shares outstanding) I can identify the following scenarios.

  1. Trading suspension not related to number of shares (number of shares stays constant before and after suspension)
  2. Stock splits (number of shares after suspension increases)
  3. Stock merger (number of shares decrease after suspension by integer)
  4. Stock buyback (number of shares decrease after suspension by non-integer)

In case of 1-3, closing prices should be adjusted in proportion to shares outstanding. In case of 4, it is not immediately clear how and why the adjusted prices should be adjusted. In case of stock buy-backs, prices offered for stocks often well-exceed the market prices, as can be seen in the example of LS Networks (000680.KS).

LS Networks bought back 9.27% of shares at 11,200 won per share. The report was original filed on 2009-12-23 when the closing price was 8,230 won. Due to the premium paid for stocks, the investors’ returns change, so it is natural that the adjusted stock prices should change. Ideally, collecting data on proportion of stocks bought back and the prices paid for them to calculate the adjusted stock prices is optimal. However, due to the efforts required for this procedure, I follow a convention followed by FNGuide, and use the ratio of opening price after suspension and closing price before suspension to adjust stock prices.

FN Adjusted Price KRX Price

As can be seen in the FN adjusted prices (captured on February 18th 2020), during the suspension period 2010-04-13~2010-05-10, the adjusted price is the opening price on 2010-05-11. The adjusted price prior to suspension is scaled by 8,750 (opening price after suspension) / 8710(closing price before suspension). Now, it is time to apply this algorithm to compute the adjusted stock price. The first step is to append adjusted stock price column to the database. Since adjusted price is no longer guaranteed to be integer, I will store it as a flow with 2 decimal points.

ALTER TABLE `echo_blog`.`stockprices` 
ADD COLUMN `adj_prc` float(13,2) NULL AFTER `cls_prc`;

The python script is as follows.

import MySQLdb as sql

#Read Database User ID / User PW
with open("../credentials.txt", "r") as f:
    _, user_id, user_pw = f.read().split("|")

#Connect to Database
con = sql.connect(host="eric-cho.com", port=3308, database="echo_blog", user=user_id, password=user_pw, charset="utf8")
cursor = con.cursor() 

#Get list of all stocks in the database
cursor.execute("select distinct(isu_cd) from stockprices")
stockids = cursor.fetchall()

for idx,[stockid] in enumerate(stockids):
    cursor.execute("select tradedate, open_prc, cls_prc, list_stock_vol from stockprices where isu_cd = %s order by tradedate desc", [stockid])
    result = cursor.fetchall()
    for i, [tradedate, open_prc, cls_prc, list_stock_vol] in enumerate(result):
        if i == 0: #Set scale to 1 at most recent observation
            scale = 1
        elif next_open_prc !=0 and open_prc ==0: #Identify Suspension End Date
            if list_stock_vol == next_list_stock_vol: #Case 1
                pass
            elif next_list_stock_vol>list_stock_vol: #Case 2
                scale *= (list_stock_vol/next_list_stock_vol)
            elif list_stock_vol%next_list_stock_vol==0: #Case 3
                scale *= (list_stock_vol/next_list_stock_vol)
            else: # Case 4
                scale *= next_open_prc / cls_prc
        #Insert computed adjust price to database
        cursor.execute(f"update stockprices set adj_prc = %s where isu_cd = %s and tradedate = %s", [scale*cls_prc, stockid, tradedate])
        
        next_tradedate, next_open_prc, next_cls_prc, next_list_stock_vol = tradedate, open_prc, cls_prc, list_stock_vol
    print(idx, stockid)
    con.commit()

Snippets of this code is added into the daily stock update code to update the adjusted stock price as well. The current version of daily stock price update code can be found here

Tags: python  cron_job 

Discussion and feedback