Solved

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

Posted on 2011-02-24
3
287 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
  • 2
3 Comments
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
Comment Utility
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
Comment Utility
A sample workbook.

Kevin
Q-26846705.xls
0
 
LVL 1

Author Closing Comment

by:shaolinfunk
Comment Utility
PERFECT!
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Outlook Free & Paid Tools
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

772 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now