Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

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

Posted on 2011-02-24
Medium Priority
296 Views
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?
0
Question by:shaolinfunk
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 81

Expert Comment

ID: 34976383
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)
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_OPEN_TYPE_PRECONFIG = 0
Private Const INTERNET_FLAG_PRAGMA_NOCACHE = &H100
Private Const INTERNET_OPTION_RECEIVE_TIMEOUT As Long = 6

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 dwFlags As Long, _
ByVal dwContext As Long _
) As Long

Private Declare Function InternetReadFile Lib "wininet" ( _
ByVal hFile As Long, _
ByVal sBuffer As String, _
) 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
'
'
' 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 Result As Boolean
Dim InputBuffer As String * 10000
Dim PageContent As String
Dim SecurityData As String

If Len(ProxyUserID) > 0 Then
SecurityData = ProxyUserID
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
Do
InputBuffer = vbNullString
PageContent = PageContent & Left(InputBuffer, BytesRead)
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".

Kevin
0

LVL 81

Accepted Solution

zorvek (Kevin Jones) earned 2000 total points
ID: 34976395
A sample workbook.

Kevin
Q-26846705.xls
0

LVL 1

Author Closing Comment

ID: 34976484
PERFECT!
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes how to import an Outlook PST file to Office 365 using a third party product to avoid Microsoft's Azure command line tool, saving you time.
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
###### Suggested Courses
Course of the Month7 days, 13 hours left to enroll