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

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Sort by date Sort by votes
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!
 
Upvote 0
Like this:
Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Question
Can anyone help me get my VBA code working? I have spent tons of time but can't quite fix it.
Replies
3
Views
3K
profray
P
C
  • Question
Excel Userform Listbox Search issues
Replies
3
Views
275
Akuini
Akuini
C
  • Question
How to create a macro that copy pastes a fix range into another sheet and takes into consideration a cell value
Replies
9
Views
606
Flaiban
F
K
  • Question
Help creating spreadsheet that auto updates historical stock price data
Replies
2
Views
657
krismiller1982
K
F
  • Question
Update on Automating Yahoo Finance Stock History into Excel
Replies
3
Views
1K
fred3
F

Forum statistics

Threads
1,216,095
Messages
6,128,795
Members
449,468
Latest member
AGreen17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top