Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

kitco.com provides a realtime price quote for gold and other metals. I have a spreadsheet which makes calculations based on the price of gold and would like for this realtime value to automatically be updated on my excel sheet. I tried 'get external data' from a website but that didn't work. any ideas?

EDIT ADDED: Kitco has a gadget called KCAST which displays realtime quotes on the Windows taskbar. I tried capturing those values from the taskbar but that didn't work either. Maybe if Kitco provided an API or feed, it could be done?

share|improve this question

migrated from stackoverflow.com Nov 1 '10 at 4:30

This question came from our site for professional and enthusiast programmers.

    
I can suggest a method, but first I need to know which is the exact web page you are interested in. –  Toc Nov 2 '10 at 23:07
    
@Toc: kitco.com –  FrankComputerAtYmailDotCom Dec 18 '10 at 16:13
    
After your edit, I think that updating quotation were simpler if you would use Google Docs instead of Excel. –  Toc Dec 23 '10 at 16:19

3 Answers 3

up vote 4 down vote accepted

You can do this with a web query in excel 2003.It does work. select the table on the left of their page.

alt text

Then import to the excel range you want. You can either refresh as you want, or setup a macro to handle that pretty easily. This gives you Bid and Ask prices which import into two separate fields so you should be able to use either as your live number.

share|improve this answer
    
actually forgot you can set the refresh rate in the data range properties after you insert the web query. Seems like the lowest refresh rate is every minute –  datatoo Nov 12 '10 at 15:16

If you can program in Visual Basic for Applications, the macro language of Excel, you will want to use the ActiveX XMLHttpRequest object provided by Internet Explorer to access arbitrary URLs.

Then, although it is not the strictly "correct" way of doing it, you can create a regex to extract the gold price from the web page's source code. Finally, update the correct cell.

share|improve this answer
    
ok, I will explore this method. thank you! –  FrankComputerAtYmailDotCom Oct 31 '10 at 4:24

Use the Google Docs Import function below.

Note that the table number may change; find the correct table number by searching page source after finding the table data your want (i.e. gold spot). Table numbers are shown in the search results of find in the Chrome browser (i.e. 22 of 24 </table> using the CTRL-F find function.

=ImportHtml("http://www.kitco.com/market/"; "table";20)
share|improve this answer
    
I dont understand. My app is written with Microsoft Excel and Access. Why Google Docs, and not Excel importing it directly?.. or you mean from GDocs to Excel, explain. –  FrankComputerAtYmailDotCom Sep 11 '11 at 4:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.