2
\$\begingroup\$

I have been obtaining financial information on an annuity I have which is invested in multiple funds which are proprietary to the insurance company.

The asset value is not available other than on their website.

I have been obtaining the information using VBA and Internet Explorer. Due to the rumor that IE will be discontinued in Windows 11, I am trying to implement a Selenium-VBA solution. This is my first time working with Selenium.

The posted code seems to work OK in both Edge and Chrome, but I'd appreciate someone more knowledgeable critiquing it. The biggest problem I run into is that the site somewhat randomly, but frequently, presents one or multiple CAPTCHA's consisting of a nine-segment set of pictures that have to be analyzed. This may take several minutes to run through. With Selenium, I am using the timeout argument when finding an element, and that seems to take care of that problem without inducing unneeded delays.

Thank you for taking a look at this.

Unfortunately, because of the confidentiality of the information, I cannot provide URL/credentials that will function. If that makes this question off-topic for this forum, I will withdraw it.

Option Explicit
'reference Selenium type library
Sub test2()
    Dim dr As Selenium.ChromeDriver
    Dim Username As Selenium.WebElement, Password As Selenium.WebElement, Login As Selenium.WebElement
    Dim roth As Selenium.WebElement, details As Selenium.WebElement, fundView As Selenium.WebElement
    Dim funds As Selenium.WebElements, EL As Selenium.WebElement
    Dim fundsText As Collection
    Const sURL As String = "https://www.nationwide.com/access/web/login.htm"
    
    'just a throwaway for testing
    Dim v

Set dr = New Selenium.ChromeDriver
With dr
    .Start "chrome"
    .Get sURL
        
    Set Username = .FindElementByCss("input#user.text-field", timeout:=20000)
    Set Password = .FindElementByCss("input#password.text-field", timeout:=20000)
    Set Login = .FindElementByName("submitBtn", timeout:=20000)

    Username.SendKeys "xxxxxxxxxx"
    Password.SendKeys "xxxxxxxxxx"
    Login.Click

'this can take a long time as there are intermittenly complex CAPTCHA's to solve
' with multiple instances of 9 picture frames at a time to analyze
    Set roth = .FindElementById("xxxxxxxxxx", timeout:=300000)
    roth.Click

    Set details = .FindElementByXPath("//*[@id=""skip-main-content""]/div/section/div[3]/div[4]/a[1]", timeout:=20000)
    details.Click
    
    Set fundView = .FindElementByCss("#viewByFund", timeout:=20000)
    fundView.Click
    
    'Also display the short fund name
    .FindElementByCss("#displayShortFundName", timeout:=5000).Click
    
    Set funds = .FindElementsByXPath("//*[@id='fundByFundOnly']/tbody/child::*", timeout:=20000)
    
    'store results of the scraping into a collection
    Set fundsText = New Collection
        For Each EL In funds
            fundsText.Add EL.Text
        Next EL
    .Close
End With

Stop

'write the results to a worksheet for review
'will be processing differently when "for real"
Dim r As Range, i As Long
Set r = Sheet1.Cells(1, 1)
For i = 1 To fundsText.Count
    r(i) = fundsText(i)
Next i
End Sub

```
\$\endgroup\$

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.