Auto get stock price macro-(2)-

Auto get stock price macro

Regarding the macro that automatically gets the stock price In the previous article, I introduced the following step 1, so in this article I’ll explain step 2 and subsequent steps.

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

Step 2

Let’s store the information get in step 1 as a log in an easy-to-use format.
The completed image is as follows.

The idea is simple. I carry out steps ① to ③ in order for all the stock I own.

①Get the macro execution date and slide col (column) to the left until it becomes an empty row.
②i – When the stock name already exists
⇒Slide down to the row where the relevant stock exists.
ii – When the corresponding stock does not exist in the stock name (new stock is purchased)
⇒ For stocks that do not exist even if I slide to the bottom when searching in i. Add the line to the new stock.
③Output the valuation gain / loss of the stock to the cell (row, column)

The process of ① is expressed in VBA as follows.
When creating a macro with VBA, rather than writing the process from the upper left (A1), I think it is better to define it from about C3 with a margin. It will be easier to implement if you want to add functionality later. If you don’t use it, you can hide the area, so it is recommended to leave a margin.

Dim row, rowInit, col, colInit As Integer	'出力行列コントロール用
row = 2			'出力開始行　マクロ実行日を記録するために1行下げておく
col = 3			'出力開始列　評価損益や株式を記録するために2列ずらしておく
rowInit = row		'出力リセット用 ←作っておくと後で楽
colInit = col		'出力リセット用 ←作っておくと後で楽
	
count = 0		'マクロ結果を格納していくcol(行)を決定するために使用
'ログ作成日時記録（"log"シートを予め作っておく必要がある）
Do While Sheets("log").Cells(rowInit - 1, colInit + count).Value <> ""
   count = count + 1
   If Sheets("log").Cells(rowInit - 1, colInit + count).Value = Date Then
      GoTo CONTINUEDATE '①に飛ぶ
   End If
Loop

CONTINUEDATE: '①
   Sheets("log").Cells(rowInit - 1, colInit + count).Value = Date

After deciding where to output, we will drop the acquired information into excel in order.
The log is actually recorded by the processing around the 12th and 23rd lines.

Dim lRow, lCol As Integer                   'ログ用パラメータ
Dim outFlag As Boolean                      '有効なテーブル範囲フラグ

For Each mtext In table.getElementsByClassName("mtext") '②
  If outFlag = True Then
      Sheet.Cells(row, col) = mtext.innerText
      col = col + 1
      lRow = rowInit
      lCol = colInit
      continueFlag = False
      If col = colInit + 4 Then
          'ログ作成　③
          Do While Sheets("log").Cells(lRow, lCol + 1).Value <> ""
              If Sheet.Cells(row, colInit - 1) = Sheets("log").Cells(lRow, colInit + 1) Then
                  Sheets("log").Cells(lRow, colInit + count) = Sheet.Cells(row, col - 1) '評価損益
                  continueFlag = True
              End If
              lRow = lRow + 1
          Loop
          If continueFlag = False Then
              Sheets("log").Cells(lRow, colInit) = Sheet.Cells(row, colInit - 1)　　 '株名
              Sheets("log").Cells(lRow, colInit + count) = Sheet.Cells(row, col - 1) '評価損益
          End If
          col = colInit
          row = row + 1
      End If
  
   End If
  
   If mtext.innerText = "評価損益" Then
      outFlag = True
   End If
  
Next

With the above, every time you execute a macro, it will take a log automatically.

If you run the macro more than once on the same day, the log for that day will be overwritten.

I think that this process will be more efficient if you check how to write VBA appropriately, so you can customize it as you like.

If you can implement it so far as a source, it will be almost completed.
However, as it stands, you have to manually start the macro once and for all. This is a bothersome. Let’s make it work automatically.

The processing will be introduced in the next “株価自動取得マクロ_SBI証券-(3)-“.

Thank you for reading to the end!!