I was hoping to be able to write a python or VBA script that would go through a two column list (Full Name, Company), and return an updated sheet with each person’s CRD number, City and State.

Is this possible/would anyone be able to assist with the code for this?

Thank you

I’ve used code written by ChatGPT, but unfortunately I have limited knowledge and don’t know what I need to replace the prompts with:

Sub FindBrokerCheckInfo()
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim firstName As String
    Dim lastName As String
    Dim companyName As String
    Dim url As String
    Dim http As Object
    Dim response As String
    
    ' Set your worksheet and range where the data is located
    Set ws = ThisWorkbook.Sheets("Sheet1") ' names_companies.xlsm
    Set rng = ws.Range("A2:C" & ws.Cells(ws.Rows.Count, 1).End(xlUp).Row)
    
    ' Create an HTTP request object
    Set http = CreateObject("MSXML2.ServerXMLHTTP.6.0")
    
    For Each cell In rng.Rows
        firstName = cell.Cells(1, 1).Value
        lastName = cell.Cells(1, 2).Value
        companyName = cell.Cells(1, 3).Value
        
        ' Construct the BrokerCheck URL
        url = "https://brokercheck.finra.org/individual/summary/" & firstName & "-" & lastName
        
        ' Make the HTTP request
        http.Open "GET", url, False
        http.send
        
        ' Extract relevant information from the response
        If http.Status = 200) Then
            response = http.responseText
            ' Parse the response to find CRD number, city, and state
            ' (You'll need to customize this part based on the website's structure)
            ' Example: crdNumber = ExtractInfo(response, "CRD Number: (d+)")
            '          city = ExtractInfo(response, "City: ([A-Za-z ]+)")
            '          state = ExtractInfo(response, "State: ([A-Za-z]+)")
            
            ' Print the results (replace with your own logic)
            Debug.Print "Name: " & firstName & " " & lastName
            Debug.Print "CRD Number: " & crdnumber
            Debug.Print "City: " & city
            Debug.Print "State: " & State
        Else
            Debug.Print "Error fetching data for " & firstName & " " & lastName
        End If
    Next cell
End Sub

' Function to extract information from the response (you'll need to implement this)
Function ExtractInfo(response As String, pattern As String) As String
    ' Use regular expressions or other methods to extract relevant info
    ' Example: ExtractInfo = RegExMatch(response, pattern)
End Function