• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 298
  • Last Modified:

How do I use Data Query from Web to bring data into Excel cells?

I have a list of 5 stocks whose stock prices I want to put into cells B2 through B6.  I know how to import data "From Web" using a web query with this address (I made one for each of the 5 stocks):


How do I make it so that if I add more stock symbols to my excel worksheet in column B, and hit an "update macro button" (i know how to create this) it will run a macro that displays the retrieved value from the above URL into the A column?  
  • 2
1 Solution
zorvek (Kevin Jones)ConsultantCommented:
The function below pulls a company's stock price from the Yahoo Finance website. The function requires the module modInternetRoutines.

Public Function GetStockPrice( _
      ByVal CompanyTicker As String _
   ) As Double
' Pull a company's stock price from the Yahoo Finance website given it's ticker.

   Dim PageContent As String
   Dim StartPos As Long
   Dim EndPos As Long
   Dim Internet As New clsInternetConnection

   PageContent = Internet.GetWebPage("http://finance.yahoo.com/q?s=" & CompanyTicker)
   StartPos = InStr(PageContent, ">Last Trade:</th><td")
   If StartPos > 0 Then
      StartPos = InStr(StartPos, PageContent, "<big><b><span id=")
      If StartPos > 0 Then
         StartPos = InStr(StartPos + 17, PageContent, ">") + 1
         EndPos = InStr(StartPos, PageContent, "<")
         If EndPos > 0 Then
            GetStockPrice = CDbl(Mid(PageContent, StartPos, EndPos - StartPos))
         End If
      End If
   End If

End Function

To add a UDF or User Defined Function to a workbook for use in cell functions, press ALT+F11 to open the VBA development environment (VBE). Press CTRL+R to open the VBA project explorer. Select the menu command Insert->Module to create a new VBA module. Paste the UDF code into the document window that appears. Press ALT+F11 to return to the Excel workbook. Do not place UDFs inside a worksheet module or the workbook code module (ThisWorkbook) - Excel will not find them there.

You will need this class:

' clsInternetConnection
' Class provides functionalty to read web pages.
' Implemented as a class module for use with any VB or VBA project.
' © 2008-2011 Kevin M. Jones

Option Explicit

Private Const INTERNET_FLAG_RELOAD = &H80000000

Private Const UserAgent = "VB"

Private mConnectionHandle As Long
Private mTimeoutSeconds As Long

Private Declare Function InternetCloseHandle Lib "wininet" ( _
      ByRef hInet As Long _
   ) As Long

Private Declare Function InternetOpen Lib "wininet" Alias "InternetOpenA" ( _
      ByVal sAgent As String, _
      ByVal lAccessType As Long, _
      ByVal sProxyName As String, _
      ByVal sProxyBypass As String, _
      ByVal lFlags As Long _
   ) As Long

Private Declare Function InternetOpenUrl Lib "wininet" Alias "InternetOpenUrlA" ( _
      ByVal hInternetSession As Long, _
      ByVal lpszUrl As String, _
      ByVal lpszHeaders As String, _
      ByVal dwHeadersLength As Long, _
      ByVal dwFlags As Long, _
      ByVal dwContext As Long _
) As Long

Private Declare Function InternetReadFile Lib "wininet" ( _
      ByVal hFile As Long, _
      ByVal sBuffer As String, _
      ByVal lNumBytesToRead As Long, _
      lNumberOfBytesRead As Long _
   ) As Integer
Private Declare Function InternetSetOption Lib "wininet.dll" Alias "InternetSetOptionA" ( _
      ByVal hInternet As Long, _
      ByVal dwOption As Long, _
      ByRef lpBuffer As Any, _
      ByVal dwBufferLength As Long _
   ) As Long

Private Sub Class_Initialize()

   mConnectionHandle = InternetOpen(UserAgent, INTERNET_OPEN_TYPE_PRECONFIG, vbNullString, vbNullString, 0)
   If mConnectionHandle = 0 Then
      MsgBox "Unable to open an Internet connection. The most likely cause is too many handles have been created."
   End If
   mTimeoutSeconds = 30

End Sub

Private Sub Class_Terminate()

   InternetCloseHandle mConnectionHandle
End Sub

Public Function GetWebPage( _
      ByVal URL As String, _
      Optional ByVal ProxyUserID As String, _
      Optional ByVal ProxyPassword As String, _
      Optional ByVal TimeoutSeconds As Long _
   ) As String

' Get a web page.
' Syntax
' GetWebPage(URL, [ProxyUserID], [ProxyPassword])
' URL - A complete URL to a web page.
' ProxyUserID - The user ID for the proxy server, if any. Optional. If omitted
'   then no proxy server is assumed.
' ProxyPassword - The password for the proxy server. Optional. Ignored if
'   ProxyUserID is omitted.
' TimeoutSeconds - The number of seconds to wait until timing out. Optional. If
'   omitted then 30 seconds is used.

   Dim ConnectionHandle As Long
   Dim URLHandle As Long
   Dim BytesRead As Long
   Dim Result As Boolean
   Dim InputBuffer As String * 10000
   Dim PageContent As String
   Dim SecurityData As String
   If Len(ProxyUserID) > 0 Then
      SecurityData = ProxyUserID
      If Len(ProxyPassword) > 0 Then
         SecurityData = SecurityData & ":" & ProxyPassword
      End If
      SecurityData = SecurityData & "@"
      URL = Replace(URL, "://", "://" & SecurityData)
   End If
   If TimeoutSeconds = 0 Then TimeoutSeconds = mTimeoutSeconds
   If mConnectionHandle <> 0 Then
      Result = InternetSetOption(mConnectionHandle, INTERNET_OPTION_RECEIVE_TIMEOUT, TimeoutSeconds * 1000, Len(TimeoutSeconds))
      URLHandle = InternetOpenUrl(mConnectionHandle, URL, vbNullString, 0, INTERNET_FLAG_RELOAD Or INTERNET_FLAG_PRAGMA_NOCACHE, 0)
      If URLHandle <> 0 Then
            InputBuffer = vbNullString
            Result = InternetReadFile(URLHandle, InputBuffer, Len(InputBuffer), BytesRead)
            PageContent = PageContent & Left(InputBuffer, BytesRead)
         Loop While BytesRead > 0
         InternetCloseHandle URLHandle
      End If
   End If
   GetWebPage = PageContent
End Function

Public Property Get ValidInternetConnection() As Boolean

   ValidInternetConnection = mConnectionHandle <> 0

End Property

Public Property Get TimeoutSeconds() As Long

   TimeoutSeconds = mTimeoutSeconds

End Property

Public Property Let TimeoutSeconds( _
      ByVal Value As Long _

   mTimeoutSeconds = Value

End Property

Create a new class and add the above class code to it. Name the class "clsInternetConnection".

zorvek (Kevin Jones)ConsultantCommented:
A sample workbook.

shaolinfunkAuthor Commented:

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now