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