S&P 500 index earning data. how to get EPS #geteps

概要

1. データの取得

2. 修正加工コマンド生成

3. 取り込み

データ

以下から取得する。googleで「spglobal index earning」で検索すれば見つかる。

https://www.spglobal.com/spdji/en/documents/additional-material/sp-500-eps-est.xlsx

またこちらでも見つけられる。

https://www.spglobal.com/spdji/en/search/?query=index+earnings&activeTab=all

CSVに変換する

変換結果はこうなる。

12/31/2023,,$62.88,$56.37,,,,,$240.56,$214.23
9/30/2023,,$60.98,$54.31,,,,,$235.64,$209.12
6/30/2023,,$59.55,$52.68,,,,,$230.65,$204.04
3/31/2023,,$57.15,$50.87,,,,,$218.35,$194.76
12/31/2022,,$57.96,$51.25,,,,,$210.56,$189.88
9/30/2022,,$55.99,$49.23,,,,,$209.33,$192.57
6/30/2022 ,3785.38,$47.25,$43.41,,,,,$205.36,$192.93
3/31/2022 ,4530.41,$49.36,$45.99,,21.56,22.89,,$210.16,$197.91
12/31/2021 ,4766.18,$56.73,$53.94,,22.89,24.09,,$208.21,$197.87
9/30/2021,4307.54,$52.02,$49.59,,22.71,24.56,,$189.66,$175.37

なお、オリジナルでは当該四半期データは以下のように記録されている。

12/31/2023,,$62.88,$56.37,,,,,$240.56,$214.23
9/30/2023,,$60.98,$54.31,,,,,$235.64,$209.12
6/30/2023,,$59.55,$52.68,,,,,$230.65,$204.04
3/31/2023,,$57.15,$50.87,,,,,$218.35,$194.76
12/31/2022,,$57.96,$51.25,,,,,$210.56,$189.88
9/30/2022,,$55.99,$49.23,,,,,$209.33,$192.57
6/30/2022 (95.8%),3785.38,$47.25,$43.41,,,,,$205.36,$192.93
,,,,,18.43,19.62,"(P/E on Jun,'22 price)",,
ACTUALS,,,,,,,,,
3/31/2022 ,4530.41,$49.36,$45.99,,21.56,22.89,,$210.16,$197.91
12/31/2021 ,4766.18,$56.73,$53.94,,22.89,24.09,,$208.21,$197.87
9/30/2021,4307.54,$52.02,$49.59,,22.71,24.56,,$189.66,$175.37
6/30/2021 ,4297.50,$52.05,$48.39,,31.22,34.17,,$137.64,$125.78
3/31/2021,3972.89,$47.41,$45.95,,46.42,51.34,,$85.59,$77.39
12/31/2020,3756.07,$38.18,$31.44,,98.38,119.47,,$38.18,$31.44

邪魔なので、エディタを使用して以下のように修正する。

12/31/2023,,$62.88,$56.37,,,,,$240.56,$214.23
9/30/2023,,$60.98,$54.31,,,,,$235.64,$209.12
6/30/2023,,$59.55,$52.68,,,,,$230.65,$204.04
3/31/2023,,$57.15,$50.87,,,,,$218.35,$194.76
12/31/2022,,$57.96,$51.25,,,,,$210.56,$189.88
9/30/2022,,$55.99,$49.23,,,,,$209.33,$192.57
6/30/2022 ,3785.38,$47.25,$43.41,,,,,$205.36,$192.93
3/31/2022 ,4530.41,$49.36,$45.99,,21.56,22.89,,$210.16,$197.91
12/31/2021 ,4766.18,$56.73,$53.94,,22.89,24.09,,$208.21,$197.87
9/30/2021,4307.54,$52.02,$49.59,,22.71,24.56,,$189.66,$175.37
6/30/2021 ,4297.50,$52.05,$48.39,,31.22,34.17,,$137.64,$125.78
3/31/2021,3972.89,$47.41,$45.95,,46.42,51.34,,$85.59,$77.39
12/31/2020,3756.07,$38.18,$31.44,,98.38,119.47,,$38.18,$31.44

tac コマンド

古い日付のデータを先にソートし直す。

　 tac eps.csv > eps202209.csv

ツールを使用してコマンドを生成

2021年分データから生成する場合。

tac eps.txt | awk '{gsub("\\$","",$NF);print "eps_year_xts[\"2021::\"]["NR"] <- "$NF}'

1988年分データから生成する場合

csv のエントリをRのコマンドに変換する。1988はcsvの先頭日付の年号を指定する。

cat eps202209.csv | awk -F, '{print $1,$10}' | awk '{gsub("\$","",$NF);print "eps_year_xts["1988::"]["NR"] <- "$NF}'

出力結果。

eps_year_xts["1988::"][131] <- 158.76
eps_year_xts["1988::"][132] <- 175.37
eps_year_xts["1988::"][133] <- 197.87
eps_year_xts["1988::"][134] <- 197.91
eps_year_xts["1988::"][135] <- 192.93
eps_year_xts["1988::"][136] <- 192.57
eps_year_xts["1988::"][137] <- 189.88
eps_year_xts["1988::"][138] <- 194.76
eps_year_xts["1988::"][139] <- 204.04
eps_year_xts["1988::"][140] <- 209.12
eps_year_xts["1988::"][141] <- 214.23

入力

コピペして入力する。

データ

eps_year_xts["1988::"][1] <- 5.62
eps_year_xts["1988::"][2] <- 12.36
eps_year_xts["1988::"][3] <- 18.84
eps_year_xts["1988::"][4] <- 23.69
eps_year_xts["1988::"][5] <- 22.87
eps_year_xts["1988::"][6] <- 21.67
eps_year_xts["1988::"][7] <- 21.26
eps_year_xts["1988::"][8] <- 21.74
eps_year_xts["1988::"][9] <- 21.34
eps_year_xts["1988::"][10] <- 20.94
eps_year_xts["1988::"][11] <- 19.41
eps_year_xts["1988::"][12] <- 17.82
eps_year_xts["1988::"][13] <- 15.97
eps_year_xts["1988::"][14] <- 16.19
eps_year_xts["1988::"][15] <- 17.05
eps_year_xts["1988::"][16] <- 18.04
eps_year_xts["1988::"][17] <- 19.09
eps_year_xts["1988::"][18] <- 19.84
eps_year_xts["1988::"][19] <- 19.33
eps_year_xts["1988::"][20] <- 20.41
eps_year_xts["1988::"][21] <- 21.89
eps_year_xts["1988::"][22] <- 22.71
eps_year_xts["1988::"][23] <- 25.20
eps_year_xts["1988::"][24] <- 27.33
eps_year_xts["1988::"][25] <- 30.60
eps_year_xts["1988::"][26] <- 32.55
eps_year_xts["1988::"][27] <- 34.43
eps_year_xts["1988::"][28] <- 35.18
eps_year_xts["1988::"][29] <- 33.96
eps_year_xts["1988::"][30] <- 34.04
eps_year_xts["1988::"][31] <- 34.91
eps_year_xts["1988::"][32] <- 36.00
eps_year_xts["1988::"][33] <- 38.73
eps_year_xts["1988::"][34] <- 40.24
eps_year_xts["1988::"][35] <- 40.55
eps_year_xts["1988::"][36] <- 40.64
eps_year_xts["1988::"][37] <- 39.72
eps_year_xts["1988::"][38] <- 39.54
eps_year_xts["1988::"][39] <- 38.97
eps_year_xts["1988::"][40] <- 38.09
eps_year_xts["1988::"][41] <- 37.71
eps_year_xts["1988::"][42] <- 38.38
eps_year_xts["1988::"][43] <- 41.02
eps_year_xts["1988::"][44] <- 43.96
eps_year_xts["1988::"][45] <- 48.17
eps_year_xts["1988::"][46] <- 50.95
eps_year_xts["1988::"][47] <- 51.92
eps_year_xts["1988::"][48] <- 53.70
eps_year_xts["1988::"][49] <- 50.00
eps_year_xts["1988::"][50] <- 45.44
eps_year_xts["1988::"][51] <- 36.79
eps_year_xts["1988::"][52] <- 28.31
eps_year_xts["1988::"][53] <- 24.69
eps_year_xts["1988::"][54] <- 24.70
eps_year_xts["1988::"][55] <- 26.74
eps_year_xts["1988::"][56] <- 30.04
eps_year_xts["1988::"][57] <- 27.59
eps_year_xts["1988::"][58] <- 30.32
eps_year_xts["1988::"][59] <- 34.55
eps_year_xts["1988::"][60] <- 38.58
eps_year_xts["1988::"][61] <- 48.74
eps_year_xts["1988::"][62] <- 52.00
eps_year_xts["1988::"][63] <- 56.15
eps_year_xts["1988::"][64] <- 57.77
eps_year_xts["1988::"][65] <- 58.55
eps_year_xts["1988::"][66] <- 60.32
eps_year_xts["1988::"][67] <- 63.36
eps_year_xts["1988::"][68] <- 66.57
eps_year_xts["1988::"][69] <- 69.93
eps_year_xts["1988::"][70] <- 72.67
eps_year_xts["1988::"][71] <- 74.49
eps_year_xts["1988::"][72] <- 78.57
eps_year_xts["1988::"][73] <- 81.51
eps_year_xts["1988::"][74] <- 83.15
eps_year_xts["1988::"][75] <- 84.92
eps_year_xts["1988::"][76] <- 78.60
eps_year_xts["1988::"][77] <- 66.18
eps_year_xts["1988::"][78] <- 60.39
eps_year_xts["1988::"][79] <- 51.37
eps_year_xts["1988::"][80] <- 45.95
eps_year_xts["1988::"][81] <- 14.88
eps_year_xts["1988::"][82] <- 6.86
eps_year_xts["1988::"][83] <- 7.51
eps_year_xts["1988::"][84] <- 12.54
eps_year_xts["1988::"][85] <- 50.97
eps_year_xts["1988::"][86] <- 60.93
eps_year_xts["1988::"][87] <- 67.10
eps_year_xts["1988::"][88] <- 71.86
eps_year_xts["1988::"][89] <- 77.35
eps_year_xts["1988::"][90] <- 81.31
eps_year_xts["1988::"][91] <- 83.87
eps_year_xts["1988::"][92] <- 86.98
eps_year_xts["1988::"][93] <- 86.95
eps_year_xts["1988::"][94] <- 88.54
eps_year_xts["1988::"][95] <- 87.92
eps_year_xts["1988::"][96] <- 86.50
eps_year_xts["1988::"][97] <- 86.51
eps_year_xts["1988::"][98] <- 87.70
eps_year_xts["1988::"][99] <- 90.95
eps_year_xts["1988::"][100] <- 94.37
eps_year_xts["1988::"][101] <- 100.20
eps_year_xts["1988::"][102] <- 100.85
eps_year_xts["1988::"][103] <- 103.12
eps_year_xts["1988::"][104] <- 105.96
eps_year_xts["1988::"][105] <- 102.31
eps_year_xts["1988::"][106] <- 99.25
eps_year_xts["1988::"][107] <- 94.91
eps_year_xts["1988::"][108] <- 90.66
eps_year_xts["1988::"][109] <- 86.53
eps_year_xts["1988::"][110] <- 86.44
eps_year_xts["1988::"][111] <- 86.92
eps_year_xts["1988::"][112] <- 89.09
eps_year_xts["1988::"][113] <- 94.55
eps_year_xts["1988::"][114] <- 100.29
eps_year_xts["1988::"][115] <- 104.02
eps_year_xts["1988::"][116] <- 107.08
eps_year_xts["1988::"][117] <- 109.88
eps_year_xts["1988::"][118] <- 115.44
eps_year_xts["1988::"][119] <- 122.48
eps_year_xts["1988::"][120] <- 130.39
eps_year_xts["1988::"][121] <- 132.39
eps_year_xts["1988::"][122] <- 134.39
eps_year_xts["1988::"][123] <- 135.27
eps_year_xts["1988::"][124] <- 132.90
eps_year_xts["1988::"][125] <- 139.47
eps_year_xts["1988::"][126] <- 116.33
eps_year_xts["1988::"][127] <- 99.23
eps_year_xts["1988::"][128] <- 98.22
eps_year_xts["1988::"][129] <- 94.13
eps_year_xts["1988::"][130] <- 128.20
eps_year_xts["1988::"][131] <- 158.76
eps_year_xts["1988::"][132] <- 175.37
eps_year_xts["1988::"][133] <- 197.87
eps_year_xts["1988::"][134] <- 197.91
eps_year_xts["1988::"][135] <- 192.93
eps_year_xts["1988::"][136] <- 192.57
eps_year_xts["1988::"][137] <- 189.88
eps_year_xts["1988::"][138] <- 194.76
eps_year_xts["1988::"][139] <- 204.04
eps_year_xts["1988::"][140] <- 209.12
eps_year_xts["1988::"][141] <- 214.23