background

Recently, I have been learning data analysis. At the end of the course, the teacher talked about the case of choosing stocks through quantitative analysis, which was very interesting. It happened that some people were speculating on stocks around me, so I simply made a software to practice what I learned. In the course, we mainly use Python related libraries to deal with the data of Bitcoin, and the amount of data is not large, but we can draw inferences from one example after understanding the principle.

First of all, let’s review the main points. There are two important indicators used when choosing stocks. See the screenshot of the courseware below for their definitions. I will not elaborate on the specific content, because I am a non-financial major, so it is a little difficult for me to explain these words. We just have to remember how do we calculate these two indices, and then how do we use them

KDJ indicator definition

Calculate K index

Calculate D index

Calculate J index

The experimental principle

Teacher talked about K and D are reflected in the curriculum stock trends, sensitive than K D, when worn on D K value (K > D) for the first time on behalf of the stock is likely to rebound upwards, is a buying opportunity at this time, when D value K (K < for the first time D), on behalf of a fall in the value of stock with the larger probability is selling opportunity at this time. Using this method, we were able to sift through more than 4, 000 stocks to pick a few that were likely to rebound, and then use human experience and other information to pick the favorite stocks. Our experimental thinking is as follows:

  1. First, the trading information of 4000 stocks in the past six months, including date, high, low, open and close, is obtained from the network and stored in the StockBars table
  2. Each record in the StockBars table is read with Python program, and the RSV index is calculated and stored in the StockRSVS table
  3. Finally, the StockRSVS table is read with Python program, and the three indexes K, D and J are calculated
  4. Finally, we use SQL statement to query the database, and each person can freely define the query mode based on the four values of RSV, k, d, j

Here we used KDJ financial knowledge, Python programming knowledge, SQL language and database related content, is also a comprehensive exercise. You can also use Sugar to draw large screen data analysis online — tool text (Visualize Sugar)

The experimental process

Preparation of experimental environment

We used Python development environment, here we use Docker directly get a mirror to use, save the installation of a bunch of messy dependencies, of course, you can also according to their own preferences to install

Docker pull docker.io/python

For the database, we use a free cloud database, MemfireDB https://memfiredb.com, which provides public network IP and a visual SQL editor to facilitate our subsequent data query

Experiment steps and code

Getting the raw data

Calculate the RSV index

   for stock in stocks:
        i += 1
        bars = session.query(StockBar).filter(
                    StockBar.stock_id == stock.id
                ).order_by(StockBar.date.asc()).all()

        if len(bars) < window:
            print("stock %s  bar less than window %s real %s cal next stock" % (stock.id, window, len(bars)))
            continue

        for bar in bars:
            rsv = session.query(StockRSV).filter(
                    StockRSV.id == bar.stock_id + "_" + str(bar.date)
                ).first()


            if rsv is not None:
                print("rsv: id:%s stock_id:%s, date:%s,rsv value:%s cal next bar" % (
                    rsv.id, rsv.stock_id, rsv.date, rsv.rsv
                ))
                continue

            prevbars = session.query(StockBar).filter(
                    StockBar.stock_id == stock.id,
                    StockBar.date <= bar.date
                ).order_by(StockBar.date.desc()).limit(window).all()

            if len(prevbars) < window:
                print("stock %s date %s perv less than window %s cal next date" % (stock.id, bar.date, window))
                continue

            for prevbar in prevbars:
                print("prevbar: id %s , stock_id:%s, date:%s, open:%s, high:%s, low:%s, close:%s" % 
                        (prevbar.id, prevbar.stock_id, prevbar.date, prevbar.open, prevbar.high, prevbar.low, prevbar.close))

            low = prevbars[0].low
            high = prevbars[0].high
            lastopen = prevbars[0].open
            lastclose = prevbars[0].close

            for prevbar in prevbars:
                if prevbar.high >= high:
                    high = prevbar.high
                if prevbar.low <= low:
                    low = prevbar.low
            
            print("rsv: stock_id %s, date:%s lastopen:%s, lastclose:%s, high:%s, low:%s" % 
                    ( bar.stock_id, bar.date, lastopen, lastclose, high, low))

            stockrsv = StockRSV(id = bar.stock_id + "_" + str(bar.date),
                stock_id = bar.stock_id,
                date = bar.date,
                rsv = 100 * (lastclose - low) / (high - low))
            session.add(stockrsv)
            session.commit()

The calculation results

Calculate the KDJ index

for stock in stocks: i += 1 rsvs = session.query(StockRSV).filter( StockRSV.stock_id == stock.id ).order_by(StockRSV.date.asc()).all() if len(rsvs) < 1: print("stock %s rsv less than window %s real %s cal next stock" % (stock.id, 1, len(rsvs))) continue for stockrsv in rsvs: curkdj = session.query(StockKDJ).filter( StockKDJ.id == stockrsv.stock_id + "_" + str(stockrsv.date) ).first() if curkdj  is not None: print("kdj id:%s, stock_id:%s,date:%s,k:%s, d:%s,j:%s exist cal next" % ( curkdj.id, curkdj.stock_id, curkdj.date, curkdj.k, curkdj.d, curkdj.j )) continue lastkdj = session.query(StockKDJ).filter( StockKDJ.stock_id == stockrsv.stock_id, StockKDJ.date < stockrsv.date ).order_by(StockKDJ.date.desc()).limit(1).first() lastkvalue = 0 lastdvalue = 0 if lastkdj  is not None: lastkvalue = lastkdj.k lastdvalue = lastkdj.d stockkdj = StockKDJ(id = stockrsv.stock_id + "_" + str(stockrsv.date), stock_id = stockrsv.stock_id, date = stockrsv.date, k = curkvalue, d = curdvalue, j = 0) session.add(stockkdj) session.commit()

The calculation results

Use SQL to pick stocks

We select 10 stocks with low RSV and K>D recently

Go to the stock software to view the trend chart of these stocks

The experimental conclusion

Through this experiment, we explored a way to use Python to obtain stock data. Because there are many records and the calculation process cannot be recursive, we can only calculate the indexes through a circular way combined with the database. Finally, the results of the calculation are stored in the database, and the rich semantics of SQL language can be used to verify various stock selection models flexibly. Although the experiment may not be as accurate as the professional institutions, but in the flexible, you can combine their own experience to adjust.