Day23 - 将台湾证券交易所的每日收盘行情存入 DB

前言

前面已经知道如何抓「台湾证券交易所」的每日收盘行情 CSV 档，接下来要处理资料，并存入 DB

说明

在处理过程中，需要考量，可能会有新上市的公司，若有的话，要建立 Stock，至於已经下市的公司，则应该要软删除 (这部分还没做，可自行研究)

同一天的资料，若执行多次，应该要判断是否已在 DB 建立过，不应该重复建立

实作

# app/features/twse/allbut_0999/save_to_db.rb

module Twse::Allbut0999
  class SaveToDb

    include Twse::Helpers

    def execute
      start_time = Time.current
      puts "#{self.class}, start_time: #{start_time.to_s}"

      end_transaction_date = find_latest_transaction_date
      return puts "#{self.class}, 已经是最新的资料" if end_transaction_date == start_time

      is_linux = `uname -a`[/Linux/].present?
      file_paths = Dir["data/twse/ALLBUT0999/*/*/*"]
      file_paths.each do |file_path|
        rows = decode_data(file_path, is_linux)
        next if not_process?(rows, end_transaction_date)

        row_index = find_rows_index(rows)
        all_rows = filter_rows(rows, row_index)
        filtered_stocks = filter_by_stocks(all_rows)
        Stock.import(filtered_stocks) if filtered_stocks.present?

        import_daily_quotes(all_rows)
      end
      puts "#{self.class}, done_time:#{Time.current}, #{(Time.current - start_time).to_s} sec"
    rescue StandardError => e
      puts "errors: #{e.inspect}, #{e.backtrace}"
    end

    private

    def find_latest_transaction_date
      DailyQuote.latest_transaction_date
    end

    def not_process?(rows, end_transaction_date)
      year, month, day = rows[0].scan(/\d+/)
      year = "20" + (year.to_i + 11).to_s[1..2]
      @file_date = year + month + day
      end_transaction_date.present? && @file_date.to_date <= end_transaction_date
    end

    def find_rows_index(rows)
      row_index = nil
      rows.each_with_index { |row, index| row_index = index if row.include?("证券代号") }
      row_index
    end

    def filter_rows(rows, row_index)
      all_rows = []
      rows[(row_index + 1)..-1].each do |row_string|
        row_item = []
        row_string.split(',"').each { |row| row_item << row.gsub(/[=|,|"]/, '') }
        all_rows << row_item
      end
      all_rows
    end

    def filter_by_stocks(all_rows)
      stock_infos = []
      all_rows.each { |rows| stock_infos << { code: rows[0], name: rows[1] } }
      stocks = Stock.all.select(:code).index_by(&:code)

      need_create_stocks = []
      stock_infos.each do |stock_info|
        stock = stocks[stock_info[:code]]
        next if stock

        need_create_stocks << Stock.new(code: stock_info[:code], name: stock_info[:name])
      end
      need_create_stocks
    end

    def import_daily_quotes(all_rows)
      stocks = Stock.all.select(:code).index_by(&:code)

      need_create_daily_quotes = []
      all_rows.each do |row|
        stock = stocks[row[0]]

        need_create_daily_quotes << stock.daily_quotes.new(
          transaction_date: @file_date.to_date,
          trade_volume: row[2],
          number_of_transactions: row[3],
          trade_price: row[4],
          opening_price: row[5],
          highest_price: row[6],
          lowest_price: row[7],
          closing_price: row[8],
          ups_and_downs: row[9],
          price_difference: row[10],
          last_best_bid_price: row[11],
          last_best_bid_volume: row[12],
          last_best_ask_price: row[13],
          last_best_ask_volume: row[14],
          price_earning_ratio: row[15],
        )
      end
      DailyQuote.import(need_create_daily_quotes) if need_create_daily_quotes.present?
    end

  end
end

检查是否有存入 DB

小结

现在看别人写好的 code ，应该会觉得蛮简单的，当下自己在做的时候，并没有范例的 code 可以参考，边摸索边做出来的，过程的酸甜苦辣只有自己知道，唯有自己做过，才会知道~

铁人赛文章连结：https://ithelp.ithome.com.tw/articles/10272899
medium 文章连结：https://link.medium.com/HmmocOLuTjb
本文同步发布於 小菜的 Blog https://riverye.com/

备注：之後文章修改更新，以个人部落格为主