Auto get stock price macro-(1)-

Do you trade stocks already?
Auto get stock price macro
1. Step 1
2. 関連

Do you trade stocks already?

In Japan today, bank interest rates are fairly low, that’s around 0.002%. In addition, there is also a tax incentive system for investment called * NISA (Nippon Individual Savings Account), so many people think that they will buy stocks rather than depositing money in a bank maybe.

NISA: Roughly speaking, it is a system that allows you to receive tax-free income from your investment.
Normally, 20.315% tax is levied on dividends / distributions and profits from transfers. However within NISA is exempt from tax.
For details, if you search NISA with google, there is a lot of information, so if you are interested, please check it.

I started NISA about three months ago, however I diversify my investment destinations for risk hedging, including individual stocks, management becomes more and more complex.

Auto get stock price macro

I think it is not smart that we don’t know about every day’s detail information of invested stocks. If you’d like to earn by stocks. Therefore, I created a macro that automatically gets the stock price.

The procedure for macros is as follows. (The macro is designed to run on windows 10.)

Acquire all stock prices in your portfolio (like a list of stocks you own) with Excel VBA. (so-called scraping)
Reshape information got by 1. In order to accumulate logs.
Use the task scheduler to set to do 1. and 2. at a fixed time every day. ←The most Important

Before actually introducing the procedure, there are some items that need to be set.
The following libraries need to be activated for use with VBA in order to run the macro.
・Microsoft HTML Object Library
・Microsoft Internet Controls

The procedure is the following 4 steps.
Launch the VBA development tool ⇒ Reference settings in the tool ⇒ Find the above two items and check them ⇒ Click OK

Step 1

In order to do step 1, Let’s take a quick look at how the current page is organized. In my case, I use SBI Securities, so it looks like the image below.

The following 5 pieces of information are required.
①Name of stock owned
②Number of stock owned
③Acquisition stock price
④Base stock price
⑤Valuation profit and loss

Next, how is it represented in HTML? Let’s actually check the source.

You can right-click the mouse on the browser, the following “page source” will be displayed, so check it from here.

I got the necessary information maybe.

The place I want to pay attention to is the class = “mtext” marked in yellow in the image below. It turns out that the information I want is stored in a table named mtext.

OK, next step is just get necessary information by VBA.(Scraping)

First of all, I have to move to the portfolio screen.
Write a VBA that opens up to the login screen of SBI securities.

Sub StockPriceMonitoring()
   'HTMLドキュメントオブジェクトを準備
   Dim htmlDoc As HTMLDocument
   'ブラウザ指定(IE)
   Dim objIE As InternetExplorer
   
   'ブラウザオブジェクトを生成
   Set objIE = CreateObject("InternetExplorer.Application")

   'IEを表示
   objIE.Visible = True
   
   'ログイン画面
   objIE.navigate "https://www.sbisec.co.jp/ETGate"

   '読み込み待ち(これがないと、うまくいきません。)
   Do While objIE.Busy = True Or objIE.readyState < 4
      '4=READYSTATE_COMPLETE
      DoEvents
   Loop

   'objIEで読み込まれているHTMLドキュメントをセット
   Set htmlDoc = objIE.document
   
   以後の処理はここに書いていきます。
   
End Sub

Next, of course, I need to enter my ID and password to access my account.

Enter your ID and password in 〇〇〇 below.

	'ID
	htmlDoc.getElementById("user_id").Value = "〇〇〇"
	'パスワード
	htmlDoc.getElementById("user_password").Value = "〇〇〇"
	'ログインボタンクリック
	htmlDoc.getElementById("ACT_login").Click

	'読み込み待ち(これがないと、うまくいきません。)
	Do While objIE.Busy = True Or objIE.readyState < 4
		'4=READYSTATE_COMPLETE
		DoEvents
	Loop

Finally, write the information about portfolio to excel in order.

	'ポートフォリオ画面に移動
	objIE.navigate "https://site3.sbisec.co.jp/ETGate/?_ControlID=WPLETacR001Control&_PageID=DefaultPID&_DataStoreID=DSWPLETacR001Control&_ActionID=DefaultAID&getFlg=on"
	
	'objIEで読み込まれているHTMLドキュメントをセット
	Set htmlDoc = objIE.document
	
	'目的のテーブル情報を得るためにtableタグを格納
	table = htmlDoc.getElementsByTagName("table")
	
	'excel出力行列コントロール用に準備（本来は先頭に書いた方がいいです）
	Dim row, col As Integer
	row = 1
	col = 1	'A1から順に値を出力する場合
	
	'tableにおいて、クラス名がmtextであるものに対して処理を行う
	For Each mtext In table.getElementsByClassName("mtext")
	
		'情報をexcelに出力
		Cells(row, col) = mtext.innerText
		col = col + 1
		
		'4個周期で次の株情報になるため改行処理を入れる
		If col = 4 Then
			col = 1
			row = row + 1
		End If

	Next

That’s roughly the overall code.
I was able to output the desired information as shown below. it’s like good.
As long as you get the information, you can format the data as you like with excel.