Accurate gold coin offer price scraping for the spreadsheet nerds

Discussion of the Gold portion of the Permanent Portfolio

Moderator: Global Moderator

Post Reply
fnord123
Executive Member
Executive Member
Posts: 233
Joined: Sun Apr 25, 2010 9:33 pm

Accurate gold coin offer price scraping for the spreadsheet nerds

Post by fnord123 » Fri Jan 23, 2015 11:38 pm

Someone messaged me about a way to get accurate offer prices for physical gold coins for valuing the physical part of the gold in their PP.  Apmex posts buy prices on their web pages if you know where to look, so I put together an example that shows examples of how scrape those values into a Google spreadsheet.  Here's the link in case anybody finds it useful.

https://docs.google.com/spreadsheets/d/ ... sp=sharing

I'd request that folks don't share this around too widely in other forums, as doing so will prompt the fine folks at Apmex to start mucking with their HTML/CSS/XML/etc. and break the spreadsheet.
User avatar
Mark Leavy
Executive Member
Executive Member
Posts: 1950
Joined: Thu Mar 01, 2012 10:20 pm
Location: US Citizen, Permanent Traveler

Re: Accurate gold coin offer price scraping for the spreadsheet nerds

Post by Mark Leavy » Sat Jan 24, 2015 12:59 am

Thanks Fnord.  I've learned a lot from your spreadsheet samples.

This is what I use in my google docs:

=importhtml("http://www.coloradogold.com/selling.php",1,0)

This is in the A1 cell of a tab labeled "Gold Prices" and then all other cells refer to this tab.
fnord123
Executive Member
Executive Member
Posts: 233
Joined: Sun Apr 25, 2010 9:33 pm

Re: Accurate gold coin offer price scraping for the spreadsheet nerds

Post by fnord123 » Sat Jan 24, 2015 12:37 pm

Nice web page find there! I particularly like it when I can extract a whole table of prices rather than one by one. I suspect Google spreadsheets run out of steam when you give them too many URLs to retrieve.
User avatar
dualstow
Executive Member
Executive Member
Posts: 14225
Joined: Wed Oct 27, 2010 10:18 am
Location: synagogue of Satan
Contact:

Re: Accurate gold coin offer price scraping for the spreadsheet nerds

Post by dualstow » Sat Jan 24, 2015 12:56 pm

I don't know whether it's accurate, but I've been using the following for a long time

Code: Select all

=Index(ImportHTML("http://apmex.com/","table",1),2,2)
And I'm sure I got it from this forum.
RIP Marcello Gandini
User avatar
dualstow
Executive Member
Executive Member
Posts: 14225
Joined: Wed Oct 27, 2010 10:18 am
Location: synagogue of Satan
Contact:

Re: Accurate gold coin offer price scraping for the spreadsheet nerds

Post by dualstow » Fri Jan 30, 2015 7:55 am

..and now it's showing gold at a spot price of $0.18 this morning.  ??? Time to buy?  ;)
RIP Marcello Gandini
Khisanth
Full Member
Full Member
Posts: 82
Joined: Thu Sep 08, 2011 9:39 pm

Re: Accurate gold coin offer price scraping for the spreadsheet nerds

Post by Khisanth » Fri Jan 30, 2015 9:56 am

Sometimes APMEX has different tables or something which fixes itself later on, so I have two formulas that I switch between.

Code: Select all

=Index(ImportHTML("http://www.cmegroup.com/trading/metals/precious/gold_quotes_globex.html","table",1),3,9)
or

Code: Select all

=Index(ImportHTML("http://apmex.com/","table",1),2,2)
Right now APMEX result for that table is FEB 2015 so I've switched to the CMEGROUP formula.[/code]
User avatar
dualstow
Executive Member
Executive Member
Posts: 14225
Joined: Wed Oct 27, 2010 10:18 am
Location: synagogue of Satan
Contact:

Re: Accurate gold coin offer price scraping for the spreadsheet nerds

Post by dualstow » Fri Jan 30, 2015 9:57 am

Good to know, thanks!
RIP Marcello Gandini
User avatar
dualstow
Executive Member
Executive Member
Posts: 14225
Joined: Wed Oct 27, 2010 10:18 am
Location: synagogue of Satan
Contact:

Re: Accurate gold coin offer price scraping for the spreadsheet nerds

Post by dualstow » Tue Jun 20, 2017 10:31 am

My code isn't working, and neither is Mark's.
Anyone using a code in Google Docs that works today?
RIP Marcello Gandini
User avatar
Mark Leavy
Executive Member
Executive Member
Posts: 1950
Joined: Thu Mar 01, 2012 10:20 pm
Location: US Citizen, Permanent Traveler

Re: Accurate gold coin offer price scraping for the spreadsheet nerds

Post by Mark Leavy » Tue Jun 20, 2017 1:07 pm

dualstow wrote:My code isn't working, and neither is Mark's.
Anyone using a code in Google Docs that works today?
=importhtml("http://www.coloradogold.com/htsell.php","table",1)

This is what I have been using for awhile. The old link stopped working when Colorado Gold reformatted the tables on their web page a while back.
User avatar
dualstow
Executive Member
Executive Member
Posts: 14225
Joined: Wed Oct 27, 2010 10:18 am
Location: synagogue of Satan
Contact:

Re: Accurate gold coin offer price scraping for the spreadsheet nerds

Post by dualstow » Tue Jun 20, 2017 1:26 pm

Weird, it still doesn't work. Maybe it's my sheet and not the code, somehow.
Thanks anyway, Mark.
RIP Marcello Gandini
flyingpylon
Executive Member
Executive Member
Posts: 1102
Joined: Fri Jan 06, 2012 9:04 am

Re: Accurate gold coin offer price scraping for the spreadsheet nerds

Post by flyingpylon » Tue Jun 20, 2017 2:58 pm

APMEX changed their pages recently. Here's what I'm currently using, it seems to work for me.

=Index(ImportHTML("http://www.apmex.com/category/10000/gold","table",1),2,2)
User avatar
dualstow
Executive Member
Executive Member
Posts: 14225
Joined: Wed Oct 27, 2010 10:18 am
Location: synagogue of Satan
Contact:

Re: Accurate gold coin offer price scraping for the spreadsheet nerds

Post by dualstow » Wed Jun 21, 2017 11:16 am

EDIT: suddenly the 'REF' error is gone and it's working again!

earlier:
Hmm, that's my regular, the one that hasn't worked for a while.
Guess it's my sheet. Weird.
RIP Marcello Gandini
User avatar
dualstow
Executive Member
Executive Member
Posts: 14225
Joined: Wed Oct 27, 2010 10:18 am
Location: synagogue of Satan
Contact:

Re: Accurate gold coin offer price scraping for the spreadsheet nerds

Post by dualstow » Wed Nov 29, 2017 11:01 am

Damn, the colorado gold link isn't working for me anymore.

Code: Select all

=importhtml("http://www.coloradogold.com/htsell.php","table",1)
Going to try apmex again.

Nah, that's not working either. I should just type in the value manually once in a while.
RIP Marcello Gandini
User avatar
Kriegsspiel
Executive Member
Executive Member
Posts: 4052
Joined: Sun Sep 16, 2012 5:28 pm

Re: Accurate gold coin offer price scraping for the spreadsheet nerds

Post by Kriegsspiel » Wed Nov 29, 2017 11:23 am

dualstow wrote: Nah, that's not working either. I should just type in the value manually once in a while.
I just go to kitco and type in the gold spot price. No big deal.
User avatar
dualstow
Executive Member
Executive Member
Posts: 14225
Joined: Wed Oct 27, 2010 10:18 am
Location: synagogue of Satan
Contact:

Re: Accurate gold coin offer price scraping for the spreadsheet nerds

Post by dualstow » Wed Nov 29, 2017 11:25 am

Yeah, Kitco is where I usually peek outside of spreadsheets.
I just type 'k' into the chrome browser and chrome rolls its eyes and takes me there.
RIP Marcello Gandini
flyingpylon
Executive Member
Executive Member
Posts: 1102
Joined: Fri Jan 06, 2012 9:04 am

Re: Accurate gold coin offer price scraping for the spreadsheet nerds

Post by flyingpylon » Wed Nov 29, 2017 1:54 pm

Here's one that still works:

=Index(ImportHTML("https://www.moneymetals.com/precious-me ... ,"table",1),2,2)

It's getting more difficult to find sites still using tables, that deliver the same page content to both browsers and Google Sheets, and don't use javascript to populate the price value, etc.
User avatar
dualstow
Executive Member
Executive Member
Posts: 14225
Joined: Wed Oct 27, 2010 10:18 am
Location: synagogue of Satan
Contact:

Re: Accurate gold coin offer price scraping for the spreadsheet nerds

Post by dualstow » Wed Nov 29, 2017 3:17 pm

It does indeed work, thanks! I keep all these different lines of code in the comment section of the gold price sell of my spreadsheet. That way I can try alternates when one fails.
RIP Marcello Gandini
User avatar
Cortopassi
Executive Member
Executive Member
Posts: 3338
Joined: Mon Feb 24, 2014 2:28 pm
Location: https://www.jwst.nasa.gov/content/webbL ... sWebb.html

Re: Accurate gold coin offer price scraping for the spreadsheet nerds

Post by Cortopassi » Wed Dec 06, 2017 1:44 pm

flyingpylon wrote:Here's one that still works:

=Index(ImportHTML("https://www.moneymetals.com/precious-me ... ,"table",1),2,2)

It's getting more difficult to find sites still using tables, that deliver the same page content to both browsers and Google Sheets, and don't use javascript to populate the price value, etc.
Thanks. Apmex just seemed to go away again and this does work.

Otherwise, the other options I've considered:

1) Hey, not supposed to be looking at the PP every minute anyway. Just enter the price manually for gold.
2) Use GLD and a multiplier. I do this already as a backup. Gets you pretty close.
User avatar
dualstow
Executive Member
Executive Member
Posts: 14225
Joined: Wed Oct 27, 2010 10:18 am
Location: synagogue of Satan
Contact:

Re: Accurate gold coin offer price scraping for the spreadsheet nerds

Post by dualstow » Thu Feb 04, 2021 10:03 am

None of the codes on these two pages are working for me today. Maybe it's a Google Sheets problem.
RIP Marcello Gandini
User avatar
Mark Leavy
Executive Member
Executive Member
Posts: 1950
Joined: Thu Mar 01, 2012 10:20 pm
Location: US Citizen, Permanent Traveler

Re: Accurate gold coin offer price scraping for the spreadsheet nerds

Post by Mark Leavy » Thu Feb 04, 2021 11:07 am

These two are both working for me right now.

Code: Select all

=importhtml("http://www.coloradogold.com/htsell.php","table",1)

Code: Select all

=importhtml("http://www.ajpm.com/gold-bullion.html","table",0)
User avatar
dualstow
Executive Member
Executive Member
Posts: 14225
Joined: Wed Oct 27, 2010 10:18 am
Location: synagogue of Satan
Contact:

Re: Accurate gold coin offer price scraping for the spreadsheet nerds

Post by dualstow » Thu Feb 04, 2021 2:32 pm

Mark Leavy wrote:
Thu Feb 04, 2021 11:07 am
These two are both working for me right now.
...
Darn, they're not working for me. Thanks, anyway.
RIP Marcello Gandini
User avatar
pors
Associate Member
Associate Member
Posts: 27
Joined: Sun Jan 03, 2021 2:06 am

Re: Accurate gold coin offer price scraping for the spreadsheet nerds

Post by pors » Fri Feb 05, 2021 2:51 am

Mark Leavy wrote:
Thu Feb 04, 2021 11:07 am
These two are both working for me right now.

Code: Select all

=importhtml("http://www.coloradogold.com/htsell.php","table",1)

Code: Select all

=importhtml("http://www.ajpm.com/gold-bullion.html","table",0)

Awesome thanks! The result takes up quite a few cells, but at least it works :)
Post Reply