I took a look at Speed up processing between VBA and IE, but I didn't see anything that addressed the speeding up of the retrieval of information via IE.

Since MS got rid of the stock retrieval database (MSN Money), there wasn't an easy way to get current stock prices. From what I gather, there are some add-ins available through the app store, but like usual, I'm stuck on 2007 doing this.

This was my attempt at a UDF for retrieving the stock price from http://www.msn.com/en-us/money/ which requires finding elements on the page and getting the value from within. It's.. not very elegant, but it works. What can I do to speed up the IE processes? I know, I know - screenupdating, etc

Option Explicit

Public Function GetTickerPrice(ByVal ticker As String) As Double

    Dim IE As Object
    Dim pageData As Object

    Set IE = CreateObject("InternetExplorer.Application")
    Dim URL As String
    URL = "http://www.msn.com/en-us/money/stockdetails/fi-126.1." & ticker & ".NAS?symbol=" & ticker & "=PRFIMQ"

    IE.Navigate URL

     Do Until IE.ReadyState >= 4
            DoEvents
     Loop

     Application.Wait Now + TimeSerial(0, 0, 5)

     Set pageData = IE.document

     GetTickerPrice = pageData.getElementsByClassName("precurrentvalue")(0).innertext

     Set IE = Nothing
     Set pageData = Nothing
End Function
  • 2
    Maybe using an API is cleaner? The speed issue is very likely because of the time needed to open the URL: instead you could just fetch the HTML page (without displaying it in IT) and search the price. If it is not fast enough, you might need to perform concurrent http requests (for the different stock prices) – oliverpool Mar 2 '16 at 12:38
  • @oliverpool maybe, when I was messing around with the API, I wasn't sure if I could do anything with the csv without downloading and opening it, which may or may not be faster. – Raystafarian Mar 2 '16 at 12:40
  • I think this is one of the rare cases when your question would be better asked on SO. Namely, something like "Fastest way to load and retrieve element values from a webpage using VBA" – Kaz Mar 2 '16 at 12:58
  • 1
    Why are you using VBA at all... support.office.com/en-us/article/… (let alone automating IE) – James Snell Mar 2 '16 at 13:31
  • @JamesSnell I thought that the msn money connections were deprecated in 2013. – Raystafarian Mar 2 '16 at 13:35
up vote 4 down vote accepted

Worksheet functions were not designed for this. User-defined worksheet functions were not made a feature of Excel to go and fetch data online - in my humble opinion, this is a terrible abuse of UDF's.

A function like this should be part of the definition of a class module that defines an object responsible for this task, and wrapped with an interface, say ITickerPriceProvider:

Public Function GetTickerPrice(ByVal ticker As String) As Double
End Function

Then your code moves into a class module with Implements ITickerPriceProvider, and the signature becomes this:

Option Explicit
Implements ITickerPriceProvider

Private Function ITickerPriceProvider_GetTickerPrice(ByVal ticker As String) As Double
    'your code
End Function

What gives? Now you can get fancy and write a macro that looks like this:

Public Sub UpdateTickerPrices()

    Dim provider As ITickerPriceProvider
    Set provider = New WebTickerPriceProvider

    Dim updater As TickerSheetUpdater
    Set updater = New TickerSheetUpdater

    updater.UpdateTickerPrices provider, TickerPricesSheet

End Sub

And the TickerSheetUpdater.UpdateTickerPrices method is where you go and implement the code that locates ticker strings (your worksheet must have a column with those, right?), fetches the prices for each one, and updates the worksheet.

This is a job for a macro, not a UDF.

As a bonus, the TickerSheetUpdater class can be unit-tested because the dependencies are under control - and now if somebody cuts the network cable, you can handle errors gracefully, instead of having 200 UDF calls blowing up one after another.


As for the implementation itself, I don't see any glaring issues. I would have named IE something like browser, URL would have been url, and there would have been an On Error GoTo CleanFail statement at the top, and a CleanExit label to ensure resources get cleaned up whether or not an error occurs:

Private Function ITickerPriceProvider_GetTickerPrice(ByVal ticker As String) As Double
    On Error GoTo CleanFail

    'implementation here

CleanExit:
    Set browser = Nothing
    Set pageData = Nothing
    Exit Function

CleanFail:
    'handle errors
    Resume CleanExit

End Function

Turning ScreenUpdating off isn't going to help much, at least not with a UDF approach - the function isn't updating any cell values, it is a cell value.

However I'd try to see if there wouldn't be a way to remove this:

Application.Wait Now + TimeSerial(0, 0, 5)

...or add a comment that explains why it's needed, and why it needs to be a whole 5 seconds - especially given you've just waited for a specific ReadyState on your IE object:

 Do Until IE.ReadyState >= 4
        DoEvents
 Loop

What's 4 anyway? Make an Enum for the possible values, and use it instead of hard-coding a value like this. Why do you need to wait 5 seconds after the browser gets into that ReadyState? Could you keep loop-waiting for a later state value? An enum would help understanding what's going on and why here.

  • Excellent review and points, thank you. – Raystafarian Mar 2 '16 at 16:05
  • @Raystafarian notice it leaves you the freedom to implement some XmlTickerPriceProvider and/or CsvTickerPriceProvider and/or SqlTickerPriceProvider, or whatever other way you might want to fetch this data =) – Mathieu Guindon Mar 2 '16 at 17:49
  • It's a better solution than mine, for sure. You might want to post it over at the superuser question I was attempting to solve for the bounty. – Raystafarian Mar 2 '16 at 17:51

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.