Results 1 to 13 of 13
  1. #1
    Join Date
    Aug 2003
    Posts
    123

    Question How to query/download directly from Yahoo!Finance

    How can I directly query or download historical stock prices from Yahoo Finance? For instance, the following URL brings up a spreadsheet/csv in a browser window. I can copy or save it into Excel and then import that into Access. But I would like to be able to import directly from the web page into Access through VBA.

    http://ichart.finance.yahoo.com/tabl...=m&ignore=.csv

    I would like use the same URL each time but change only the parameter at "s=" to a different ticker symbol.

  2. #2
    Join Date
    Aug 2006
    Posts
    559
    Depends on the system and what you want to achieve after the opening.
    If the default browser is set up to be IE than you can just use the FollowHyperlink method e.g.:

    Application.FollowHyperlink Me.YourControl

    Where YourControl is the control which holds the complete URL. If IE is not necessarily the default browser or you want to automate IE further after opening it you could use automation code with the CreateObject() method e.g.:

    Set objIE = CreateObject("InternetExplorer.Application")
    objIE.Visible = True
    objIE.Navigate Me.YourControl
    ...

    Make sure to clean up the code correctly afterward it runs and you fully declare variables. The above is just a small sample.


    *&*&*&*&*&*&*&*&*&*&*&*&*&*

    OR:

    You can use the Microsoft Web Browser ActiveX control which ships with Access. To use it on your form

    Insert-->ActiveX Control-->Microsoft Web Browser

    Me.YourWebBrowserControl.Navigate URL:="www.dbforums.com"

  3. #3
    Join Date
    Aug 2003
    Posts
    123
    Thanks, that gets me part way there. But the real trick is that I would like to go to the URL and import the data directly into an Access table behind the scenes--so that an IE window is not necessarily visible while this goes on.

    Just following the hyperlink opens up an IE window with the spreadsheet within the window.

  4. #4
    Join Date
    Aug 2006
    Posts
    559
    I think that the only way you'll be able to do that is by using ASP. I will continue to fiddle with it and see though.

  5. #5
    Join Date
    Feb 2004
    Posts
    137
    I understand exactly what you are looking for. The following function will do what you ask:
    Code:
    Public Sub http_Retrieve(ByVal sHttp As String, ByVal sTable As String)
    
        Dim XMLHTTP As Object
        Dim byteData() As Byte
        Dim td As TableDef
        Dim ff As Integer
    
        ' Retrieve the file from the specified URL
        Set XMLHTTP = CreateObject("MSXML2.XMLHTTP")
        XMLHTTP.Open "GET", sHttp, False
        XMLHTTP.send
        byteData = XMLHTTP.responseBody
        Set XMLHTTP = Nothing
    
        ' Write the data to a temporary CSV file
        ff = FreeFile
        Open Environ("Temp") & "\tmp.csv" For Binary Access Write As ff
            Put #ff, , byteData
        Close #1
    
        ' Delete the specified table if it already exists in the database
        For Each td In DBEngine(0)(0).TableDefs
            If td.Name = sTable Then
                DBEngine(0)(0).TableDefs.Delete sTable
            End If
        Next td
    
        ' Import the temporary CSV file into a new instance
        ' of the specified table in the database
        DoCmd.TransferText acImportDelim, , sTable, _
            Environ("Temp") & "\tmp.csv", True
    
        ' Delete the temporary CSV file
        Kill Environ("Temp") & "\tmp.csv"
    
    End Sub
    For example, to use this function to import the file from the URL you provided into a table in your database named MyTable:


    http_Retrieve "http://ichart.finance.yahoo.com/table.csv?s=CLMS&a=03&b=1&c=1997&d=03&e=30&f=2007& g=m&ignore=.csv", "MyTable"


    See if this solution works for you.

  6. #6
    Join Date
    Aug 2003
    Posts
    123
    THAT'S IT EXACTLY! AWESOME and thanks to both of you!!

  7. #7
    Join Date
    Aug 2003
    Posts
    123
    Matthew, found an issue with the table delete section. The code is not picking up the sTable when it's cycling through the TableDefs. For instance, I'm using the name "temp_prc", which in the list of objects should come right after another table "sys_tool".

    But then the For..Next gets to "sys_tool", it ends and never makes it to "temp_prc"--which is the last table. So then the downloaded prices just keep getting added to the existing "temp_prc" table, rather than deleting the existing table and creating a new one.

    ' Delete the specified table if it already exists in the database
    For Each td In DBEngine(0)(0).TableDefs
    If td.Name = sTable Then
    DBEngine(0)(0).TableDefs.Delete sTable
    End If
    Next td

  8. #8
    Join Date
    Jan 2009
    Posts
    2

    Yahoo finance delayed stock quote to MySql D/Base using C#

    Quote Originally Posted by Matthew Reeves
    I understand exactly what you are looking for. The following function will do what you ask:
    Code:
    Public Sub http_Retrieve(ByVal sHttp As String, ByVal sTable As String)
    
        Dim XMLHTTP As Object
        Dim byteData() As Byte
        Dim td As TableDef
        Dim ff As Integer
    
        ' Retrieve the file from the specified URL
        Set XMLHTTP = CreateObject("MSXML2.XMLHTTP")
        XMLHTTP.Open "GET", sHttp, False
        XMLHTTP.send
        byteData = XMLHTTP.responseBody
        Set XMLHTTP = Nothing
    
        ' Write the data to a temporary CSV file
        ff = FreeFile
        Open Environ("Temp") & "\tmp.csv" For Binary Access Write As ff
            Put #ff, , byteData
        Close #1
    
        ' Delete the specified table if it already exists in the database
        For Each td In DBEngine(0)(0).TableDefs
            If td.Name = sTable Then
                DBEngine(0)(0).TableDefs.Delete sTable
            End If
        Next td
    
        ' Import the temporary CSV file into a new instance
        ' of the specified table in the database
        DoCmd.TransferText acImportDelim, , sTable, _
            Environ("Temp") & "\tmp.csv", True
    
        ' Delete the temporary CSV file
        Kill Environ("Temp") & "\tmp.csv"
    
    End Sub
    For example, to use this function to import the file from the URL you provided into a table in your database named MyTable:


    http_Retrieve "http://ichart.finance.yahoo.com/table.csv?s=CLMS&a=03&b=1&c=1997&d=03&e=30&f=2007& g=m&ignore=.csv", "MyTable"


    See if this solution works for you.




    Can anybody show me how to develop this in C# tying it to a MySql d/base using Visual Studio '08

    I understand most of the code and consider the likely changes are changing the variable declarations & changing the keyword "sub" to "void" for c#

    I am developing a virtual stock market portfolio creator in asp.net for a final year project in University and would appreciate anything you can do for me

    Regards

    Finbarr
    Last edited by Finbarr Sean; 01-30-09 at 01:23. Reason: type errors

  9. #9
    Join Date
    Sep 2006
    Posts
    265
    I was doing some research into the File System Object and I received the impression that there are no C# classes for this type of Object. I did try converting your script on a site without success. This site converts either VB to C# or C# to VB.

    Convert C# to VB.NET - A free code conversion tool - developer Fusion - Visual Basic, C# Programming, ASP.NET, .NET Framework and Java Tutorials

    Simon

  10. #10
    Join Date
    Jan 2009
    Posts
    2
    cheers simon,

    I will give that a go

    Barry

  11. #11
    Join Date
    Dec 2009
    Posts
    1
    Hi,
    You can also take a useful library at codeproject.com for downloading stock quotes from yahoo.
    Look here: Yahoo! Finance Managed

    Steven

  12. #12
    Join Date
    Jan 2004
    Location
    California, USA
    Posts
    25
    Yahoo! Finance Managed
    Nice reference; however, is this code useable in MS Access? Let's identify our references to codes other than VBA and which are useable to answer the question we are reading.
    I think this code may answer the original question. This code is from a posting by Albert D. Kallal - 18 Nov 2009 17:05.
    http://www.accessmonster.com/Uwe/For...-Call-from-VBA
    Last edited by PC User; 03-27-10 at 11:57.

  13. #13
    Join Date
    Sep 2010
    Posts
    1
    nevermind... thanks for the info!
    Last edited by slam19s; 09-20-10 at 02:57.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •