Results 1 to 7 of 7
  1. #1
    DigitalAdrenaline is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2012
    Posts
    100

    Can Access Import Stock Quotes?

    Hi, is there a VBA script that will import stock quotes from say, Yahoo, to a filtered query table? I know there's a way with Excel. I'm designing a portfolio manager. Thanks.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Yes. I'm trying to find the code. But yes.

  3. #3
    DigitalAdrenaline is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2012
    Posts
    100
    Excellent ranman. Many thanks.

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Here is some code for a form.
    the form has fields for Amt,ticker,date, and quoteType (default = 'ask')
    the code returns the price. It updates the 1 ticker.

    This COULD be modified to run from a query. The query could update an entire list of tickers.

    Code:
    Private Sub btnGet_Click()
    Dim vRet
    
    'txtAmt = YahooFinance(txtTicker, "ask")
    
    Select Case True
       Case IsNull(txtTicker)
          MsgBox "Ticker must have a value", vbCritical, "Required"
       Case IsNull(cboItem)
          MsgBox "Stock Item must have a value", vbCritical, "Required"
       Case Else
            vRet = YahooFinance(txtTicker, cboItem)
            'MsgBox vRet
            txtAmt = vRet
            txtDate = Now()
    End Select
    End Sub
    
    
    Function YahooFinance(Ticker As String, pvItem As String)
    Dim strURL As String
    Dim tag
    
    Select Case pvItem
        Case "ask"
            tag = "a"
        Case "bid"
            tag = "b"
        Case "bookvalue"
            tag = "b4"
        Case "change"
            tag = "c1"
        Case "afterhourschangerealtime"
            tag = "c8"
        Case "tradedate"
            tag = "d2"
        Case "epsestimatecurrentyear"
            tag = "e7"
        Case "floatshares"
            tag = "f6"
        Case "52weeklow"
            tag = "j"
        Case "annualizedgain"
            tag = "g3"
        Case "holdingsgainrealtime"
            tag = "g3"
        Case "marketcapitalization"
            tag = "j1"
        Case "percentchangefrom52weekhigh"
            tag = "k5"
        Case "daysrangerealtime"
            tag = "m2"
        Case "changefrom200daymovingaverage"
            tag = "m5"
        Case "percentchangefrom50daymovingaverage"
            tag = "m8"
        Case "open"
            tag = "o"
        Case "changeinpercent"
            tag = "p2"
        Case "exdividenddate"
            tag = " q"
        Case "peratiorealtime"
            tag = "r2"
        Case "priceepsestimatenextyear"
            tag = "r7"
        Case "shortratio"
            tag = "s7"
        Case "tickertrend"
            tag = "t7"
        Case "holdingsvalue"
            tag = "v1"
        Case "daysvaluechange"
            tag = "w1"
        Case "dividendyield"
            tag = "y"
        Case "averagedailyvolume"
            tag = "a2"
        Case "askrealtime"
            tag = "b2"
        Case "bidsize"
            tag = "b6"
        Case "commision"
            tag = "c3"
        Case "dividendshare"
            tag = "d"
        Case "earningspershare"
            tag = "e"
        Case "epsestimatenextyear"
            tag = "e8"
        Case "days low"
            tag = "g"
        Case "52weekhigh"
            tag = "k"
        Case "holdsingain"
            tag = "g4"
        Case "moreinfo"
            tag = "i"
        Case "marketcaprealtime"
            tag = "j3"
        Case "percentchangefrom52weeklow"
            tag = "j6"
        Case "lasttradesize"
            tag = "k3"
        Case "lasttradewithtime"
            tag = "l"
        Case "lowlimit"
            tag = "l3"
        Case "50movingaverage"
            tag = "m3"
        Case "percentchangefrom200daymovingaverage"
            tag = "m6"
        Case "name"
            tag = "n"
        Case "previousclose"
            tag = "p"
        Case "pricesales"
            tag = "p5"
        Case "peratio"
            tag = "r"
        Case "pegratio"
            tag = "r5"
        Case "symbol"
            tag = "s"
        Case "lasttradetime"
            tag = "t1"
        Case "1yeartargetprice"
            tag = "t8"
        Case "holdingsvaluerealtime"
            tag = "v7"
        Case "daysvaluechangerealtime"
            tag = "w4"
        Case "asksize"
            tag = "a5"
        Case "bidrealtime"
            tag = "b3"
        Case "change&percentchange"
            tag = "c"
        Case "changerealtime"
            tag = "c6"
        Case "lasttradedate"
            tag = "d1"
        Case "errorindication"
            tag = "e1"
        Case "epsestimatenextquarter"
            tag = "e9"
        Case "dayshigh"
            tag = "h"
        Case "holdingsgainpercent"
            tag = "g1"
        Case "holdsingsgainpercentrealtime"
            tag = "g5"
        Case "orderbookrealtime"
            tag = "i5"
        Case "ebitda"
            tag = "j4"
        Case "lasttraderealtimewithtime"
            tag = "k1"
        Case "changefrom52weekhigh"
            tag = "k4"
        Case "lasttradepriceonly"
            tag = "l1"
        Case "daysrange"
            tag = "m"
        Case "200daymovingaverage"
            tag = "m4"
        Case "notes"
            tag = "n4"
        Case "pricepaid"
            tag = "p1"
        Case "pricebook"
            tag = "p6"
        Case "dividendpaydate"
            tag = "r1"
        Case "priceepsestimatecurrentyear"
            tag = "r6"
        Case "sharesowned"
            tag = "s1"
        Case "tradelinks"
            tag = "t6"
        Case "volume"
            tag = "v"
        Case "52weekrange"
            tag = "w"
        Case "stockexchange"
            tag = "x"
        Case "changepercentrealtime"
            tag = "k2"
        Case "changefrom52weeklow"
            tag = "j5"
        Case Else
            itemFound = 0
    End Select
    
    
    If Not IsNull(cboItem) Then
        strURL = "http://download.finance.yahoo.com/d/quotes.csv?s=" & Ticker & "&f=" & tag
        Set XMLHTTP = CreateObject("MSXML2.XMLHTTP")
        XMLHTTP.Open "GET", strURL, False
        XMLHTTP.Send
        YahooFinance = XMLHTTP.responseText
        Set XMLHTTP = Nothing
        'Range("B5").Value = YahooFinance
    Else
        YahooFinance = ""
        MsgBox Ticker & " not found"
    End If
    End Function

  5. #5
    DigitalAdrenaline is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2012
    Posts
    100
    Thanks again ranman, will look at this code much closer and add that to my forms. Cheers.

  6. #6
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  7. #7
    DigitalAdrenaline is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Dec 2012
    Posts
    100
    Thanks Orange. That's great.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Stock Control via Access?
    By Mr. Twig in forum Access
    Replies: 4
    Last Post: 02-03-2015, 10:15 AM
  2. Replies: 2
    Last Post: 08-22-2014, 05:16 PM
  3. Import Text File Error with quotes being stripped
    By examart in forum Import/Export Data
    Replies: 2
    Last Post: 10-08-2012, 08:11 AM
  4. Replies: 7
    Last Post: 03-23-2012, 01:55 PM
  5. Stock Update using VBA in Access 2007
    By thanosgr in forum Access
    Replies: 19
    Last Post: 02-13-2012, 03:21 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums