複数の証券会社で資産を管理していることにより、現在のアセットアロケーションの把握が煩雑化・困難化している方、多いのではないかと思います。
私は、エクセルマクロ(VBA)で複数の証券会社からWEBスクレイピングをすることで、現在の運用資産価額の総額を把握し、運用方針検討の一助としています。
WEBスクレイピングとは、WEBサイトからデータを収集するコンピューター技術のことです。
本記事では、当該 エクセルマクロ(VBA) について、シェアします。
スクレイピングの対象サイト
スクレイピングの対象サイトは以下です。
- SBI証券/投資信託トータルリターン サマリー
- SBI証券/個別銘柄分析データ
- SBIベネフィット・システムズ（確定拠出年金）
- SBIネオモバイル証券/特定口座損益明細
- SBIネオモバイル証券/口座管理（ポートフォリオ）
- 野村証券/預り資産
- 野村証券/特定口座譲渡益税履歴
- 持株WEBサービス(野村証券)
- NRK（確定拠出年金）
- 楽天証券（未成年総合）/トータルリターン（投資信託）
- 楽天証券（ジュニアNISA）/トータルリターン（投資信託）
- 楽天証券/トータルリターン（投資信託）
- 楽天証券/保有商品一覧
- 楽天証券/配当・分配金/すべて
- CAPEレシオ(シラーPER)
マクロ入りのエクセルブック
マクロ入りのエクセルブックは以下の通り
集計結果は以下のように表示されます。
グラフで、各種証券会社に置いた資産総計のアセットアロケーションを把握します。
マクロの紹介
マクロの初期設定
最初に初期設定が必要です。
まず、以下記事を参考に、Seleniumの設定をします。
マクロコード全文紹介
メインのマクロコードは以下の通り。
個別機能の紹介・解説は次項を参照ください。
Sub データ取得(USER_ID_1 As String, USER_PAS1 As String _
, USER_ID_2 As String, USER_PAS2 As String _
, USER_ID_3 As String, USER_PAS3 As String _
, USER_ID_4 As String, USER_PAS4 As String, USER_ID_5 As String _
, USER_ID_6 As String, USER_PAS6 As String _
, USER_ID_7 As String, USER_PAS7 As String _
, USER_ID_8 As String, USER_PAS8 As String _
, USER_ID_9 As String, USER_PAS9 As String _
, USER_ID_10 As String, USER_PAS10 As String _
, USER_ID_11 As String, USER_PAS11 As String _
, Path As String _
, PROFILE_PATH As String)
Dim myBy As New By
Workbooks("運用資産価額管理.xlsm").Activate
Application.ScreenUpdating = False
driver.AddArgument ("user-data-dir=" & PROFILE_PATH)
driver.Start "chrome"
Application.Wait Now + TimeSerial(0, 0, 2)
'ネオモバイル
15:
On Error GoTo ERR15
driver.Get "https://trade.sbineomobile.co.jp/login"
driver.Window.Maximize
driver.FindElementByXPath("/html/body/div[3]/div/div/div[1]/div[2]/div[1]/main/form/section[1]/div/div[1]/input").SendKeys USER_ID_3
driver.FindElementByXPath("/html/body/div[3]/div/div/div[1]/div[2]/div[1]/main/form/section[1]/div/div[2]/input").SendKeys USER_PAS3
driver.FindElementByXPath("/html/body/div[3]/div/div/div[1]/div[2]/div[1]/main/form/section[2]/button").Click
driver.Get "https://trade.sbineomobile.co.jp/trade/domestic/tradeHistory/capitalGainTaxDetail"
If driver.FindElementByXPath("/html/body/div[3]/div/div/div[1]/div[2]/div[1]/main/section[3]/section/div[1]/table/tbody/tr[1]/td/span").Text = "--" Then
Sheets("個別履歴").Range("L2") = 0
Else
Sheets("個別履歴").Range("L2") = driver.FindElementByXPath("/html/body/div[3]/div/div/div[1]/div[2]/div[1]/main/section[3]/section/div[1]/table/tbody/tr[1]/td/span").Text * -1
End If
On Error GoTo 0
If driver.FindElementByXPath("/html/body/div[3]/div/div/div[1]/div[2]/div[1]/main/section[3]/section/div[1]/table/tbody/tr[2]/td/span").Text = "--" Then
Sheets("個別履歴").Range("L3") = 0
Else
Sheets("個別履歴").Range("L3") = driver.FindElementByXPath("/html/body/div[3]/div/div/div[1]/div[2]/div[1]/main/section[3]/section/div[1]/table/tbody/tr[2]/td/span").Text
End If
If driver.FindElementByXPath("/html/body/div[3]/div/div/div[1]/div[2]/div[1]/main/section[3]/section/div[2]/table/tbody/tr[1]/td/span").Text = "--" Then
Sheets("個別履歴").Range("L4") = 0
Else
Sheets("個別履歴").Range("L4") = driver.FindElementByXPath("/html/body/div[3]/div/div/div[1]/div[2]/div[1]/main/section[3]/section/div[2]/table/tbody/tr[1]/td/span").Text * -1
End If
If driver.FindElementByXPath("/html/body/div[3]/div/div/div[1]/div[2]/div[1]/main/section[3]/section/div[2]/table/tbody/tr[2]/td/span").Text = "--" Then
Sheets("個別履歴").Range("L5") = 0
Else
Sheets("個別履歴").Range("L5") = driver.FindElementByXPath("/html/body/div[3]/div/div/div[1]/div[2]/div[1]/main/section[3]/section/div[2]/table/tbody/tr[2]/td/span").Text
End If
driver.Get "https://trade.sbineomobile.co.jp/account/portfolio"
For i = 1 To 20
On Error GoTo ERR14
driver.FindElementByXPath("/html/body/div[3]/div/div/div[1]/div[2]/div[1]/main/div[3]/section/button").Click
Application.Wait Now + TimeSerial(0, 0, 2)
Next i
14:
On Error GoTo 0
Sheets("ﾈｵﾓﾊﾞ一覧").Select
Columns("A:D").Select
Selection.Clear
Columns(1).NumberFormatLocal = "@"
Sheets("ﾈｵﾓﾊﾞ一覧").Cells(2, 3) = driver.FindElementByXPath("/html/body/div[3]/div/div/div[1]/div[2]/div[1]/main/div[1]/section[4]/ul/li[1]/p[2]/span").Text
Application.Calculation = xlCalculationManual
For i = 1 To driver.FindElementsByClass("name").Count
Sheets("ﾈｵﾓﾊﾞ一覧").Cells(i + 2, 1) = Format(driver.FindElementByXPath("/html/body/div[3]/div/div/div[1]/div[2]/div[1]/main/div[1]/section[" & i + 4 & "]/div/div[1]/div[2]/p").Text, "@")
Sheets("ﾈｵﾓﾊﾞ一覧").Cells(i + 2, 2) = Format(driver.FindElementByXPath("/html/body/div[3]/div/div/div[1]/div[2]/div[1]/main/div[1]/section[" & i + 4 & "]/div/div[1]/div[2]/h4/a").Text, "@")
If driver.FindElementByXPath("/html/body/div[3]/div/div/div[1]/div[2]/div[1]/main/div[1]/section[" & i + 4 & "]/div/div[2]/div[1]/span").Text = "--" Then
Sheets("ﾈｵﾓﾊﾞ一覧").Cells(i + 3, 3) = 0
Else
Sheets("ﾈｵﾓﾊﾞ一覧").Cells(i + 2, 3) = driver.FindElementByXPath("/html/body/div[3]/div/div/div[1]/div[2]/div[1]/main/div[1]/section[" & i + 4 & "]/div/div[2]/div[1]/span").Text
End If
Sheets("ﾈｵﾓﾊﾞ一覧").Cells(i + 2, 4) = driver.FindElementByXPath("/html/body/div[3]/div/div/div[1]/div[2]/div[1]/main/div[1]/section[" & i + 4 & "]/div/div[2]/div[2]/span").Text
Next i
Application.Calculation = xlCalculationAutomatic
ActiveWorkbook.Worksheets("ﾈｵﾓﾊﾞｻﾏﾘ").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("ﾈｵﾓﾊﾞｻﾏﾘ").Sort.SortFields.Add Key:=Range("L1"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("ﾈｵﾓﾊﾞｻﾏﾘ").Sort
.SetRange Range(Cells(1, 1), Cells(18, 20))
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
If ActiveWorkbook.Worksheets("銘柄データ").FilterMode = True Then
ActiveWorkbook.Worksheets("銘柄データ").ShowAllData
Else
ActiveWorkbook.Worksheets("銘柄データ").Rows(1).AutoFilter
End If
ActiveWorkbook.Worksheets("銘柄データ").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("銘柄データ").Sort.SortFields.Add Key:=Range("I1"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("銘柄データ").Sort.SortFields.Add Key:=Range("E1"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("銘柄データ").Sort
.SetRange Range(Cells(1, 1), Cells(Sheets("銘柄データ").Cells(Rows.Count, 1).End(xlUp).Row, 300))
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.Worksheets("銘柄データ").Rows(1).AutoFilter Field:=8, Criteria1:="<>#VALUE!", Operator:=xlFilterValues
'SBI証券 夫
driver.Get "https://www.sbisec.co.jp/ETGate"
driver.FindElementByXPath("//*[@id='user_input']/input").SendKeys USER_ID_1
driver.FindElementByXPath("//*[@id='password_input']/input").SendKeys USER_PAS1
Application.Wait Now + TimeSerial(0, 0, 2)
driver.FindElementByXPath("/html/body/table/tbody/tr[1]/td[2]/div[2]/form/p[2]/input").Click
Application.Wait Now + TimeSerial(0, 0, 1)
driver.Get "https://site2.sbisec.co.jp/ETGate/?_ControlID=WPLETsiR001Control&_DataStoreID=DSWPLETsiR001Control&_PageID=WPLETsiR001Idtl70&_ActionID=DefaultAID&s_rkbn=2&s_btype=&i_stock_sec=8306&i_dom_flg=1&i_exchange_code=JPN&i_output_type=6&exchange_code=TKY&stock_sec_code_mul=8306&ref_from=1&ref_to=20&wstm4130_sort_id=&wstm4130_sort_kbn=&qr_keyword=1&qr_suggest=1&qr_sort=1"
sTmp = ""
Dim LinkURL As Variant 'As HTMLAnchorElement
For Each LinkURL In driver.FindElementsByTag("iframe").Attribute("src")
If InStr(LinkURL, "token") <> 0 Then
sTmp = Replace(LinkURL, "https://graph.sbisec.co.jp/sbiscreener/analysis?token=", "")
sTmp = Replace(sTmp, "&sym=8306.T", "")
TOKEN1 = sTmp
End If
Next LinkURL
sTmp = ""
For i = 2 To Sheets("銘柄データ").Cells(Rows.Count, 1).End(xlUp).Row
sTmp = Sheets("銘柄データ").Cells(i, 1)
driver.Get "https://graph.sbisec.co.jp/sbiscreener/analysis?token=" & TOKEN1 & "&sym=" & sTmp & ".T"
Application.Wait Now + TimeSerial(0, 0, 1)
sTmp2 = 0
Sheets("メイン").Cells(1, 2) = 0
Do Until sTmp2 = 1
If driver.IsElementPresent(myBy.XPath("//*[@id='root']/div/div/div[2]/div[1]/table[1]/tbody/tr/td[2]/span[1]")) Then
Sheets("銘柄データ").Cells(i, 11) = driver.FindElementByXPath("//*[@id='root']/div/div/div[2]/div[1]/table[1]/tbody/tr/td[2]/span[1]").Text
Sheets("銘柄データ").Cells(i, 12) = driver.FindElementByXPath("//*[@id='root']/div/div/div[2]/div[1]/table[2]/tbody/tr[1]/td[2]/div[3]").Text
Sheets("銘柄データ").Cells(i, 13) = driver.FindElementByXPath("//*[@id='root']/div/div/div[2]/div[1]/table[2]/tbody/tr[2]/td[2]/div[3]").Text
Sheets("銘柄データ").Cells(i, 14) = driver.FindElementByXPath("//*[@id='root']/div/div/div[2]/div[1]/table[2]/tbody/tr[3]/td[2]/div[3]").Text
Sheets("銘柄データ").Cells(i, 15) = driver.FindElementByXPath("//*[@id='root']/div/div/div[2]/div[1]/table[2]/tbody/tr[4]/td[2]/div[3]").Text
Sheets("銘柄データ").Cells(i, 16) = driver.FindElementByXPath("//*[@id='root']/div/div/div[2]/div[1]/table[2]/tbody/tr[5]/td[2]/div[3]").Text
sTmp2 = 1
End If
Sheets("メイン").Cells(1, 2) = Sheets("メイン").Cells(1, 2) + 1
If Sheets("メイン").Cells(1, 2) < 6 Then
driver.Refresh
Else
Stop
End If
Loop
Next i
Sheets("メイン").Cells(1, 2) = 0
11:
Err.Number = 0
If Sheets("メイン").Cells(1, 2) < 6 Then
On Error GoTo ERR11
Else
On Error GoTo 0
End If
driver.Get "https://www.sbisec.co.jp/ETGate"
driver.FindElementByXPath("//*[@id='user_input']/input").SendKeys USER_ID_1
driver.FindElementByXPath("//*[@id='password_input']/input").SendKeys USER_PAS1
driver.FindElementByXPath("/html/body/table/tbody/tr[1]/td[2]/div[2]/form/p[2]/input").Click
Application.Wait Now + TimeSerial(0, 0, 2)
driver.Get "https://www.sbisec.co.jp/ETGate/?OutSide=on&_ControlID=WPLETsmR001Control&_DataStoreID=DSWPLETsmR001Control&_PageID=WPLETsmR001Sdtl12&sw_page=WNS001&sw_param1=account&sw_param2=totalreturn&sw_param3=fund&sw_param4=summary&cat1=home&cat2=none&getFlg=on&int_pr1=150313_cmn_gnavi:2_dmenu_04"
driver.Refresh
' driver.ExecuteScript ("window.open();")
' driver.SwitchToNextWindow
' driver.SwitchToPreviousWindow
driver.FindElementByXPath("//*[@id='printArea1']/div/table/tbody/tr[3]/td[1]/a").Click
driver.FindElementByXPath("//*[@id='MAINAREA02_780']/div[5]/p/a[1]").Click
Application.Wait Now + TimeSerial(0, 0, 5)
Call ダウンロードファイル起動(Path, "fundList20*.csv")
If Date - FileDateTime(sTmp) < 14 Then
Workbooks.Open sTmp, ReadOnly:=True
Else
GoTo a
End If
Application.Wait Now + TimeSerial(0, 0, 5)
Windows("運用資産価額管理.xlsm").Activate
Sheets("元(SBI夫)").Select
Cells.Select
Selection.Clear
Workbooks(Dir(sTmp)).Activate
Sheets(Mid(Dir(sTmp), 1, Len(Dir(sTmp)) - 4)).Select
Range(Sheets(Mid(Dir(sTmp), 1, Len(Dir(sTmp)) - 4)).Cells(1, 1), Sheets(Mid(Dir(sTmp), 1, Len(Dir(sTmp)) - 4)).Cells(Sheets(Mid(Dir(sTmp), 1, Len(Dir(sTmp)) - 4)).Cells(Rows.Count, 1).End(xlUp).Row, 12)).Copy
Windows("運用資産価額管理.xlsm").Activate
Sheets("元(SBI夫)").Select
Cells.Select
ActiveSheet.Paste
Application.DisplayAlerts = False
Workbooks(Dir(sTmp)).Activate
ActiveWorkbook.Close SaveChanges:=False
Kill sTmp
Application.DisplayAlerts = True
On Error GoTo 0
'SBI証券(妻)
Sheets("メイン").Cells(1, 2) = 0
12:
Err.Number = 0
If Sheets("メイン").Cells(1, 2) < 6 Then
On Error GoTo ERR12
Else
On Error GoTo 0
End If
driver.Get "https://www.sbisec.co.jp/ETGate"
driver.FindElementByXPath("//*[@id='user_input']/input").SendKeys USER_ID_8
driver.FindElementByXPath("//*[@id='password_input']/input").SendKeys USER_PAS8
driver.FindElementByXPath("/html/body/table/tbody/tr[1]/td[2]/div[2]/form/p[2]/input").Click
Application.Wait Now + TimeSerial(0, 0, 2)
driver.Get "https://www.sbisec.co.jp/ETGate/?OutSide=on&_ControlID=WPLETsmR001Control&_DataStoreID=DSWPLETsmR001Control&_PageID=WPLETsmR001Sdtl12&sw_page=WNS001&sw_param1=account&sw_param2=totalreturn&sw_param3=fund&sw_param4=summary&cat1=home&cat2=none&getFlg=on&int_pr1=150313_cmn_gnavi:2_dmenu_04"
' Application.Wait Now + TimeSerial(0, 0, 2)
' driver.Refresh
' driver.ExecuteScript ("window.open();")
' driver.SwitchToNextWindow
' driver.SwitchToPreviousWindow
driver.FindElementByXPath("/html/body/table/tbody/tr/td[2]/div[4]/div/table/tbody/tr[7]/td[1]/a").Click
driver.FindElementByXPath("//*[@id='MAINAREA02_780']/div[5]/p/a[1]").Click
Application.Wait Now + TimeSerial(0, 0, 5)
Call ダウンロードファイル起動(Path, "fundList20*.csv")
If Date - FileDateTime(sTmp) < 14 Then
Workbooks.Open sTmp, ReadOnly:=True
Else
GoTo a
End If
Windows("運用資産価額管理.xlsm").Activate
Sheets("元(SBI妻)").Select
Cells.Select
Selection.Clear
Workbooks(Dir(sTmp)).Activate
Sheets(Mid(Dir(sTmp), 1, Len(Dir(sTmp)) - 4)).Select
Range(Sheets(Mid(Dir(sTmp), 1, Len(Dir(sTmp)) - 4)).Cells(1, 1), Sheets(Mid(Dir(sTmp), 1, Len(Dir(sTmp)) - 4)).Cells(Sheets(Mid(Dir(sTmp), 1, Len(Dir(sTmp)) - 4)).Cells(Rows.Count, 1).End(xlUp).Row, 12)).Copy
Windows("運用資産価額管理.xlsm").Activate
Sheets("元(SBI妻)").Select
Cells.Select
ActiveSheet.Paste
Application.DisplayAlerts = False
Workbooks(Dir(sTmp)).Activate
ActiveWorkbook.Close SaveChanges:=False
Kill sTmp
Application.DisplayAlerts = True
On Error GoTo 0
'SBI証券(会社)
13:
Err.Number = 0
On Error GoTo ERR13
driver.Get "https://www.sbisec.co.jp/ETGate"
driver.FindElementByXPath("//*[@id='user_input']/input").SendKeys USER_ID_9
driver.FindElementByXPath("//*[@id='password_input']/input").SendKeys USER_PAS9
driver.FindElementByXPath("/html/body/table/tbody/tr[1]/td[2]/div[2]/form/p[2]/input").Click
Application.Wait Now + TimeSerial(0, 0, 2)
driver.Get "https://www.sbisec.co.jp/ETGate/?OutSide=on&_ControlID=WPLETsmR001Control&_DataStoreID=DSWPLETsmR001Control&_PageID=WPLETsmR001Sdtl12&sw_page=WNS001&sw_param1=account&sw_param2=totalreturn&sw_param3=fund&sw_param4=summary&cat1=home&cat2=none&getFlg=on&int_pr1=150313_cmn_gnavi:2_dmenu_04"
' Application.Wait Now + TimeSerial(0, 0, 2)
' driver.Refresh
' driver.ExecuteScript ("window.open();")
' driver.SwitchToNextWindow
' driver.SwitchToPreviousWindow
driver.FindElementByXPath("/html/body/table/tbody/tr/td[2]/div[4]/div/table/tbody/tr[3]/td[1]/a").Click
driver.FindElementByXPath("//*[@id='MAINAREA02_780']/div[5]/p/a[1]").Click
Application.Wait Now + TimeSerial(0, 0, 5)
Call ダウンロードファイル起動(Path, "fundList20*.csv")
If Date - FileDateTime(sTmp) < 14 Then
Workbooks.Open sTmp, ReadOnly:=True
Else
GoTo a
End If
Windows("運用資産価額管理.xlsm").Activate
Sheets("元(SBI会社)").Select
Cells.Select
Selection.Clear
Workbooks(Dir(sTmp)).Activate
Sheets(Mid(Dir(sTmp), 1, Len(Dir(sTmp)) - 4)).Select
Range(Sheets(Mid(Dir(sTmp), 1, Len(Dir(sTmp)) - 4)).Cells(1, 1), Sheets(Mid(Dir(sTmp), 1, Len(Dir(sTmp)) - 4)).Cells(Sheets(Mid(Dir(sTmp), 1, Len(Dir(sTmp)) - 4)).Cells(Rows.Count, 1).End(xlUp).Row, 12)).Copy
Windows("運用資産価額管理.xlsm").Activate
Sheets("元(SBI会社)").Select
Cells.Select
ActiveSheet.Paste
Application.DisplayAlerts = False
Workbooks(Dir(sTmp)).Activate
ActiveWorkbook.Close SaveChanges:=False
Kill sTmp
Application.DisplayAlerts = True
On Error GoTo 0
'SBIベネフィット
driver.Get "https://www.benefit401k.com/customer/RkDCMember/Common/JP_D_BFKLogin.aspx"
driver.FindElementByXPath("//*[@id='txtUserID']").SendKeys USER_ID_2
driver.FindElementByXPath("//*[@id='txtPassword']").SendKeys USER_PAS2
Application.Wait Now + TimeSerial(0, 0, 1)
driver.FindElementByXPath("//*[@id='btnLogin']").Click
driver.Get "https://www.benefit401k.com/customer/RkDCMember/Financial/JP_D_Financial_AssetState.aspx"
Sheets("元(DC)").Range("B5") = Replace(driver.FindElementByXPath("//*[@id='contentArea']/div[2]/div[1]/div[1]/table/tbody/tr/td[2]/table/tbody/tr[2]/td[1]/table/tbody/tr/td").Text, "円", "")
Sheets("元(DC)").Range("C5") = Replace(driver.FindElementByXPath("//*[@id='contentArea']/div[2]/div[1]/div[1]/table/tbody/tr/td[2]/table/tbody/tr[1]/td[1]/table/tbody/tr/td").Text, "円", "")
'楽天証券(夫)
Sheets("メイン").Cells(1, 2) = 0
16:
Err.Number = 0
If Sheets("メイン").Cells(1, 2) < 6 Then
On Error GoTo ERR16
Else
On Error GoTo 0
End If
driver.Get "https://www.rakuten-sec.co.jp/ITS/V_ACT_Login.html"
driver.FindElementByXPath("//*[@id='form-login-id']").SendKeys USER_ID_11
driver.FindElementByXPath("//*[@id='form-login-pass']").SendKeys USER_PAS11
Application.Wait Now + TimeSerial(0, 0, 2)
driver.FindElementByXPath("//*[@id='login-btn']").Click
Application.Wait Now + TimeSerial(0, 0, 3)
'保有商品一覧
driver.ExecuteScript ("memberPageJump('/app/ass_all_possess_lst.do;BV_SessionID=XXXXXXXXXX?eventType=directInit&type=&sub_type=&local=&gmn=S&smn=01&lmn=01&fmn=01');s.lidTrack('pulldown_menu_ass_stk_01');return false;")
driver.ExecuteScript ("window.open();")
driver.SwitchToNextWindow
driver.SwitchToPreviousWindow
Application.Wait Now + TimeSerial(0, 0, 5)
driver.FindElementByXPath("//*[@id='printLink']/table/tbody/tr/td[4]/div/a/img").Click
Application.Wait Now + TimeSerial(0, 0, 3)
Call ダウンロードファイル起動(Path, "assetbalance(all)_20*.csv")
If Date - FileDateTime(sTmp) < 14 Then
Workbooks.Open sTmp, ReadOnly:=True
Else
GoTo a
End If
Windows("運用資産価額管理.xlsm").Activate
Sheets("元(楽天)").Select
Cells.Select
Selection.Clear
Workbooks(Dir(sTmp)).Activate
Cells.Select
Selection.Copy
Windows("運用資産価額管理.xlsm").Activate
Sheets("元(楽天)").Select
Cells.Select
ActiveSheet.Paste
Application.DisplayAlerts = False
Workbooks(Dir(sTmp)).Activate
ActiveWorkbook.Close SaveChanges:=False
Kill sTmp
Application.DisplayAlerts = True
'トータルリターン
driver.ExecuteScript ("memberPageJump('/app/ass_total_all.do;BV_SessionID=5E4779E201C0D6C09EAE744AF23CE32C.42ad2c3e?eventType=init&gmn=S&smn=01&lmn=01&fmn=08#')")
driver.FindElementByXPath("/html/body/div[2]/div/div[1]/div/div/table/tbody/tr/td/form/div[4]/table/tbody/tr/td[2]/a/img").Click
Application.Wait Now + TimeSerial(0, 0, 3)
Call ダウンロードファイル起動(Path, "TotalReturn_all_*.csv")
If Date - FileDateTime(sTmp) < 14 Then
Workbooks.Open sTmp, ReadOnly:=True
Else
GoTo a
End If
Windows("運用資産価額管理.xlsm").Activate
Sheets("元(楽天ﾄｰﾀﾙ)").Select
Cells.Select
Selection.Clear
Workbooks(Dir(sTmp)).Activate
Sheets(Mid(Dir(sTmp), 1, Len(Dir(sTmp)) - 4)).Select
Cells.Select
Selection.Copy
Windows("運用資産価額管理.xlsm").Activate
Sheets("元(楽天ﾄｰﾀﾙ)").Select
Cells.Select
ActiveSheet.Paste
Application.DisplayAlerts = False
Workbooks(Dir(sTmp)).Activate
ActiveWorkbook.Close SaveChanges:=False
Kill sTmp
Application.DisplayAlerts = True
'分配金
driver.ExecuteScript ("memberPageJump('/app/ass_dividend_history.do;BV_SessionID=XXXXXXXXXX?eventType=init&gmn=S&smn=06&lmn=01&fmn=01');return false")
driver.FindElementByXPath("//*[@id='dispTermPanel']/span[5]/img[1]").Click
driver.FindElementByXPath("//*[@id='str-main-inner']/table/tbody/tr/td/form/div[3]/table/tbody/tr[7]/td/span/input").Click
driver.FindElementByXPath("//*[@id='str-main-inner']/table/tbody/tr/td/form/table[3]/tbody/tr/td/table/tbody/tr/td/div/a/img").Click
Application.Wait Now + TimeSerial(0, 0, 3)
Call ダウンロードファイル起動(Path, "dividendlist_*.csv")
If Date - FileDateTime(sTmp) < 14 Then
Workbooks.Open sTmp, ReadOnly:=True
Else
GoTo a
End If
Windows("運用資産価額管理.xlsm").Activate
Sheets("元(楽天(夫)分配金)").Select
Cells.Select
Selection.Clear
Workbooks(Dir(sTmp)).Activate
Sheets(Mid(Dir(sTmp), 1, Len(Dir(sTmp)) - 4)).Select
Cells.Select
Selection.Copy
Windows("運用資産価額管理.xlsm").Activate
Sheets("元(楽天(夫)分配金)").Select
Cells.Select
ActiveSheet.Paste
Application.DisplayAlerts = False
Workbooks(Dir(sTmp)).Activate
ActiveWorkbook.Close SaveChanges:=False
Kill sTmp
Application.DisplayAlerts = True
'楽天証券(子)
driver.Get "https://www.rakuten-sec.co.jp/ITS/V_ACT_Login.html"
driver.FindElementByXPath("//*[@id='form-login-id']").SendKeys USER_ID_10
driver.FindElementByXPath("//*[@id='form-login-pass']").SendKeys USER_PAS10
Application.Wait Now + TimeSerial(0, 0, 2)
driver.FindElementByXPath("//*[@id='login-btn']").Click
Application.Wait Now + TimeSerial(0, 0, 2)
driver.ExecuteScript ("memberPageJump('/app/ass_total_all.do;BV_SessionID=7B5EF13CA1DE13A351E1DDE96964526F.f003e262?eventType=init&gmn=S&smn=01&lmn=01&fmn=08')")
driver.FindElementByXPath("/html/body/div[2]/div/div[1]/div/div/table/tbody/tr/td/form/div[4]/table/tbody/tr/td[2]/a/img").Click
driver.ExecuteScript ("memberPageJump('/app/ass_total_all_jrisa.do;BV_SessionID=XXXXXXXXXX?eventType=init&gmn=H&smn=01&lmn=&fmn=#');return false;")
driver.FindElementByXPath("/html/body/div[2]/div/div[1]/div/div/table/tbody/tr/td/form/div[4]/table/tbody/tr/td[2]/a/img").Click
Application.Wait Now + TimeSerial(0, 0, 3)
Call ダウンロードファイル起動(Path, "TotalReturn_all_*.csv")
If Date - FileDateTime(sTmp) < 14 Then
Workbooks.Open sTmp, ReadOnly:=True
Else
GoTo a
End If
Windows("運用資産価額管理.xlsm").Activate
Sheets("元(楽天(夫)分配金)").Select
Cells.Select
Selection.Clear
Workbooks(Dir(sTmp)).Activate
Cells.Select
Selection.Copy
Windows("運用資産価額管理.xlsm").Activate
Sheets("元(楽天(夫)分配金)").Select
Cells.Select
ActiveSheet.Paste
Application.DisplayAlerts = False
Workbooks(Dir(sTmp)).Activate
ActiveWorkbook.Close SaveChanges:=False
Kill sTmp
Application.DisplayAlerts = True
Call ダウンロードファイル起動(Path, "TotalReturn_all_*.csv")
If Date - FileDateTime(sTmp) < 14 Then
Workbooks.Open sTmp, ReadOnly:=True
Else
GoTo a
End If
Windows("運用資産価額管理.xlsm").Activate
Sheets("元(楽天 子)").Select
Cells.Select
Selection.Clear
Workbooks(Dir(sTmp)).Activate
Cells.Select
Selection.Copy
Windows("運用資産価額管理.xlsm").Activate
Sheets("元(楽天 子)").Select
Cells.Select
ActiveSheet.Paste
Application.DisplayAlerts = False
Workbooks(Dir(sTmp)).Activate
ActiveWorkbook.Close SaveChanges:=False
Kill sTmp
Application.DisplayAlerts = True
'確定拠出年金
If (Weekday(Date) = 1 And (Time > CDate("2:00") Or Time < CDate("8:00"))) Or (getDateWeekNum(Year(Now), Month(Now), vbSaturday, 2) = Date And Time > CDate("21:00")) Or (getDateWeekNum(Year(Now), Month(Now), vbSunday, 2) = Date And Time < CDate("8:00")) Then
Else
driver.Get "https://supportsite.dc.tr.mufg.jp/mmbli/li001/LI001A01/"
driver.FindElementByXPath("/html/body/div[1]/div[2]/div[2]/div/form/div[1]/div[1]/span/input").Clear
driver.FindElementByXPath("/html/body/div[1]/div[2]/div[2]/div/form/div[1]/div[1]/span/input").SendKeys USER_ID_7
driver.FindElementByXPath("/html/body/div[1]/div[2]/div[2]/div/form/div[1]/div[2]/span/input").SendKeys USER_PAS7
driver.FindElementByXPath("/html/body/div[1]/div[2]/div[2]/div/form/div[3]/input").Click
Application.Wait Now + TimeSerial(0, 0, 3)
Sheets("元(DC)").Range("C2") = driver.FindElementByXPath("/html/body/form/div[2]/div[3]/div[2]/div[1]/div/div[1]/table/tbody/tr[2]/td[1]/p/span/em").Text
Sheets("元(DC)").Range("B2") = driver.FindElementByXPath("/html/body/form/div[2]/div[3]/div[2]/div[1]/div/div[1]/table/tbody/tr[2]/td[3]/p/span/em").Text
Sheets("元(DC)").Range("D2") = driver.FindElementByXPath("/html/body/form/div[2]/div[3]/div[2]/div[1]/div/div[1]/table/tbody/tr[2]/td[5]/p/span/em").Text
End If
'持株会
If Time > CDate("6:00") Or Time < CDate("2:00") Then
17:
Err.Number = 0
On Error GoTo ERR17
driver.Get "https://www.e-plan.nomura.co.jp/e-forum/html/index.html"
driver.FindElementByXPath("/html/body/main/div/div/div[2]/ul[2]/li[1]/form/div[1]/div/div[1]/input").SendKeys USER_ID_6
driver.FindElementByXPath("/html/body/main/div/div/div[2]/ul[2]/li[1]/form/div[1]/div/div[2]/div/input").SendKeys USER_PAS6
driver.FindElementByXPath("/html/body/main/div/div/div[2]/ul[2]/li[1]/form/div[2]/p/input").Click
On Error GoTo 0
driver.Get "https://www.e-plan.nomura.co.jp/mocikabu/script/WEAW1101.jsp"
Sheets("持株会").Range("B2") = Mid(driver.FindElementByXPath("/html/body/main/section[1]/div/div/div[1]/div[1]/dl/dd[1]").Text, 1, InStr(driver.FindElementByXPath("/html/body/main/section[1]/div/div/div[1]/div[1]/dl/dd[1]").Text, "株") - 1)
Sheets("持株会").Range("B3") = driver.FindElementByXPath("/html/body/main/section[1]/div/div/div[1]/div[1]/div[1]/dl/dd/span[2]").Text
Sheets("持株会").Range("B4") = driver.FindElementByXPath("/html/body/main/section[1]/div/div/div[1]/div[1]/div[2]/dl/dd/span[1]").Text
Sheets("持株会").Range("B5") = driver.FindElementByXPath("/html/body/main/section[1]/div/div/div[1]/div[1]/div[3]/dl/dd/span[1]").Text
Sheets("持株会").Range("B6") = Mid(driver.FindElementByXPath("/html/body/main/section[1]/div/div/div[2]/div[3]/table/tbody/tr/td").Text, 1, InStr(driver.FindElementByXPath("/html/body/main/section[1]/div/div/div[2]/div[3]/table/tbody/tr/td").Text, "円") - 1)
driver.FindElementByXPath("/html/body/main/section[1]/div/div/div[2]/div[2]/table/thead/tr/th/div").Click
Sheets("持株会").Range("B7") = Mid(driver.FindElementByXPath("/html/body/main/section[1]/div/div/div[2]/div[2]/table/tbody/tr[2]/td").Text, 1, InStr(driver.FindElementByXPath("/html/body/main/section[1]/div/div/div[2]/div[2]/table/tbody/tr[2]/td").Text, "円") - 1)
End If
'野村証券
10:
If Time > CDate("6:00") Or Time < CDate("2:00") Then
On Error GoTo ERR10
driver.Get "https://hometrade.nomura.co.jp/web/rmfCmnCauSysLgiInitAction.do"
driver.FindElementByXPath("/html/body/div[1]/div/main/div/div[4]/form/div[2]/div[1]/div[1]/fieldset/div/ul[1]/li/div[2]/label/input[2]").SendKeys USER_ID_5
driver.FindElementByXPath("/html/body/div[1]/div/main/div/div[4]/form/div[2]/div[1]/div[1]/fieldset/div/ul[3]/li/div[2]/label/input[2]").SendKeys USER_ID_4
driver.FindElementByXPath("/html/body/div[1]/div/main/div/div[4]/form/div[2]/div[1]/div[1]/fieldset/div/div[3]/ul/li[1]/div[2]/label/input[1]").SendKeys USER_PAS4
driver.FindElementByXPath("/html/body/div[1]/div/main/div/div[4]/form/div[2]/div[1]/div[2]/div/button").Click
Application.Wait Now + TimeSerial(0, 0, 1)
driver.Get "https://hometrade.nomura.co.jp/web/rmfAstTrhCpgHisInitAction.do"
driver.FindElementByXPath("/html/body/div[1]/div/main/div/div[3]/form/div[2]/fieldset/dl/dd/div[1]/div[1]/div[1]/label/select/option[3]").Click
driver.FindElementByXPath("/html/body/div[1]/div/main/div/div[3]/form/div[2]/fieldset/dl/dd/div[1]/div[1]/div[2]/label/select/option[1]").Click
driver.FindElementByXPath("/html/body/div[1]/div/main/div/div[3]/form/div[2]/fieldset/dl/dd/div[1]/div[1]/div[3]/label/select/option[1]").Click
driver.FindElementByXPath("/html/body/div[1]/div/main/div/div[3]/form/div[3]/div/button").Click
driver.FindElementByXPath("/html/body/div[1]/div/main/div/div[3]/form/ul[2]/li[2]/a").Click
Application.Wait Now + TimeSerial(0, 0, 3)
Call ダウンロードファイル起動(Path, "New_file*.csv")
If Dir(sTmp) = "" Then
GoTo 10
End If
If Date - FileDateTime(sTmp) < 14 Then
Workbooks.Open sTmp, ReadOnly:=True
Else
GoTo ERR10
End If
Windows("運用資産価額管理.xlsm").Activate
Sheets("元（野村履歴)").Select
EndRow = Sheets("元（野村履歴)").Cells(1048576, 1).End(xlUp).Row
Range(Sheets("元（野村履歴)").Cells(12, 1), Sheets("元（野村履歴)").Cells(EndRow, 2)).Clear
Workbooks(Dir(sTmp)).Activate
Sheets(Mid(Dir(sTmp), 1, Len(Dir(sTmp)) - 4)).Select
Cells.Select
Selection.Copy
Windows("運用資産価額管理.xlsm").Activate
Sheets("元（野村履歴)").Select
Cells.Select
ActiveSheet.Paste
Application.DisplayAlerts = False
Workbooks(Dir(sTmp)).Activate
ActiveWorkbook.Close SaveChanges:=False
Kill sTmp
Application.DisplayAlerts = True
driver.Get "https://hometrade.nomura.co.jp/web/rmfAstAdpAdpRealAdpAction.do?kzKkKbn=1"
driver.FindElementByXPath("/html/body/div[1]/div/main/div/div[4]/ul[1]/li[2]/a").Click
Application.Wait Now + TimeSerial(0, 0, 3)
Call ダウンロードファイル起動(Path, "New_file*.csv")
If Dir(sTmp) = "" Then
GoTo 10
End If
If Date - FileDateTime(sTmp) < 14 Then
Workbooks.Open sTmp, ReadOnly:=True
Else
GoTo ERR10
End If
Windows("運用資産価額管理.xlsm").Activate
Sheets("元(野村預り）").Select
Cells.Select
Selection.Clear
Workbooks(Dir(sTmp)).Activate
Sheets(Mid(Dir(sTmp), 1, Len(Dir(sTmp)) - 4)).Select
Cells.Select
Selection.Copy
Windows("運用資産価額管理.xlsm").Activate
Sheets("元(野村預り）").Select
Cells.Select
ActiveSheet.Paste
Application.DisplayAlerts = False
Workbooks(Dir(sTmp)).Activate
ActiveWorkbook.Close SaveChanges:=False
Kill sTmp
Application.DisplayAlerts = True
End If
'シラーPER
driver.Get "https://www.multpl.com/shiller-pe"
sTmp = Mid(driver.FindElementByXPath("/html/body/div[2]/div[2]/div[2]/div[2]/div[3]/div[1]").Text, 27, 5) * 1
EndRow = Sheets("株比率").Cells(1048576, 10).End(xlUp).Row
For i = 4 To EndRow
If sTmp > (Sheets("株比率").Cells(i - 1, 10) + Sheets("株比率").Cells(i, 10)) / 2 And sTmp <= (Sheets("株比率").Cells(i, 10) + Sheets("株比率").Cells(i + 1, 10)) / 2 Then
Sheets("株比率").Cells(i, 1) = 1
Else
Sheets("株比率").Cells(i, 1) = ""
End If
Next i
Set driver = Nothing
On Error GoTo 0
EndRow = Sheets("資産推移").Cells(1048576, 1).End(xlUp).Row + 1
Sheets("資産推移").Cells(EndRow, 1) = Date
Sheets("資産推移").Cells(EndRow, 2) = Sheets("メイン").Cells(50, 12)
Sheets("資産推移").Cells(EndRow, 3) = Sheets("メイン").Cells(50, 12) - Sheets("メイン").Cells(50, 17)
Sheets("資産推移").Cells(EndRow, 4) = Sheets("メイン").Cells(50, 17)
Sheets("資産推移").Cells(EndRow, 5) = Sheets("メイン").Cells(50, 18)
Sheets("資産推移").Cells(EndRow, 7) = Sheets("メイン").Cells(54, 2)
Sheets("資産推移").Cells(EndRow, 8) = Sheets("15年").Cells(31, 3) * Sheets("メイン").Cells(54, 2)
Sheets("資産推移").Cells(EndRow, 9) = Sheets("15年").Cells(70, 3) * Sheets("メイン").Cells(54, 2)
Sheets("メイン").Select
Sheets("メイン").Cells(1, 1) = Now
NumLockOn
Application.ScreenUpdating = True
Sheets("メイン").Cells(1, 1).Select
' Cells.Copy
' ActiveWorkbook.Close SaveChanges:=False
'IE(InternetExplorer)を閉じる
' objIE.Quit
'オブジェクト解除
' Set objIE = Nothing
Exit Sub
ERR10:
Resume 10
ERR11:
Windows("運用資産価額管理.xlsm").Activate
Sheets("メイン").Cells(1, 2) = Sheets("メイン").Cells(1, 2) + 1
Resume 11
ERR12:
Windows("運用資産価額管理.xlsm").Activate
Sheets("メイン").Cells(1, 2) = Sheets("メイン").Cells(1, 2) + 1
Resume 12
ERR13:
Resume 13
ERR14:
Resume 14
ERR15:
Resume 15
ERR16:
Windows("運用資産価額管理.xlsm").Activate
Sheets("メイン").Cells(1, 2) = Sheets("メイン").Cells(1, 2) + 1
Set driver = Nothing
driver.AddArgument ("user-data-dir=" & PROFILE_PATH)
driver.Start "chrome"
Application.Wait Now + TimeSerial(0, 0, 2)
Resume 16
ERR17:
Resume 17
a:
MsgBox "エラー", vbExclamation, "エラー"
End Sub
関連のコードは以下の通り
' API declarations:
Private Declare PtrSafe Sub keybd_event Lib "user32" _
(ByVal bVk As Byte, _
ByVal bScan As Byte, _
ByVal dwFlags As Long, ByVal dwExtraInfo As Long)
Private Declare PtrSafe Function GetKeyboardState Lib "user32" _
(pbKeyState As Byte) As Long
' Constant declarations:
Const VK_NUMLOCK = &H90
Const VK_SCROLL = &H91
Const VK_CAPITAL = &H14
Const KEYEVENTF_EXTENDEDKEY = &H1
Const KEYEVENTF_KEYUP = &H2
Function NumLockOn()
Dim NumLockState As Boolean
Dim keys(0 To 255) As Byte
GetKeyboardState keys(0)
NumLockState = keys(VK_NUMLOCK)
'オフであれば強制的にオンに切り替えます。
If NumLockState <> True Then
'Simulate Key Press
keybd_event VK_NUMLOCK, &H45, KEYEVENTF_EXTENDEDKEY Or 0, 0
'Simulate Key Release
keybd_event VK_NUMLOCK, &H45, KEYEVENTF_EXTENDEDKEY Or KEYEVENTF_KEYUP, 0
End If
End Function
Function getDateWeekNum(myYear As Integer, myMonth As Integer, _
myWeekday As VbDayOfWeek, weekNum As Integer) As Date
Dim firstWeekday As Integer ' 指定した年月の1日の曜日番号
Dim Sabun As Integer ' 曜日番号の差分
' 指定した年月の1日の曜日番号求める
firstWeekday = Weekday(DateSerial(myYear, myMonth, 1))
'1日の曜日番号と求めたい曜日番号の差分を求める
Sabun = myWeekday - firstWeekday
If myWeekday < firstWeekday Then
Sabun = Sabun + 7
End If
' 目的の日付データを求めて返す
getDateWeekNum = DateSerial(myYear, myMonth, 1 + Sabun + 7 * (weekNum - 1))
End Function
Sub ダウンロードファイル起動(PATH2 As String, Target As String)
Dim FSO As Object, Folder As Variant, File As Variant
On Error Resume Next
Set FSO = CreateObject("Scripting.FileSystemObject")
' For Each Folder In FSO.GetFolder(Path).SubFolders
' Call ダウンロードファイル(Folder.Path, Target)
' Next Folder
For Each File In FSO.GetFolder(PATH2).Files
If File.Name Like Target Then
If sTmp = "" Then
sTmp = File.PATH
Else
If FileDateTime(sTmp) < FileDateTime(File) Then
sTmp = File.PATH
End If
End If
End If
Next File
End Sub
マクロコード個別解説
SBI証券のスクレイピング
本項では、SBI証券のスクレイピングコードを抜粋して紹介します。
二つの機能があります。
・株価を取得する機能
・SBI証券による企業スコア評価を取得する機能
・トータルリターンのcsvベタデータを取得する機能
最後の機能について、たまに、原因不明の動作エラーがでるため、無限リトライする機能をつけています。
'SBI証券 夫
driver.Get "https://www.sbisec.co.jp/ETGate"
driver.FindElementByXPath("//*[@id='user_input']/input").SendKeys USER_ID_1
driver.FindElementByXPath("//*[@id='password_input']/input").SendKeys USER_PAS1
Application.Wait Now + TimeSerial(0, 0, 2)
driver.FindElementByXPath("/html/body/table/tbody/tr[1]/td[2]/div[2]/form/p[2]/input").Click
Application.Wait Now + TimeSerial(0, 0, 1)
driver.Get "https://site2.sbisec.co.jp/ETGate/?_ControlID=WPLETsiR001Control&_DataStoreID=DSWPLETsiR001Control&_PageID=WPLETsiR001Idtl70&_ActionID=DefaultAID&s_rkbn=2&s_btype=&i_stock_sec=8306&i_dom_flg=1&i_exchange_code=JPN&i_output_type=6&exchange_code=TKY&stock_sec_code_mul=8306&ref_from=1&ref_to=20&wstm4130_sort_id=&wstm4130_sort_kbn=&qr_keyword=1&qr_suggest=1&qr_sort=1"
sTmp = ""
Dim LinkURL As Variant 'As HTMLAnchorElement
For Each LinkURL In driver.FindElementsByTag("iframe").Attribute("src")
If InStr(LinkURL, "token") <> 0 Then
sTmp = Replace(LinkURL, "https://graph.sbisec.co.jp/sbiscreener/analysis?token=", "")
sTmp = Replace(sTmp, "&sym=8306.T", "")
TOKEN1 = sTmp
End If
Next LinkURL
sTmp = ""
For i = 2 To Sheets("銘柄データ").Cells(Rows.Count, 1).End(xlUp).Row
sTmp = Sheets("銘柄データ").Cells(i, 1)
driver.Get "https://graph.sbisec.co.jp/sbiscreener/analysis?token=" & TOKEN1 & "&sym=" & sTmp & ".T"
Application.Wait Now + TimeSerial(0, 0, 1)
sTmp2 = 0
Sheets("メイン").Cells(1, 2) = 0
Do Until sTmp2 = 1
If driver.IsElementPresent(myBy.XPath("//*[@id='root']/div/div/div[2]/div[1]/table[1]/tbody/tr/td[2]/span[1]")) Then
Sheets("銘柄データ").Cells(i, 11) = driver.FindElementByXPath("//*[@id='root']/div/div/div[2]/div[1]/table[1]/tbody/tr/td[2]/span[1]").Text
Sheets("銘柄データ").Cells(i, 12) = driver.FindElementByXPath("//*[@id='root']/div/div/div[2]/div[1]/table[2]/tbody/tr[1]/td[2]/div[3]").Text
Sheets("銘柄データ").Cells(i, 13) = driver.FindElementByXPath("//*[@id='root']/div/div/div[2]/div[1]/table[2]/tbody/tr[2]/td[2]/div[3]").Text
Sheets("銘柄データ").Cells(i, 14) = driver.FindElementByXPath("//*[@id='root']/div/div/div[2]/div[1]/table[2]/tbody/tr[3]/td[2]/div[3]").Text
Sheets("銘柄データ").Cells(i, 15) = driver.FindElementByXPath("//*[@id='root']/div/div/div[2]/div[1]/table[2]/tbody/tr[4]/td[2]/div[3]").Text
Sheets("銘柄データ").Cells(i, 16) = driver.FindElementByXPath("//*[@id='root']/div/div/div[2]/div[1]/table[2]/tbody/tr[5]/td[2]/div[3]").Text
sTmp2 = 1
End If
Sheets("メイン").Cells(1, 2) = Sheets("メイン").Cells(1, 2) + 1
If Sheets("メイン").Cells(1, 2) < 6 Then
driver.Refresh
Else
Stop
End If
Loop
Next i
Sheets("メイン").Cells(1, 2) = 0
11:
Err.Number = 0
If Sheets("メイン").Cells(1, 2) < 6 Then
On Error GoTo ERR11
Else
On Error GoTo 0
End If
driver.Get "https://www.sbisec.co.jp/ETGate"
driver.FindElementByXPath("//*[@id='user_input']/input").SendKeys USER_ID_1
driver.FindElementByXPath("//*[@id='password_input']/input").SendKeys USER_PAS1
driver.FindElementByXPath("/html/body/table/tbody/tr[1]/td[2]/div[2]/form/p[2]/input").Click
Application.Wait Now + TimeSerial(0, 0, 2)
driver.Get "https://www.sbisec.co.jp/ETGate/?OutSide=on&_ControlID=WPLETsmR001Control&_DataStoreID=DSWPLETsmR001Control&_PageID=WPLETsmR001Sdtl12&sw_page=WNS001&sw_param1=account&sw_param2=totalreturn&sw_param3=fund&sw_param4=summary&cat1=home&cat2=none&getFlg=on&int_pr1=150313_cmn_gnavi:2_dmenu_04"
driver.Refresh
' driver.ExecuteScript ("window.open();")
' driver.SwitchToNextWindow
' driver.SwitchToPreviousWindow
driver.FindElementByXPath("//*[@id='printArea1']/div/table/tbody/tr[3]/td[1]/a").Click
driver.FindElementByXPath("//*[@id='MAINAREA02_780']/div[5]/p/a[1]").Click
Application.Wait Now + TimeSerial(0, 0, 5)
Call ダウンロードファイル起動(Path, "fundList20*.csv")
If Date - FileDateTime(sTmp) < 14 Then
Workbooks.Open sTmp, ReadOnly:=True
Else
GoTo a
End If
Application.Wait Now + TimeSerial(0, 0, 5)
Windows("運用資産価額管理.xlsm").Activate
Sheets("元(SBI夫)").Select
Cells.Select
Selection.Clear
Workbooks(Dir(sTmp)).Activate
Sheets(Mid(Dir(sTmp), 1, Len(Dir(sTmp)) - 4)).Select
Range(Sheets(Mid(Dir(sTmp), 1, Len(Dir(sTmp)) - 4)).Cells(1, 1), Sheets(Mid(Dir(sTmp), 1, Len(Dir(sTmp)) - 4)).Cells(Sheets(Mid(Dir(sTmp), 1, Len(Dir(sTmp)) - 4)).Cells(Rows.Count, 1).End(xlUp).Row, 12)).Copy
Windows("運用資産価額管理.xlsm").Activate
Sheets("元(SBI夫)").Select
Cells.Select
ActiveSheet.Paste
Application.DisplayAlerts = False
Workbooks(Dir(sTmp)).Activate
ActiveWorkbook.Close SaveChanges:=False
Kill sTmp
Application.DisplayAlerts = True
On Error GoTo 0楽天証券のスクレイピング
本項では、 楽天証券のスクレイピングコードを抜粋して紹介します。
以下の機能があります。
- 投資信託のトータルリターンを取得する機能。
- ジュニアNISAの投資信託のトータルリターンを取得する機能。
- 保有商品一覧を取得する機能
- 分配金の履歴を取得する機能
'楽天証券(夫)
Sheets("メイン").Cells(1, 2) = 0
16:
Err.Number = 0
If Sheets("メイン").Cells(1, 2) < 6 Then
On Error GoTo ERR16
Else
On Error GoTo 0
End If
driver.Get "https://www.rakuten-sec.co.jp/ITS/V_ACT_Login.html"
driver.FindElementByXPath("//*[@id='form-login-id']").SendKeys USER_ID_11
driver.FindElementByXPath("//*[@id='form-login-pass']").SendKeys USER_PAS11
Application.Wait Now + TimeSerial(0, 0, 2)
driver.FindElementByXPath("//*[@id='login-btn']").Click
Application.Wait Now + TimeSerial(0, 0, 3)
'保有商品一覧
driver.ExecuteScript ("memberPageJump('/app/ass_all_possess_lst.do;BV_SessionID=XXXXXXXXXX?eventType=directInit&type=&sub_type=&local=&gmn=S&smn=01&lmn=01&fmn=01');s.lidTrack('pulldown_menu_ass_stk_01');return false;")
driver.ExecuteScript ("window.open();")
driver.SwitchToNextWindow
driver.SwitchToPreviousWindow
Application.Wait Now + TimeSerial(0, 0, 5)
driver.FindElementByXPath("//*[@id='printLink']/table/tbody/tr/td[4]/div/a/img").Click
Application.Wait Now + TimeSerial(0, 0, 3)
Call ダウンロードファイル起動(Path, "assetbalance(all)_20*.csv")
If Date - FileDateTime(sTmp) < 14 Then
Workbooks.Open sTmp, ReadOnly:=True
Else
GoTo a
End If
Windows("運用資産価額管理.xlsm").Activate
Sheets("元(楽天)").Select
Cells.Select
Selection.Clear
Workbooks(Dir(sTmp)).Activate
Cells.Select
Selection.Copy
Windows("運用資産価額管理.xlsm").Activate
Sheets("元(楽天)").Select
Cells.Select
ActiveSheet.Paste
Application.DisplayAlerts = False
Workbooks(Dir(sTmp)).Activate
ActiveWorkbook.Close SaveChanges:=False
Kill sTmp
Application.DisplayAlerts = True
'トータルリターン
driver.ExecuteScript ("memberPageJump('/app/ass_total_all.do;BV_SessionID=5E4779E201C0D6C09EAE744AF23CE32C.42ad2c3e?eventType=init&gmn=S&smn=01&lmn=01&fmn=08#')")
driver.FindElementByXPath("/html/body/div[2]/div/div[1]/div/div/table/tbody/tr/td/form/div[4]/table/tbody/tr/td[2]/a/img").Click
Application.Wait Now + TimeSerial(0, 0, 3)
Call ダウンロードファイル起動(Path, "TotalReturn_all_*.csv")
If Date - FileDateTime(sTmp) < 14 Then
Workbooks.Open sTmp, ReadOnly:=True
Else
GoTo a
End If
Windows("運用資産価額管理.xlsm").Activate
Sheets("元(楽天ﾄｰﾀﾙ)").Select
Cells.Select
Selection.Clear
Workbooks(Dir(sTmp)).Activate
Sheets(Mid(Dir(sTmp), 1, Len(Dir(sTmp)) - 4)).Select
Cells.Select
Selection.Copy
Windows("運用資産価額管理.xlsm").Activate
Sheets("元(楽天ﾄｰﾀﾙ)").Select
Cells.Select
ActiveSheet.Paste
Application.DisplayAlerts = False
Workbooks(Dir(sTmp)).Activate
ActiveWorkbook.Close SaveChanges:=False
Kill sTmp
Application.DisplayAlerts = True
'分配金
driver.ExecuteScript ("memberPageJump('/app/ass_dividend_history.do;BV_SessionID=XXXXXXXXXX?eventType=init&gmn=S&smn=06&lmn=01&fmn=01');return false")
driver.FindElementByXPath("//*[@id='dispTermPanel']/span[5]/img[1]").Click
driver.FindElementByXPath("//*[@id='str-main-inner']/table/tbody/tr/td/form/div[3]/table/tbody/tr[7]/td/span/input").Click
driver.FindElementByXPath("//*[@id='str-main-inner']/table/tbody/tr/td/form/table[3]/tbody/tr/td/table/tbody/tr/td/div/a/img").Click
Application.Wait Now + TimeSerial(0, 0, 3)
Call ダウンロードファイル起動(Path, "dividendlist_*.csv")
If Date - FileDateTime(sTmp) < 14 Then
Workbooks.Open sTmp, ReadOnly:=True
Else
GoTo a
End If
Windows("運用資産価額管理.xlsm").Activate
Sheets("元(楽天(夫)分配金)").Select
Cells.Select
Selection.Clear
Workbooks(Dir(sTmp)).Activate
Sheets(Mid(Dir(sTmp), 1, Len(Dir(sTmp)) - 4)).Select
Cells.Select
Selection.Copy
Windows("運用資産価額管理.xlsm").Activate
Sheets("元(楽天(夫)分配金)").Select
Cells.Select
ActiveSheet.Paste
Application.DisplayAlerts = False
Workbooks(Dir(sTmp)).Activate
ActiveWorkbook.Close SaveChanges:=False
Kill sTmp
Application.DisplayAlerts = True
'楽天証券(子)
driver.Get "https://www.rakuten-sec.co.jp/ITS/V_ACT_Login.html"
driver.FindElementByXPath("//*[@id='form-login-id']").SendKeys USER_ID_10
driver.FindElementByXPath("//*[@id='form-login-pass']").SendKeys USER_PAS10
Application.Wait Now + TimeSerial(0, 0, 2)
driver.FindElementByXPath("//*[@id='login-btn']").Click
Application.Wait Now + TimeSerial(0, 0, 2)
driver.ExecuteScript ("memberPageJump('/app/ass_total_all.do;BV_SessionID=7B5EF13CA1DE13A351E1DDE96964526F.f003e262?eventType=init&gmn=S&smn=01&lmn=01&fmn=08')")
driver.FindElementByXPath("/html/body/div[2]/div/div[1]/div/div/table/tbody/tr/td/form/div[4]/table/tbody/tr/td[2]/a/img").Click
driver.ExecuteScript ("memberPageJump('/app/ass_total_all_jrisa.do;BV_SessionID=XXXXXXXXXX?eventType=init&gmn=H&smn=01&lmn=&fmn=#');return false;")
driver.FindElementByXPath("/html/body/div[2]/div/div[1]/div/div/table/tbody/tr/td/form/div[4]/table/tbody/tr/td[2]/a/img").Click
Application.Wait Now + TimeSerial(0, 0, 3)
Call ダウンロードファイル起動(Path, "TotalReturn_all_*.csv")
If Date - FileDateTime(sTmp) < 14 Then
Workbooks.Open sTmp, ReadOnly:=True
Else
GoTo a
End If
Windows("運用資産価額管理.xlsm").Activate
Sheets("元(楽天(夫)分配金)").Select
Cells.Select
Selection.Clear
Workbooks(Dir(sTmp)).Activate
Cells.Select
Selection.Copy
Windows("運用資産価額管理.xlsm").Activate
Sheets("元(楽天(夫)分配金)").Select
Cells.Select
ActiveSheet.Paste
Application.DisplayAlerts = False
Workbooks(Dir(sTmp)).Activate
ActiveWorkbook.Close SaveChanges:=False
Kill sTmp
Application.DisplayAlerts = True
Call ダウンロードファイル起動(Path, "TotalReturn_all_*.csv")
If Date - FileDateTime(sTmp) < 14 Then
Workbooks.Open sTmp, ReadOnly:=True
Else
GoTo a
End If
Windows("運用資産価額管理.xlsm").Activate
Sheets("元(楽天 子)").Select
Cells.Select
Selection.Clear
Workbooks(Dir(sTmp)).Activate
Cells.Select
Selection.Copy
Windows("運用資産価額管理.xlsm").Activate
Sheets("元(楽天 子)").Select
Cells.Select
ActiveSheet.Paste
Application.DisplayAlerts = False
Workbooks(Dir(sTmp)).Activate
ActiveWorkbook.Close SaveChanges:=False
Kill sTmp
Application.DisplayAlerts = TrueSBIベネフィット・システムズのスクレイピング
本項では、 SBIベネフィット・システムズ のスクレイピングコードを抜粋して紹介します。
idecoの管理を委託しているサイトです。
私は１銘柄の集中投資をしているため、運用掛金と、現在値、それぞれの総額のみを取得する仕様としています。
'SBIベネフィット
driver.Get "https://www.benefit401k.com/customer/RkDCMember/Common/JP_D_BFKLogin.aspx"
driver.FindElementByXPath("//*[@id='txtUserID']").SendKeys USER_ID_2
driver.FindElementByXPath("//*[@id='txtPassword']").SendKeys USER_PAS2
Application.Wait Now + TimeSerial(0, 0, 1)
driver.FindElementByXPath("//*[@id='btnLogin']").Click
driver.Get "https://www.benefit401k.com/customer/RkDCMember/Financial/JP_D_Financial_AssetState.aspx"
Sheets("元(DC)").Range("B5") = Replace(driver.FindElementByXPath("//*[@id='contentArea']/div[2]/div[1]/div[1]/table/tbody/tr/td[2]/table/tbody/tr[2]/td[1]/table/tbody/tr/td").Text, "円", "")
Sheets("元(DC)").Range("C5") = Replace(driver.FindElementByXPath("//*[@id='contentArea']/div[2]/div[1]/div[1]/table/tbody/tr/td[2]/table/tbody/tr[1]/td[1]/table/tbody/tr/td").Text, "円", "")
SBIネオモバイル証券のスクレイピング
本項ではSBIネオモバイル証券のスクレイピング について抜粋して紹介します。以下機能があります。
当年度の譲渡益税徴収額合計
当年度の 損益額合計
当年度の 配当所得税徴収額合計
当年度の 配当金額合計
保有銘柄の銘柄情報を取得。
預かり現金額を取得
コードは以下の通りです。
'ネオモバイル
15:
On Error GoTo ERR15
driver.Get "https://trade.sbineomobile.co.jp/login"
driver.Window.Maximize
driver.FindElementByXPath("/html/body/div[3]/div/div/div[1]/div[2]/div[1]/main/form/section[1]/div/div[1]/input").SendKeys USER_ID_3
driver.FindElementByXPath("/html/body/div[3]/div/div/div[1]/div[2]/div[1]/main/form/section[1]/div/div[2]/input").SendKeys USER_PAS3
driver.FindElementByXPath("/html/body/div[3]/div/div/div[1]/div[2]/div[1]/main/form/section[2]/button").Click
driver.Get "https://trade.sbineomobile.co.jp/trade/domestic/tradeHistory/capitalGainTaxDetail"
If driver.FindElementByXPath("/html/body/div[3]/div/div/div[1]/div[2]/div[1]/main/section[3]/section/div[1]/table/tbody/tr[1]/td/span").Text = "--" Then
Sheets("個別履歴").Range("L2") = 0
Else
Sheets("個別履歴").Range("L2") = driver.FindElementByXPath("/html/body/div[3]/div/div/div[1]/div[2]/div[1]/main/section[3]/section/div[1]/table/tbody/tr[1]/td/span").Text * -1
End If
On Error GoTo 0
If driver.FindElementByXPath("/html/body/div[3]/div/div/div[1]/div[2]/div[1]/main/section[3]/section/div[1]/table/tbody/tr[2]/td/span").Text = "--" Then
Sheets("個別履歴").Range("L3") = 0
Else
Sheets("個別履歴").Range("L3") = driver.FindElementByXPath("/html/body/div[3]/div/div/div[1]/div[2]/div[1]/main/section[3]/section/div[1]/table/tbody/tr[2]/td/span").Text
End If
If driver.FindElementByXPath("/html/body/div[3]/div/div/div[1]/div[2]/div[1]/main/section[3]/section/div[2]/table/tbody/tr[1]/td/span").Text = "--" Then
Sheets("個別履歴").Range("L4") = 0
Else
Sheets("個別履歴").Range("L4") = driver.FindElementByXPath("/html/body/div[3]/div/div/div[1]/div[2]/div[1]/main/section[3]/section/div[2]/table/tbody/tr[1]/td/span").Text * -1
End If
If driver.FindElementByXPath("/html/body/div[3]/div/div/div[1]/div[2]/div[1]/main/section[3]/section/div[2]/table/tbody/tr[2]/td/span").Text = "--" Then
Sheets("個別履歴").Range("L5") = 0
Else
Sheets("個別履歴").Range("L5") = driver.FindElementByXPath("/html/body/div[3]/div/div/div[1]/div[2]/div[1]/main/section[3]/section/div[2]/table/tbody/tr[2]/td/span").Text
End If
driver.Get "https://trade.sbineomobile.co.jp/account/portfolio"
For i = 1 To 20
On Error GoTo ERR14
driver.FindElementByXPath("/html/body/div[3]/div/div/div[1]/div[2]/div[1]/main/div[3]/section/button").Click
Application.Wait Now + TimeSerial(0, 0, 2)
Next i
14:
On Error GoTo 0
Sheets("ﾈｵﾓﾊﾞ一覧").Select
Columns("A:D").Select
Selection.Clear
Columns(1).NumberFormatLocal = "@"
Sheets("ﾈｵﾓﾊﾞ一覧").Cells(2, 3) = driver.FindElementByXPath("/html/body/div[3]/div/div/div[1]/div[2]/div[1]/main/div[1]/section[4]/ul/li[1]/p[2]/span").Text
Application.Calculation = xlCalculationManual
For i = 1 To driver.FindElementsByClass("name").Count
Sheets("ﾈｵﾓﾊﾞ一覧").Cells(i + 2, 1) = Format(driver.FindElementByXPath("/html/body/div[3]/div/div/div[1]/div[2]/div[1]/main/div[1]/section[" & i + 4 & "]/div/div[1]/div[2]/p").Text, "@")
Sheets("ﾈｵﾓﾊﾞ一覧").Cells(i + 2, 2) = Format(driver.FindElementByXPath("/html/body/div[3]/div/div/div[1]/div[2]/div[1]/main/div[1]/section[" & i + 4 & "]/div/div[1]/div[2]/h4/a").Text, "@")
If driver.FindElementByXPath("/html/body/div[3]/div/div/div[1]/div[2]/div[1]/main/div[1]/section[" & i + 4 & "]/div/div[2]/div[1]/span").Text = "--" Then
Sheets("ﾈｵﾓﾊﾞ一覧").Cells(i + 3, 3) = 0
Else
Sheets("ﾈｵﾓﾊﾞ一覧").Cells(i + 2, 3) = driver.FindElementByXPath("/html/body/div[3]/div/div/div[1]/div[2]/div[1]/main/div[1]/section[" & i + 4 & "]/div/div[2]/div[1]/span").Text
End If
Sheets("ﾈｵﾓﾊﾞ一覧").Cells(i + 2, 4) = driver.FindElementByXPath("/html/body/div[3]/div/div/div[1]/div[2]/div[1]/main/div[1]/section[" & i + 4 & "]/div/div[2]/div[2]/span").Text
Next i
Application.Calculation = xlCalculationAutomatic
ActiveWorkbook.Worksheets("ﾈｵﾓﾊﾞｻﾏﾘ").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("ﾈｵﾓﾊﾞｻﾏﾘ").Sort.SortFields.Add Key:=Range("L1"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("ﾈｵﾓﾊﾞｻﾏﾘ").Sort
.SetRange Range(Cells(1, 1), Cells(18, 20))
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
If ActiveWorkbook.Worksheets("銘柄データ").FilterMode = True Then
ActiveWorkbook.Worksheets("銘柄データ").ShowAllData
Else
ActiveWorkbook.Worksheets("銘柄データ").Rows(1).AutoFilter
End If
ActiveWorkbook.Worksheets("銘柄データ").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("銘柄データ").Sort.SortFields.Add Key:=Range("I1"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
ActiveWorkbook.Worksheets("銘柄データ").Sort.SortFields.Add Key:=Range("E1"), _
SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("銘柄データ").Sort
.SetRange Range(Cells(1, 1), Cells(Sheets("銘柄データ").Cells(Rows.Count, 1).End(xlUp).Row, 300))
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveWorkbook.Worksheets("銘柄データ").Rows(1).AutoFilter Field:=8, Criteria1:="<>#VALUE!", Operator:=xlFilterValues
野村証券のスクレイピング
本項では野村證券のスクレイピング について抜粋して紹介します。 以下機能があります。
株式の現在預かり資産を取得。
特定口座の譲渡益履歴を取得。
コードは以下の通りです。
'野村証券
10:
If Time > CDate("6:00") Or Time < CDate("2:00") Then
On Error GoTo ERR10
driver.Get "https://hometrade.nomura.co.jp/web/rmfCmnCauSysLgiInitAction.do"
driver.FindElementByXPath("/html/body/div[1]/div/main/div/div[4]/form/div[2]/div[1]/div[1]/fieldset/div/ul[1]/li/div[2]/label/input[2]").SendKeys USER_ID_5
driver.FindElementByXPath("/html/body/div[1]/div/main/div/div[4]/form/div[2]/div[1]/div[1]/fieldset/div/ul[3]/li/div[2]/label/input[2]").SendKeys USER_ID_4
driver.FindElementByXPath("/html/body/div[1]/div/main/div/div[4]/form/div[2]/div[1]/div[1]/fieldset/div/div[3]/ul/li[1]/div[2]/label/input[1]").SendKeys USER_PAS4
driver.FindElementByXPath("/html/body/div[1]/div/main/div/div[4]/form/div[2]/div[1]/div[2]/div/button").Click
Application.Wait Now + TimeSerial(0, 0, 1)
driver.Get "https://hometrade.nomura.co.jp/web/rmfAstTrhCpgHisInitAction.do"
driver.FindElementByXPath("/html/body/div[1]/div/main/div/div[3]/form/div[2]/fieldset/dl/dd/div[1]/div[1]/div[1]/label/select/option[3]").Click
driver.FindElementByXPath("/html/body/div[1]/div/main/div/div[3]/form/div[2]/fieldset/dl/dd/div[1]/div[1]/div[2]/label/select/option[1]").Click
driver.FindElementByXPath("/html/body/div[1]/div/main/div/div[3]/form/div[2]/fieldset/dl/dd/div[1]/div[1]/div[3]/label/select/option[1]").Click
driver.FindElementByXPath("/html/body/div[1]/div/main/div/div[3]/form/div[3]/div/button").Click
driver.FindElementByXPath("/html/body/div[1]/div/main/div/div[3]/form/ul[2]/li[2]/a").Click
Application.Wait Now + TimeSerial(0, 0, 3)
Call ダウンロードファイル起動(Path, "New_file*.csv")
If Dir(sTmp) = "" Then
GoTo 10
End If
If Date - FileDateTime(sTmp) < 14 Then
Workbooks.Open sTmp, ReadOnly:=True
Else
GoTo ERR10
End If
Windows("運用資産価額管理.xlsm").Activate
Sheets("元（野村履歴)").Select
EndRow = Sheets("元（野村履歴)").Cells(1048576, 1).End(xlUp).Row
Range(Sheets("元（野村履歴)").Cells(12, 1), Sheets("元（野村履歴)").Cells(EndRow, 2)).Clear
Workbooks(Dir(sTmp)).Activate
Sheets(Mid(Dir(sTmp), 1, Len(Dir(sTmp)) - 4)).Select
Cells.Select
Selection.Copy
Windows("運用資産価額管理.xlsm").Activate
Sheets("元（野村履歴)").Select
Cells.Select
ActiveSheet.Paste
Application.DisplayAlerts = False
Workbooks(Dir(sTmp)).Activate
ActiveWorkbook.Close SaveChanges:=False
Kill sTmp
Application.DisplayAlerts = True
driver.Get "https://hometrade.nomura.co.jp/web/rmfAstAdpAdpRealAdpAction.do?kzKkKbn=1"
driver.FindElementByXPath("/html/body/div[1]/div/main/div/div[4]/ul[1]/li[2]/a").Click
Application.Wait Now + TimeSerial(0, 0, 3)
Call ダウンロードファイル起動(Path, "New_file*.csv")
If Dir(sTmp) = "" Then
GoTo 10
End If
If Date - FileDateTime(sTmp) < 14 Then
Workbooks.Open sTmp, ReadOnly:=True
Else
GoTo ERR10
End If
Windows("運用資産価額管理.xlsm").Activate
Sheets("元(野村預り）").Select
Cells.Select
Selection.Clear
Workbooks(Dir(sTmp)).Activate
Sheets(Mid(Dir(sTmp), 1, Len(Dir(sTmp)) - 4)).Select
Cells.Select
Selection.Copy
Windows("運用資産価額管理.xlsm").Activate
Sheets("元(野村預り）").Select
Cells.Select
ActiveSheet.Paste
Application.DisplayAlerts = False
Workbooks(Dir(sTmp)).Activate
ActiveWorkbook.Close SaveChanges:=False
Kill sTmp
Application.DisplayAlerts = True
End If持株WEBサービス(野村証券)のスクレイピング
本項では、 持株WEBサービス(野村証券)のスクレイピング について抜粋して紹介します。
以下が取得できます。
株数現在高
時価評価額
平均買付価格
リターン
繰越金
奨励金
If Time > CDate("6:00") Or Time < CDate("2:00") Then
17:
Err.Number = 0
On Error GoTo ERR17
driver.Get "https://www.e-plan.nomura.co.jp/e-forum/html/index.html"
driver.FindElementByXPath("/html/body/main/div/div/div[2]/ul[2]/li[1]/form/div[1]/div/div[1]/input").SendKeys USER_ID_6
driver.FindElementByXPath("/html/body/main/div/div/div[2]/ul[2]/li[1]/form/div[1]/div/div[2]/div/input").SendKeys USER_PAS6
driver.FindElementByXPath("/html/body/main/div/div/div[2]/ul[2]/li[1]/form/div[2]/p/input").Click
On Error GoTo 0
driver.Get "https://www.e-plan.nomura.co.jp/mocikabu/script/WEAW1101.jsp"
Sheets("持株会").Range("B2") = Mid(driver.FindElementByXPath("/html/body/main/section[1]/div/div/div[1]/div[1]/dl/dd[1]").Text, 1, InStr(driver.FindElementByXPath("/html/body/main/section[1]/div/div/div[1]/div[1]/dl/dd[1]").Text, "株") - 1)
Sheets("持株会").Range("B3") = driver.FindElementByXPath("/html/body/main/section[1]/div/div/div[1]/div[1]/div[1]/dl/dd/span[2]").Text
Sheets("持株会").Range("B4") = driver.FindElementByXPath("/html/body/main/section[1]/div/div/div[1]/div[1]/div[2]/dl/dd/span[1]").Text
Sheets("持株会").Range("B5") = driver.FindElementByXPath("/html/body/main/section[1]/div/div/div[1]/div[1]/div[3]/dl/dd/span[1]").Text
Sheets("持株会").Range("B6") = Mid(driver.FindElementByXPath("/html/body/main/section[1]/div/div/div[2]/div[3]/table/tbody/tr/td").Text, 1, InStr(driver.FindElementByXPath("/html/body/main/section[1]/div/div/div[2]/div[3]/table/tbody/tr/td").Text, "円") - 1)
driver.FindElementByXPath("/html/body/main/section[1]/div/div/div[2]/div[2]/table/thead/tr/th/div").Click
Sheets("持株会").Range("B7") = Mid(driver.FindElementByXPath("/html/body/main/section[1]/div/div/div[2]/div[2]/table/tbody/tr[2]/td").Text, 1, InStr(driver.FindElementByXPath("/html/body/main/section[1]/div/div/div[2]/div[2]/table/tbody/tr[2]/td").Text, "円") - 1)
End If
NRK（確定拠出年金）のスクレイピング
本項では、NRK（確定拠出年金） のスクレイピング について抜粋して紹介します。
私は１銘柄の集中投資をしているため、運用掛金と、現在値、それぞれの総額のみを取得する仕様としています。
If (Weekday(Date) = 1 And (Time > CDate("2:00") Or Time < CDate("8:00"))) Or (getDateWeekNum(Year(Now), Month(Now), vbSaturday, 2) = Date And Time > CDate("21:00")) Or (getDateWeekNum(Year(Now), Month(Now), vbSunday, 2) = Date And Time < CDate("8:00")) Then
Else
driver.Get "https://supportsite.dc.tr.mufg.jp/mmbli/li001/LI001A01/"
driver.FindElementByXPath("/html/body/div[1]/div[2]/div[2]/div/form/div[1]/div[1]/span/input").Clear
driver.FindElementByXPath("/html/body/div[1]/div[2]/div[2]/div/form/div[1]/div[1]/span/input").SendKeys USER_ID_7
driver.FindElementByXPath("/html/body/div[1]/div[2]/div[2]/div/form/div[1]/div[2]/span/input").SendKeys USER_PAS7
driver.FindElementByXPath("/html/body/div[1]/div[2]/div[2]/div/form/div[3]/input").Click
Application.Wait Now + TimeSerial(0, 0, 3)
Sheets("元(DC)").Range("C2") = driver.FindElementByXPath("/html/body/form/div[2]/div[3]/div[2]/div[1]/div/div[1]/table/tbody/tr[2]/td[1]/p/span/em").Text
Sheets("元(DC)").Range("B2") = driver.FindElementByXPath("/html/body/form/div[2]/div[3]/div[2]/div[1]/div/div[1]/table/tbody/tr[2]/td[3]/p/span/em").Text
Sheets("元(DC)").Range("D2") = driver.FindElementByXPath("/html/body/form/div[2]/div[3]/div[2]/div[1]/div/div[1]/table/tbody/tr[2]/td[5]/p/span/em").Text
End If
シラーPER（CAPEレシオ）のスクレイピング
本項では、 シラーPER（CAPEレシオ） 値のスクレイピング について抜粋して紹介します。
私はシラー PER（CAPEレシオ） によって株式の比率を増やしたり減らしたりしているので、値を取得しています。
また取得したうえで、株式の比率を算出する機能も設けています。
コードは以下の通りです。
'シラーPER
driver.Get "https://www.multpl.com/shiller-pe"
sTmp = Mid(driver.FindElementByXPath("/html/body/div[2]/div[2]/div[2]/div[2]/div[3]/div[1]").Text, 27, 5) * 1
EndRow = Sheets("株比率").Cells(1048576, 10).End(xlUp).Row
For i = 4 To EndRow
If sTmp > (Sheets("株比率").Cells(i - 1, 10) + Sheets("株比率").Cells(i, 10)) / 2 And sTmp <= (Sheets("株比率").Cells(i, 10) + Sheets("株比率").Cells(i + 1, 10)) / 2 Then
Sheets("株比率").Cells(i, 1) = 1
Else
Sheets("株比率").Cells(i, 1) = ""
End If
Next iまとめ
いかがでしたでしょうか？ 資産管理ツール作成の参考になれば幸いです。
本稿で紹介したマクロのサポートやカスタマイズ、もしくは新規案件については、条件によって、有償にて対応受付しております。
以下、クラウドワークスないしココナラのアカウントよりご依頼いただければ幸いです。
FIRE太郎さん(その他)に依頼・外注する | 簡単ネット発注なら【クラウドワークス】
日本最大級のクラウドソーシング「クラウドワークス」にご登録いただいているFIRE太郎さんのプロフィールです。その他のスキルを持つプロフェッショナルに1時間から仕事を気軽に発注！発注者は手数料無料。
crowdworks.jp
FIRE太郎さん(会社員)のプロフィール | ココナラ
Excelマクロ（VBA）による自動化ツールの作成が得意です。｜ブログ「FIRE（早期リタイア）研究所」運営中
coconala.com
コメント