你必須很努力

Day24 - 將臺灣證券交易所的除權除息計算結果表存入 DB

2021/10/07
字數統計: 632閱讀時間: 3 min

前言

前面已經知道如何抓「臺灣證券交易所」的除權除息計算結果表 CSV 檔,接下來要處理資料,並存入 DB

說明

需要考量的情境,與前一篇是一樣的 (描述越來越精簡 XD)

實作

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
# app/features/twse/twt_49u/save_to_db.rb

module Twse::Twt49u
class SaveToDb

include Twse::Helpers

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

latest_data_date = find_latest_data_date
return puts "#{self.class}, 已經是最新的資料" if latest_data_date == start_time.to_date

is_linux = `uname -a`[/Linux/].present?
file_paths = Dir["data/twse/TWT49U/*"]
file_paths.each do |file_path|
rows = decode_data(file_path, is_linux)
first_year, end_year = time_range(rows)
row_index, end_index = rows_range(rows)
all_rows, data_date_infos = filter_rows(rows, row_index, end_index)
next if not_process?(data_date_infos, latest_data_date)

filtered_stocks = filter_by_stocks(all_rows)
Stock.import(filtered_stocks) if filtered_stocks.present?

import_ex_stocks(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_data_date
ExStock.latest_data_date
end

def time_range(rows)
first_year, first_month, first_day, end_year, end_month, end_day = rows[0].scan(/\d+/)
[first_year, end_year].each_with_index do |year, index|
year = "20" + (year.to_i + 11).to_s[1..2]
if index.zero?
first_year = year
else
end_year =year
end
end

return [first_year, end_year]
end

def rows_range(rows)
row_index = nil
rows.each_with_index do |row, index|
if row.include?("資料日期")
row_index = index
break
end
end

end_index = nil
rows.each_with_index do |row, index|
if row.include?("公式")
end_index = index
break
end
end

return [row_index, end_index]
end

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

year, month, day = row_item[0].scan(/\d+/)
year = "20" + (year.to_i + 11).to_s[1..2]
data_date = year + month + day
row_item[0] = data_date.to_date
all_rows << row_item
data_date_infos << row_item[0]
end

return [all_rows, data_date_infos]
end

def not_process?(data_date_infos, latest_data_date)
data_date_infos.uniq!
latest_data_date.present? && data_date_infos.max <= latest_data_date
end

def filter_by_stocks(all_rows)
stock_infos = []
all_rows.each { |rows| stock_infos << { code: rows[1], name: rows[2] } }
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
next if need_create_stocks.any? { |item| item.code == stock_info[:code] }

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

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

need_create_ex_stocks = []
all_rows.each do |row|
stock = stocks[row[1]]

need_create_ex_stocks << stock.exs.new(
data_date: row[0],
closing_price_before: row[3],
reference_price: row[4],
dr_value: row[5],
dividend_right: ExStock::DIVIDEND_RIGHT[row[6]],
limit_up: row[7],
limit_down: row[8],
opening_reference_price: row[9],
ex_dividend_reference_price: row[10],
reporting_day: row[12],
price_book: row[13],
eps: row[14],
)
end
ExStock.import(need_create_ex_stocks) if need_create_ex_stocks.present?
end

end
end

檢查是否有存入 DB

小結

我猜看到這,已經不少人看不懂或沒在看了 (笑)

說明越寫越精簡,想說 code 已經直接貼出來了,直接看 code 比較快,若發現有更好的寫法,歡迎留言和我說~


鐵人賽文章連結:https://ithelp.ithome.com.tw/articles/10272913
medium 文章連結:https://link.medium.com/VfJfmvMuTjb
本文同步發布於 小菜的 Blog https://riverye.com/

備註:之後文章修改更新,以個人部落格為主

原文連結:https://riverye.com/2021/10/07/Day24-將臺灣證券交易所的除權除息計算結果表存入-DB/

發表日期:2021-10-07

更新日期:2021-09-27

CATALOG
  1. 1. 前言
  2. 2. 說明
  3. 3. 實作
  4. 4. 檢查是否有存入 DB
  5. 5. 小結