Investor's Database

[PC User]

There's code for an Excel spreadsheet that I would like to use in an Access database. It downloads stock quotes from Yahoo! Its from this website: Creating an Automated Stock Trading System using Microsoft® Excel® I need advice on how to get this go work in MS Access.

Sub GetStock(ByVal stockSymbol As String, 
ByVal StartDate As Date, ByVal EndDate As Date)
    
  Dim DownloadURL As String
  Dim StartMonth, StartDay, StartYear As String 
  Dim EndMonth, EndDay, EndYear As String
  StartMonth = Format(Month(StartDate)-1, "00")
  StartDay = Format(Day(StartDate), "00")
  StartYear = Format(Year(StartDate), "00")
    
  EndMonth = Format(Month(EndDate)-1, "00")
  EndDay = Format(Day(EndDate), "00")
  EndYear = Format(Year(EndDate), "00")
  DownloadURL="URL;http://table.finance.yahoo.com/table.csv?s=" 
                            + stockSymbol 
                            + "&a=" + StartMonth + "&b=" 
                            + StartDay + "&c=" + StartYear 
                            + "&d=" + EndMonth + "&e=" 
                            + EndDay + "&f=" 
                            + EndYear + "&g=d&ignore=.csv"
    
  With ActiveSheet.QueryTables.Add(Connection:=DownloadURL, 
         Destination:=Range("$A$1"))

        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .WebSelectionType = xlSpecifiedTables
        .WebFormatting = xlWebFormattingNone
        .WebTables = "20"
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
  End With
  ActiveWindow.SmallScroll Down:=-12
  Columns("A:A").TextToColumns Destination:=Range("A1"),_
        DataType:=xlDelimited, 
        TextQualifier:=xlDoubleQuote,_
        ConsecutiveDelimiter:=False,_
        Tab:=True,_ 
        Semicolon:=False, Comma:=True, Space:=False,_
        Other:=False,_ 
        FieldInfo:=Array(Array(1, 1), Array(2, 1),_
        Array(3, 1), Array(4, 1),_
        Array(5, 1), Array(6, 1), Array(7, 1))
  Columns("A:F").EntireColumn.AutoFit
End Sub

Sub Download()
   Call GetStock("YHOO", "02/01/2007", "09/05/2008")
End Sub

Thanks,
PC User

 

[PC User]

I see that this forum has a sample database that possibly can be used to chart stock data A Line Charting VBA Syntax Demo. If I could get the Excel code to work in MS Access, I might be able to utilize the code in this demo. Does anyone know how to translate code for Excel into code for Access?

Thanks,
PC User

 

[gemma-the-husky]

not sure how to start

plugging the url into a internet browser asks you whether you want to download/or open a csv, which contains the data yuo need

i presume the next line in excel - actually opens the table immediately - but I am not sure what the access equivalent is.

Can anyone help on this?

this bit, i think

With ActiveSheet.QueryTables.Add(Connection:=DownloadURL,
Destination:=Range("$A$1"))

 

[PC User]

I'm wondering if I should use DAO or ADO in my code. Its not clear what the Excel code is doing with the "Connection" command. In ADO this command is used to connect with an internal network and its possible that now its being used to connect to an external network. Does anyone have ideas on how this can be used in Access to download data to an Access table?

DownloadURL="URL;http://table.finance.yahoo.com/table.csv?s=" 
                            + stockSymbol 
                            + "&a=" + StartMonth + "&b=" 
                            + StartDay + "&c=" + StartYear 
                            + "&d=" + EndMonth + "&e=" 
                            + EndDay + "&f=" 
                            + EndYear + "&g=d&ignore=.csv"
    
  With ActiveSheet.QueryTables.Add(Connection:=DownloadURL, 
         Destination:=Range("$A$1")

 

[PC User]

I'm having trouble with the ADO connection and I was wondering if someone can help?

Public Function DownloadData(Symbol As String, StartDate As String, EndDate As String)
    Dim DownloadURL, stockSymbol As String
    Dim StartMonth, StartDay, StartYear As String
    Dim EndMonth, EndDay, EndYear As String
    Dim sSymbol, sOpen, sHigh, sLow, sClose, sVolume, sAdjustedClose As String


    Dim db As ADODB.Connection
    Dim rs As ADODB.Recordset

    StartMonth = Format(Month(StartDate) - 1, "00")
    StartDay = Format(Day(StartDate), "00")
    StartYear = Format(Year(StartDate), "00")
      
    EndMonth = Format(Month(EndDate) - 1, "00")
    EndDay = Format(Day(EndDate), "00")
    EndYear = Format(Year(EndDate), "00")
    
    DownloadURL = "http://table.finance.yahoo.com/table.csv?s=" & _
                    Symbol & _
                    "&a=" & StartMonth & _
                    "&b=" & StartDay & _
                    "&c=" & StartYear & _
                    "&d=" & EndMonth & _
                    "&e=" & EndDay & _
                    "&f=" & EndYear & _
                    "&g=d&ignore=.csv"
                    
    Set db = New ADODB.Connection
    DoCmd.SetWarnings False
    db.ConnectionString = DownloadURL
    Set rs = New ADODB.Recordset
    rs.Open "tblYahooData", db, adOpenDynamic, adLockOptimistic

  With rs
        .Symbol = Symbol
        .Open = sOpen
        .High = sHigh
        .Low = sLow
        .Close = sClose
        .Volume = sVolume
        .AdjustedClose = sAdjustedClose
  End With
    Set db = Nothing

End Function

' Example: http://table.finance.yahoo.com/table.csv?s=YHOO&a=01&b=01&c=2007&d=08&e=05&f=2008&g=d&ignore=.csv

 

[gemma-the-husky]

have you been able to get the csv to open in memory, as it were?

i havent had time to look to be honest - i just plugged the assembled url into IE, to see what would happen, and the data comes across.

i have similar stuff that scrapes google, although from memory the google url shows the data on the screen, rather than as a downmload - anyway, i will see what that does.

 

[PC User]

This seems to work. Try this. Let me know if you have any questions.

Option Compare Database
Option Explicit

Private Function RequestWebData(ByVal pstrURL As String) As String
    'http://www.emoreau.com/Entries/Articles/2004/03/Using-The-WebRequest-object-to-retrieve-Yahoo-stock-quotes.aspx
    Dim objWReq As WebRequest
    Dim objWResp As WebResponse
    Dim strBuffer As String

    'Contact the website
    objWReq = HttpWebRequest.Create(pstrURL)
    objWResp = objWReq.GetResponse()

    'Read the answer from the Web site and store it into a stream
    Dim objSR As StreamReader
    objSR = New StreamReader(objWResp.GetResponseStream)
    strBuffer = objSR.ReadToEnd
    objSR.Close()

    objWResp.Close()

    Return strBuffer
    MessageBox.Show (RequestWebData("http://www.utmag.com"))
    MessageBox.Show (RequestWebData("http://www.YourServer.com/YourFile.txt"))
    
End Function



Public Function GetQuoteLatest(ByVal pstrSymbol As String) As String
    Dim strURL As String
    Dim strBuffer As String

    'Creates the request URL for Yahoo
    strURL = "http://quote.yahoo.com/d/quotes.csv?" & _
             "s=" & pstrSymbol & _
             "&d=t" & _
             "&f=sl1d1t1c1ohgvj1pp2wern"

    strBuffer = RequestWebData(strURL)

    'Loop through the lines returned and transform it to a XML string
    Dim strReturn As New System.Text.StringBuilder
    strReturn.Append ("" & Environment.NewLine)
    For Each strLine As String In strBuffer.Split(ControlChars.Lf)
        If strLine.Length > 0 Then
            strReturn.Append (TransformLatestLine(strLine) & Environment.NewLine)
        End If
    Next
    strReturn.Append ("" & Environment.NewLine)

    Return strReturn.ToString
End Function

Private Function TransformLatestLine(ByVal pstrLine As String) As String
    Dim arrLine() As String
    Dim strXML As New System.Text.StringBuilder

    arrLine = pstrLine.Split(","c)

    strXML.Append ("")
    strXML.Append ("" & arrLine(0).Replace(Chr(34), "") & "")
    strXML.Append ("" & arrLine(1) & "")
    strXML.Append ("" & arrLine(2).Replace(Chr(34), "") & "")
    strXML.Append ("" & arrLine(3).Replace(Chr(34), "") & "")
    strXML.Append ("" & arrLine(4) & "")
    strXML.Append ("" & arrLine(5) & "")
    strXML.Append ("" & arrLine(6) & "")
    strXML.Append ("" & arrLine(7) & "")
    strXML.Append ("" & arrLine(8) & "")
    strXML.Append ("" & arrLine(9) & "")
    strXML.Append ("" & arrLine(10) & "")
    strXML.Append ("" & arrLine(11).Replace(Chr(34), "") & "")
    strXML.Append ("" & arrLine(12).Replace(Chr(34), "") & "")
    strXML.Append ("" & arrLine(13) & "")
    strXML.Append ("" & arrLine(14) & "")
    strXML.Append ("" & arrLine(15).Replace(Chr(34), "") & "")
    strXML.Append ("")
    Return strXML.ToString
End Function