Trying to find a way to pull current gold price on a cell in spreadsheet (Excel 2010)

[hawkeyex]

I'm trying to use the Web Query but I'm having a hard time trying to get the gold price correctly.

I used Yahoo's Finance info as found here: http://finance.yahoo.com/q?s=ZGQ12.CBT

How can I do this correctly?

 

[pbornemeier]

The price is not in a place where you can pull just that piece of data. I used the Data | From Web in Excel 2007 and retrieved the entire page to a worksheet I named 'Web'.
On another worksheet the following formulas:
A1 =(INDEX(Web!A:A,(MATCH("-CBOT ",Web!A:A,0))+2))
A2 =1*LEFT(A1,FIND(" ",A1)-1)

Returned the result in cell A2.
The position of the cell containing the downloaded gold price on the Web worksheet changed a few times, so that is why I used the INDEX/MATCH formula rather than using a hardcoded location.
If the relationship between "=CBOT " and the price changes, then this method will fail.

Please note that Yahoo! may have a clause in their sevice agreement that forbids automated data pulls. I know google does, but am not sure about Yahoo!

 

[Kyle123]

Like this:

Public Function GetGoldPrice() As Double
With CreateObject("MSXML2.XMLHTTP")
    .Open "GET", "http://finance.yahoo.com/q?s=ZGQ12.CBT"
    .send
    Do: DoEvents: Loop Until .readyState = 4
    GetGoldPrice = Val(Replace(Split(.ResponseText, "yfs_l10_zgq12.cbt"">")(1), ",", ""))
    .abort
End With
End Function

 

[pbornemeier]

That is quite nifty. Thanks for the lesson.

 

[laurajohnson]

This Excel spreadsheet pulls in historical prices for silver and gold in a range of currencies.

 

[marious]

DontFretBret has a how to video explaining how to extract data from yahoo finance using vba in excel and he is excellent explaining VBA Excel VBA - Get Stock Quotes from Yahoo Finance API - YouTube