Hello, this is Taichi automatic Kaya (@ThinkBestAnswer!

This time, I created a macro that automatically extracts growth stocks (hereinafter referred to as growth stock search macro) by combining VBA and Python! The conditions for growth stocks are those of the domestic stocks that have recently announced their financial results that meet any of the following conditions.

--Operating income margin of 10% or more, 1-year sales growth rate of 20% or more --The operating income margin is 10% or more, and the settlement data is only for the latest year (new public shares, change of settlement period, etc.)

I will explain the outline of the macro and the processing contents. I hope it helps you to see if you can do this with Excel macros!

1. Overview of Growth Stock Search Macro 2. Preparation for use 3. Full code 4. Explanation of each process

1. Overview of growth stock search macro

The growth stock search macro has the following two functions.

(1) When Excel is opened, the growth stock whose previous day is the announcement date of financial results is automatically extracted. (2) Automatically extract growth stocks whose financial results are announced on the date entered with the search button on the specified date.

Function (1): Search for growth stocks the day before

When you open Excel, the stocks whose previous day is the announcement date of financial results are automatically extracted.

When you open the Growth Stock Search Macro, the latest financial results data will be downloaded from the site called Financial Results Pro, which allows you to download the financial results data of domestic listed companies in Excel format. 決算データダウンロード中.jpg From the downloaded data, the stock data that meets the conditions stated at the beginning of the financial results announcement date on the previous day will be automatically copied to the growth stock search macro book. 自動コピー結果.jpg

Function (2): Search for growth stocks on specified days

If you press the specified date search button in the growth stock search macro, the date input field will be displayed. 日付入力欄.jpg Stock data whose entered date is the date of announcement of financial results and meets the conditions written at the beginning will be automatically copied to the book of growth stock search macro in the same way as function (1). Use the financial results data downloaded by function (1).

By the way, SBI SECURITIES'Financial Results Announcement Calendar is convenient for checking the financial results announcement schedule.

2. Preparation for use

I will describe the environment required to use the growth stock search macro.

① PC whose OS is Windows

We have not confirmed the operation on Mac or Linux.

② office software

Of course you will need it because you use Excel. Operation has been confirmed from office2010 to office365. ③GoogleChrome、Python3、Selenium、chromedriver It is used for the function to download financial results data. The download and setup procedures are described at the following sites respectively.

-Google Chrome: Official Site -Python3, Selenium, chromedriver: Automatic browser operation using Python and Selenium

④xlwings、pywin32 Required to execute python files from VBA (Excel macro). A site called Infinite Impossible Drive explains the installation procedure in an easy-to-understand manner.

3. Full code

Here is the full code of VBA and Python used in the growth stock search macro.

Macro of the previous day's growth stock search function

`Previous day growth stock search`


Option Explicit

Dim bookName As String
Dim sheet1 As String
Dim Sheet2 As String
Dim deleteIndex As Integer
Dim rowIndex As Integer

Sub Auto_Open()

    Dim yesterday As String
    Dim koumokuName As String
    Dim colIndex As Integer
    Dim maxCol As Integer
    Dim maxRow As Integer
    Dim downLoadFolderPath As String
    Dim fileName As String
    Dim tanshinPath As String
    Dim grothStockCellVal As String
    Dim oneStockCellVal As String
    Dim rowIndex As Integer
    Dim outPutBookName As String
    Dim outputRowIndex As Integer
    
    outPutBookName = "Growth stock search.xlsm"
    sheet1 = "Sheet1"
    Sheet2 = "Sheet2"
    Workbooks(outPutBookName).Worksheets(sheet1).Range("9:200").ClearContents
    
    downLoadFolderPath = "{Enter the download folder path}"
    fileName = Dir(downLoadFolderPath & "*.xls")
    'Delete all Excel in the download folder
    If fileName <> "" Then
        Kill downLoadFolderPath & "*.xls"
    End If
    
    'Download the list of financial statements
    Call RunPython("sys.path.append(r'{Enter the path of the Python file to call}'); import kessanTanshin; kessanTanshin.downLoadTanshin()")
    
    'Open the financial statements book
    fileName = Dir(downLoadFolderPath & "*.xls")
    tanshinPath = downLoadFolderPath & fileName
    Workbooks.Open tanshinPath
    
    bookName = fileName
    yesterday = Format(DateAdd("d", -1, Date), "yyyy/m/d")
    
    colIndex = 1
    koumokuName = "First"
    Do
        If koumokuName = "Accounting standards" Then
            Call colsDelete(colIndex, 2)
        ElseIf koumokuName = "Ordinary income" Then
            Call colsDelete(colIndex, 10)
        Else
            colIndex = colIndex + 1
       End If
       koumokuName = Workbooks(bookName).Worksheets(sheet1).Cells(1, colIndex).Value
    Loop While koumokuName <> "Information disclosure or update date"
    
    Workbooks(bookName).Worksheets(sheet1).Range("A1").AutoFilter Field:=colIndex, Criteria1:="=" & yesterday
    
    Workbooks(bookName).Worksheets(Sheet2).Range("A:N").Delete
    
    Workbooks(bookName).Worksheets(sheet1).UsedRange.Copy Destination:=Workbooks(bookName).Worksheets(Sheet2).Range("A1")

    Workbooks(bookName).Worksheets(Sheet2).Columns("B").ColumnWidth = 35
    Workbooks(bookName).Worksheets(Sheet2).Columns("E").AutoFit
    Workbooks(bookName).Worksheets(Sheet2).Columns("F").AutoFit
    Workbooks(bookName).Worksheets(Sheet2).Columns("H").AutoFit
    Workbooks(bookName).Worksheets(Sheet2).Columns("I").AutoFit
    
    maxCol = Workbooks(bookName).Worksheets(Sheet2).Range("A1").End(xlToRight).Column
    Workbooks(bookName).Worksheets(Sheet2).Cells(1, maxCol + 1).Value = "Operating profit margin"
    Workbooks(bookName).Worksheets(Sheet2).Cells(1, maxCol + 2).Value = "Increased yield"
    Workbooks(bookName).Worksheets(Sheet2).Cells(1, maxCol + 3).Value = "Growth stock"
    Workbooks(bookName).Worksheets(Sheet2).Cells(1, maxCol + 4).Value = "Only one line"
    
    On Error GoTo OnError
    
    maxRow = Workbooks(bookName).Worksheets(Sheet2).Range("A1").End(xlDown).Row
    Workbooks(bookName).Worksheets(Sheet2).Cells(2, maxCol + 1).Value = "=I2/H2"
    Workbooks(bookName).Worksheets(Sheet2).Range("K2").AutoFill Destination:=Workbooks(bookName).Worksheets(Sheet2).Range("K2:K" & maxRow)
    
    Workbooks(bookName).Worksheets(Sheet2).Cells(2, maxCol + 2).Value = "=(H2-H3)/H3"
    Workbooks(bookName).Worksheets(Sheet2).Range("L2").AutoFill Destination:=Workbooks(bookName).Worksheets(Sheet2).Range("L2:L" & maxRow)
    
    Workbooks(bookName).Worksheets(Sheet2).Cells(2, maxCol + 3).Value = "=IF(AND(K2>=0.1, L2>=0.2, A2=A3, A1<>A2),""GOOD"","""")"
    Workbooks(bookName).Worksheets(Sheet2).Range("M2").AutoFill Destination:=Workbooks(bookName).Worksheets(Sheet2).Range("M2:M" & maxRow)
    
    Workbooks(bookName).Worksheets(Sheet2).Cells(2, maxCol + 4).Value = "=IF(AND(A2<>A1,A2<>A3,K2>=0.1),""〇"","""")"
    Workbooks(bookName).Worksheets(Sheet2).Range("N2").AutoFill Destination:=Workbooks(bookName).Worksheets(Sheet2).Range("N2:N" & maxRow)
    
    'Extract only growth stocks and 1-row stocks
    outputRowIndex = 9
    For rowIndex = 1 To maxRow
        If Not IsError(Workbooks(bookName).Worksheets(Sheet2).Cells(rowIndex, 13).Value) And Not IsError(Workbooks(bookName).Worksheets(Sheet2).Cells(rowIndex, 14).Value) Then
            grothStockCellVal = Workbooks(bookName).Worksheets(Sheet2).Cells(rowIndex, 13).Value
            oneStockCellVal = Workbooks(bookName).Worksheets(Sheet2).Cells(rowIndex, 14).Value
            If grothStockCellVal = "GOOD" Then
                Workbooks(bookName).Worksheets(Sheet2).Range("A" & rowIndex & ":L" & rowIndex + 1).Copy
                Workbooks(outPutBookName).Worksheets(sheet1).Range("A" & outputRowIndex).PasteSpecial _
                                 Paste:=xlPasteValues, _
                                 Operation:=xlNone, _
                                 SkipBlanks:=False, _
                                 transpose:=False
                outputRowIndex = outputRowIndex + 2
            ElseIf oneStockCellVal = "〇" Then
                Workbooks(bookName).Worksheets(Sheet2).Range("A" & rowIndex & ":K" & rowIndex).Copy
                Workbooks(outPutBookName).Worksheets(sheet1).Range("A" & outputRowIndex).PasteSpecial _
                                 Paste:=xlPasteValues, _
                                 Operation:=xlNone, _
                                 SkipBlanks:=False, _
                                 transpose:=False
                outputRowIndex = outputRowIndex + 1
            End If
        End If
    Next
    
    Call Workbooks(bookName).Close(SaveChanges:=False)
    End
OnError:
    MsgBox yesterday + "There are no stocks to announce financial results"
    
    Call Workbooks(bookName).Close(SaveChanges:=False)
End Sub

Function colsDelete(colIndex As Integer, deleteCount As Integer)
    For deleteIndex = 1 To deleteCount
        Workbooks(bookName).Worksheets(sheet1).Columns(colIndex).Delete
    Next
End Function

Python file called from the previous day's growth stock search

`kessanTanshin.py`


from selenium import webdriver
import time
import os

def downLoadTanshin():

    #Load WebDriver
    driver = webdriver.Chrome("{chromedriver.Enter the absolute path of the exe}")

    #The search will be repeated for a specified time until the element is found.
    driver.implicitly_wait(5)  #Seconds

    driver.get("http://ke.kabupro.jp/doc/down40.htm")
    link = driver.find_element_by_xpath("// *[ @ id = 'centercont'] / table / tbody / tr[3] / td / a[2]")
    link.click()

    #Waiting for download
    fileName = link.get_attribute("href").split("/")[4]
    downloadFolder = "{Enter the download folder path}"
    fileFullPath = downloadFolder + fileName
    while not os.path.isfile(fileFullPath):
        time.sleep(1)

Specified day growth stock search function macro

`Specified day growth stock search`


Option Explicit

Dim bookName As String
Dim sheet1 As String
Dim Sheet2 As String
Dim deleteIndex As Integer
Dim rowIndex As Integer

Sub selectDateSearch()

    Dim yesterday As String
    Dim koumokuName As String
    Dim colIndex As Integer
    Dim maxCol As Integer
    Dim maxRow As Integer
    Dim downLoadFolderPath As String
    Dim fileName As String
    Dim tanshinPath As String
    Dim grothStockCellVal As String
    Dim oneStockCellVal As String
    Dim rowIndex As Integer
    Dim outPutBookName As String
    Dim outputRowIndex As Integer
    
    outPutBookName = "Growth stock search.xlsm"
    sheet1 = "Sheet1"
    Sheet2 = "Sheet2"
    Workbooks(outPutBookName).Worksheets(sheet1).Range("9:200").ClearContents
    
    downLoadFolderPath = "{Enter the download folder path}"
    
    'Open the financial statements book
    fileName = Dir(downLoadFolderPath & "*.xls")
    tanshinPath = downLoadFolderPath & fileName
    Workbooks.Open tanshinPath
    
    bookName = fileName
    yesterday = InputBox(Prompt:="Please enter the financial results announcement date you want to search", default:=Format(DateAdd("d", -1, Date), "yyyy/m/d"))
    
    colIndex = 1
    koumokuName = "First"
    Do
        If koumokuName = "Accounting standards" Then
            Call colsDelete(colIndex, 2)
        ElseIf koumokuName = "Ordinary income" Then
            Call colsDelete(colIndex, 10)
        Else
            colIndex = colIndex + 1
       End If
       koumokuName = Workbooks(bookName).Worksheets(sheet1).Cells(1, colIndex).Value
    Loop While koumokuName <> "Information disclosure or update date"
    
    Workbooks(bookName).Worksheets(sheet1).Range("A1").AutoFilter Field:=colIndex, Criteria1:="=" & yesterday
    
    Workbooks(bookName).Worksheets(Sheet2).Range("A:N").Delete
    
    Workbooks(bookName).Worksheets(sheet1).UsedRange.Copy Destination:=Workbooks(bookName).Worksheets(Sheet2).Range("A1")

    Workbooks(bookName).Worksheets(Sheet2).Columns("B").ColumnWidth = 35
    Workbooks(bookName).Worksheets(Sheet2).Columns("E").AutoFit
    Workbooks(bookName).Worksheets(Sheet2).Columns("F").AutoFit
    Workbooks(bookName).Worksheets(Sheet2).Columns("H").AutoFit
    Workbooks(bookName).Worksheets(Sheet2).Columns("I").AutoFit
    
    maxCol = Workbooks(bookName).Worksheets(Sheet2).Range("A1").End(xlToRight).Column
    Workbooks(bookName).Worksheets(Sheet2).Cells(1, maxCol + 1).Value = "Operating profit margin"
    Workbooks(bookName).Worksheets(Sheet2).Cells(1, maxCol + 2).Value = "Increased yield"
    Workbooks(bookName).Worksheets(Sheet2).Cells(1, maxCol + 3).Value = "Growth stock"
    Workbooks(bookName).Worksheets(Sheet2).Cells(1, maxCol + 4).Value = "Only one line"
    
    On Error GoTo OnError
    
    maxRow = Workbooks(bookName).Worksheets(Sheet2).Range("A1").End(xlDown).Row
    Workbooks(bookName).Worksheets(Sheet2).Cells(2, maxCol + 1).Value = "=I2/H2"
    Workbooks(bookName).Worksheets(Sheet2).Range("K2").AutoFill Destination:=Workbooks(bookName).Worksheets(Sheet2).Range("K2:K" & maxRow)
    
    Workbooks(bookName).Worksheets(Sheet2).Cells(2, maxCol + 2).Value = "=(H2-H3)/H3"
    Workbooks(bookName).Worksheets(Sheet2).Range("L2").AutoFill Destination:=Workbooks(bookName).Worksheets(Sheet2).Range("L2:L" & maxRow)
    
    Workbooks(bookName).Worksheets(Sheet2).Cells(2, maxCol + 3).Value = "=IF(AND(K2>=0.1, L2>=0.2, A2=A3, A1<>A2),""GOOD"","""")"
    Workbooks(bookName).Worksheets(Sheet2).Range("M2").AutoFill Destination:=Workbooks(bookName).Worksheets(Sheet2).Range("M2:M" & maxRow)
    
    Workbooks(bookName).Worksheets(Sheet2).Cells(2, maxCol + 4).Value = "=IF(AND(A2<>A1,A2<>A3,K2>=0.1),""〇"","""")"
    Workbooks(bookName).Worksheets(Sheet2).Range("N2").AutoFill Destination:=Workbooks(bookName).Worksheets(Sheet2).Range("N2:N" & maxRow)
    
    'Extract only growth stocks and 1-row stocks
    outputRowIndex = 9
    For rowIndex = 1 To maxRow
        If Not IsError(Workbooks(bookName).Worksheets(Sheet2).Cells(rowIndex, 13).Value) And Not IsError(Workbooks(bookName).Worksheets(Sheet2).Cells(rowIndex, 14).Value) Then
            grothStockCellVal = Workbooks(bookName).Worksheets(Sheet2).Cells(rowIndex, 13).Value
            oneStockCellVal = Workbooks(bookName).Worksheets(Sheet2).Cells(rowIndex, 14).Value
            If grothStockCellVal = "GOOD" Then
                Workbooks(bookName).Worksheets(Sheet2).Range("A" & rowIndex & ":L" & rowIndex + 1).Copy
                Workbooks(outPutBookName).Worksheets(sheet1).Range("A" & outputRowIndex).PasteSpecial _
                                 Paste:=xlPasteValues, _
                                 Operation:=xlNone, _
                                 SkipBlanks:=False, _
                                 transpose:=False
                outputRowIndex = outputRowIndex + 2
            ElseIf oneStockCellVal = "〇" Then
                Workbooks(bookName).Worksheets(Sheet2).Range("A" & rowIndex & ":K" & rowIndex).Copy
                Workbooks(outPutBookName).Worksheets(sheet1).Range("A" & outputRowIndex).PasteSpecial _
                                 Paste:=xlPasteValues, _
                                 Operation:=xlNone, _
                                 SkipBlanks:=False, _
                                 transpose:=False
                outputRowIndex = outputRowIndex + 1
            End If
        End If
    Next
    
    Call Workbooks(bookName).Close(SaveChanges:=False)
    End
OnError:
    MsgBox yesterday + "There are no stocks to announce financial results"
    
    Call Workbooks(bookName).Close(SaveChanges:=False)
End Sub

Function colsDelete(colIndex As Integer, deleteCount As Integer)    
    For deleteIndex = 1 To deleteCount
        Workbooks(bookName).Worksheets(sheet1).Columns(colIndex).Delete
    Next
End Function

4. Explanation of each process

I will explain the contents of each process step by step.

Growth stock search macro Process that automatically flows when Excel is started (previous day growth stock search)

VBA is automatically executed when Excel is started if the name of the Sub procedure is Auto_Open. So, the day before, the growth stock search function wrote the process in Auto_Open.

Sub Auto_Open()
    'The process written here is automatically executed when Excel is started.
End Sub

First, delete the Excel file (with the extension * .xls) in the download folder. This is to make it easier to identify when calling the financial statements book to be downloaded from now on with a macro.

`Previous day growth stock search`


    downLoadFolderPath = "{Enter the download folder path}"
    fileName = Dir(downLoadFolderPath & "*.xls")
    'Delete all Excel in the download folder
    If fileName <> "" Then
        Kill downLoadFolderPath & "*.xls"
    End If

Then call the Python file (kessanTanshin.py) to download the financial statements book.

`Previous day growth stock search`


    'Download the list of financial statements
    Call RunPython("sys.path.append(r'{Enter the absolute path of the Python file to call}'); import kessanTanshin; kessanTanshin.downLoadTanshin()")

From here, I will explain the process of downloading the financial statements book by Python scraping.

First, access the site called Financial Statement Pro.

`kessanTanshin.py`


     #Load WebDriver
    driver = webdriver.Chrome("{chromedriver.Enter the absolute path of the exe}")

    #The search will be repeated for a specified time until the element is found.
    driver.implicitly_wait(5)  #Seconds

    driver.get("http://ke.kabupro.jp/doc/down40.htm")

After accessing Financial Statement Pro, click the link to download the financial statements book.

`kessanTanshin.py`


    link = driver.find_element_by_xpath("// *[ @ id = 'centercont'] / table / tbody / tr[3] / td / a[2]")
    link.click()

決算プロのダウンロードリンク.jpg

After pressing the link, wait in a loop process until the download is completed.

`kessanTanshin.py`


    #Waiting for download
    fileName = link.get_attribute("href").split("/")[4]
    downloadFolder = "{Enter the download folder path}"
    fileFullPath = downloadFolder + fileName
    while not os.path.isfile(fileFullPath):
        time.sleep(1)

When the download is complete, the process will return to the macro.

First, open the financial statements book in the download folder.

`Previous day growth stock search`


    'Open the financial statements book
    fileName = Dir(downLoadFolderPath & "*.xls")
    'Open the financial statements book
    fileName = Dir(downLoadFolderPath & "*.xls")
    tanshinPath = downLoadFolderPath & fileName
    Workbooks.Open tanshinPath tanshinPath = downLoadFolderPath & fileName
    Workbooks.Open tanshinPath

Delete the columns of "Accounting Standard", "Consolidated Individual", "Ordinary Income"-"Financial Cash Flow" from the financial statements book. It is a preparation to copy only the necessary data from sheet1 to sheet2 of the financial statements book and use the filter function of Excel later. 不要な列を削除.jpg

`Previous day growth stock search`


    colIndex = 1
    koumokuName = "First"
    Do
        If koumokuName = "Accounting standards" Then
            Call colsDelete(colIndex, 2)
        ElseIf koumokuName = "Ordinary income" Then
            Call colsDelete(colIndex, 10)
        Else
            colIndex = colIndex + 1
       End If
       koumokuName = Workbooks(bookName).Worksheets(sheet1).Cells(1, colIndex).Value
    Loop While koumokuName <> "Information disclosure or update date"

The processing contents of the colsDelete function called in the column deletion processing are as follows.

`Previous day growth stock search`


Function colsDelete(colIndex As Integer, deleteCount As Integer)
    For deleteIndex = 1 To deleteCount
        Workbooks(bookName).Worksheets(sheet1).Columns(colIndex).Delete
    Next
End Function

Use the filter function of Excel to narrow down the data on sheet1 of the financial statements book to those whose "information disclosure or update date" is the previous day. Then delete columns A to N of sheet2 and copy all the data narrowed down from sheet1 to sheet2.

`Previous day growth stock search`


    Workbooks(bookName).Worksheets(sheet1).Range("A1").AutoFilter Field:=colIndex, Criteria1:="=" & yesterday
    
    Workbooks(bookName).Worksheets(Sheet2).Range("A:N").Delete
    
    Workbooks(bookName).Worksheets(sheet1).UsedRange.Copy Destination:=Workbooks(bookName).Worksheets(Sheet2).Range("A1")

Get the rightmost column number of the first row of sheet2. Increase the column number by 1, and add "Operating profit margin", "Increase in yield", "Growth stock", and "Only one row" to the right end of the first row.

`Previous day growth stock search`


    maxCol = Workbooks(bookName).Worksheets(Sheet2).Range("A1").End(xlToRight).Column
    Workbooks(bookName).Worksheets(Sheet2).Cells(1, maxCol + 1).Value = "Operating profit margin"
    Workbooks(bookName).Worksheets(Sheet2).Cells(1, maxCol + 2).Value = "Increased yield"
    Workbooks(bookName).Worksheets(Sheet2).Cells(1, maxCol + 3).Value = "Growth stock"
    Workbooks(bookName).Worksheets(Sheet2).Cells(1, maxCol + 4).Value = "Only one line"

If none of the stocks meet the growth stock conditions listed at the beginning, an error will occur in the subsequent processing. So I will put the error handling process here. If an error occurs, the message box "There are no stocks with financial results announced on the previous day}" will be displayed, and the financial results book will be closed.

`Previous day growth stock search`


    On Error GoTo OnError
    'Processing that may cause an error
OnError:
    MsgBox yesterday + "There are no stocks to announce financial results"
    
    Call Workbooks(bookName).Close(SaveChanges:=False)

Using the values on the second row of sheet 2 of the financial statements book, we will calculate the values for "operating profit margin," "increased yield," "growth stock," and "only one row." The conditions for "growth stock" and "only one line" are as follows.

--Growth stock = Operating profit margin 0.1 or higher and sales growth (sales growth rate) 0.2 or higher --Only one line = Data with an operating profit margin of 0.1 or more and financial data for the latest year

After calculating each of the above values for the data in the second row, the same formula is applied to all rows using the autofill function of Excel.

`Previous day growth stock search`


maxRow = Workbooks(bookName).Worksheets(Sheet2).Range("A1").End(xlDown).Row
    Workbooks(bookName).Worksheets(Sheet2).Cells(2, maxCol + 1).Value = "=I2/H2"
    Workbooks(bookName).Worksheets(Sheet2).Range("K2").AutoFill Destination:=Workbooks(bookName).Worksheets(Sheet2).Range("K2:K" & maxRow)
    
    Workbooks(bookName).Worksheets(Sheet2).Cells(2, maxCol + 2).Value = "=(H2-H3)/H3"
    Workbooks(bookName).Worksheets(Sheet2).Range("L2").AutoFill Destination:=Workbooks(bookName).Worksheets(Sheet2).Range("L2:L" & maxRow)
    
    Workbooks(bookName).Worksheets(Sheet2).Cells(2, maxCol + 3).Value = "=IF(AND(K2>=0.1, L2>=0.2, A2=A3, A1<>A2),""GOOD"","""")"
    Workbooks(bookName).Worksheets(Sheet2).Range("M2").AutoFill Destination:=Workbooks(bookName).Worksheets(Sheet2).Range("M2:M" & maxRow)
    
    Workbooks(bookName).Worksheets(Sheet2).Cells(2, maxCol + 4).Value = "=IF(AND(A2<>A1,A2<>A3,K2>=0.1),""〇"","""")"
    Workbooks(bookName).Worksheets(Sheet2).Range("N2").AutoFill Destination:=Workbooks(bookName).Worksheets(Sheet2).Range("N2:N" & maxRow)

Finally, copy the data that meets either the "growth stock" or "one line only" condition to the growth stock search macro book, close the financial results book, and finish the process.

`Previous day growth stock search`


    'Extract only growth stocks and 1-row stocks
    outputRowIndex = 9
    For rowIndex = 1 To maxRow
        If Not IsError(Workbooks(bookName).Worksheets(Sheet2).Cells(rowIndex, 13).Value) And Not IsError(Workbooks(bookName).Worksheets(Sheet2).Cells(rowIndex, 14).Value) Then
            grothStockCellVal = Workbooks(bookName).Worksheets(Sheet2).Cells(rowIndex, 13).Value
            oneStockCellVal = Workbooks(bookName).Worksheets(Sheet2).Cells(rowIndex, 14).Value
            If grothStockCellVal = "GOOD" Then
                Workbooks(bookName).Worksheets(Sheet2).Range("A" & rowIndex & ":L" & rowIndex + 1).Copy
                Workbooks(outPutBookName).Worksheets(sheet1).Range("A" & outputRowIndex).PasteSpecial _
                                 Paste:=xlPasteValues, _
                                 Operation:=xlNone, _
                                 SkipBlanks:=False, _
                                 transpose:=False
                outputRowIndex = outputRowIndex + 2
            ElseIf oneStockCellVal = "〇" Then
                Workbooks(bookName).Worksheets(Sheet2).Range("A" & rowIndex & ":K" & rowIndex).Copy
                Workbooks(outPutBookName).Worksheets(sheet1).Range("A" & outputRowIndex).PasteSpecial _
                                 Paste:=xlPasteValues, _
                                 Operation:=xlNone, _
                                 SkipBlanks:=False, _
                                 transpose:=False
                outputRowIndex = outputRowIndex + 1
            End If
        End If
    Next
    
    Call Workbooks(bookName).Close(SaveChanges:=False)

Processing that flows when the specified date search button is pressed (specified date growth stock search)

This will display the input box with the following code. Then, the data in the financial statements book is narrowed down to those whose "information disclosure or update date" matches the entered date. Use the financial results book that is already in the download folder. Other processing is the same as the previous day's growth stock search module, so it is omitted.

`Previous day growth stock search`


    today = InputBox(Prompt:="Please enter the financial results announcement date you want to search", default:=Format(DateAdd("d", -1, Date), "yyyy/m/d"))

If you would like this macro, please contact me on Twitter (@ThinkBestAnswer). All files will be sent by email. If you have any comments or questions, please contact us on Twitter so that we can easily notice them.

Excel macro (VBA) x Python scraping to automate growth stock search

table of contents

1. Overview of growth stock search macro

Function (1): Search for growth stocks the day before

Function (2): Search for growth stocks on specified days

2. Preparation for use

① PC whose OS is Windows

② office software

3. Full code

Macro of the previous day's growth stock search function

`Previous day growth stock search`

Python file called from the previous day's growth stock search

`kessanTanshin.py`

Specified day growth stock search function macro

`Specified day growth stock search`

4. Explanation of each process

Growth stock search macro Process that automatically flows when Excel is started (previous day growth stock search)

`Previous day growth stock search`

`Previous day growth stock search`

`kessanTanshin.py`

`kessanTanshin.py`

`kessanTanshin.py`

`Previous day growth stock search`

`Previous day growth stock search`

`Previous day growth stock search`

`Previous day growth stock search`

`Previous day growth stock search`

`Previous day growth stock search`

`Previous day growth stock search`

`Previous day growth stock search`

Processing that flows when the specified date search button is pressed (specified date growth stock search)

`Previous day growth stock search`