Solved

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

Posted on 2011-02-24
3
293 Views
Last Modified: 2012-05-11
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):

http://download.finance.yahoo.com/d/quotes.csv?s=msft&f=p

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
Comment
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
  • Learn & ask questions
  • 2
3 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
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)
   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_OPEN_TYPE_PRECONFIG = 0
Private Const INTERNET_FLAG_RELOAD = &H80000000
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 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
         Do
            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".

Kevin
0
 
LVL 81

Accepted Solution

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

Kevin
Q-26846705.xls
0
 
LVL 1

Author Closing Comment

by:shaolinfunk
ID: 34976484
PERFECT!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question