If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > PC based Database Applications > Microsoft Access > How to query/download directly from Yahoo!Finance

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
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"
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Aug 2003
Posts: 123
THAT'S IT EXACTLY! AWESOME and thanks to both of you!!
Reply With Quote
  #7 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #8 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #9 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #10 (permalink)  
Old
Registered User
 
Join Date: Jan 2009
Posts: 2
cheers simon,

I will give that a go

Barry
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
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
Reply With Quote
  #12 (permalink)  
Old
Registered User
 
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.
Reply With Quote
  #13 (permalink)  
Old
Registered User
 
Join Date: Sep 2010
Posts: 1
nevermind... thanks for the info!

Last edited by slam19s; 09-20-10 at 02:57.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On