Improve company productivity with a Business Account.Sign Up

x
?
Solved

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

Posted on 2011-02-24
3
Medium Priority
?
301 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)
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 2000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

What to do if a split doesn't fit? Or a bunch of invoice lines must be rounded while the sum must match a total? It takes a little, but - when done - it is extremely easy to implement.
I tried to use the SharePoint app to Import a Spreadsheet and import an Excel sheet into a Team site made in SharePoint 2016. But that just resulted in getting an error message 'Unknown Error'...
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

595 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