Gold Spot Scrape for Excel

[PJRay]

Hi friends

I've made myself an excel spreadsheet to keep a track of my purchases and I would like it to update the gold spot price in GBP automatically.

I know how to link the spreadsheet to a web page and pull data from it, the trouble I'm having is finding a suitable web page that allows it.

For example, I can direct my spreadsheet to look at thesilverforum.com landing page, but it doesn't recognise the ticker at the top with the latest prices. Same with gold.co.uk et al.

I managed to link it with kitco.com/gold-price-today-uk/ but that tracker seems really erratic compared to other spot price trackers.

Could somebody share the webpage that they use to scrape the current GBP gold spot from please?

[Mariner1961]

I use Bullion By Post in excel, have used it for a while and works well although occasionally they change the page and you have to alter the link by a cell.

[PJRay]

8 minutes ago, Mariner1961 said:

I use Bullion By Post in excel, have used it for a while and works well although occasionally they change the page and you have to alter the link by a cell.

Do you have to login to make it work, because that's one of the pages I can't seem to work with.

Perhaps it's an issue with my Firefox browser, maybe one of my plug ins is messing things up.

 

 

[Mariner1961]

No I don't think so, it doesn't ask for one but tbh I've used it that long I can't remember if I had to put one in at the start.

I checked and the data range comes straight from bullionbypost.co.uk

[PJRay]

OK thank you. My excel just flat out refuses to connect to that page. 🤔

[Mariner1961]

Sorry, wish I had a solution but no idea why it doesn't.

[razius]

I use the following in Excel for USD prices:

Quote

=Index(ImportHTML("http://www.apmex.com/spotprices/gold-prices","table",8),2,2)

Then I fetch the currency rates separately and calculate the conversion to local currency.

[Prophecy]

Not sure if this one works because i use Google Sheets =IMPORTHTML("https://www.bullionbypost.co.uk/gold-price/","table",2)

you can change 2 to 1 if it goes funky

[PJRay]

5 minutes ago, Prophecy said:

Not sure if this one works because i use Google Sheets =IMPORTHTML("https://www.bullionbypost.co.uk/gold-price/","table",2)

you can change 2 to 1 if it goes funky

I tried both but that doesn't work for me. Google sheets formatting I imagine doesn't work with excel. Thank you for trying though.

I'm a little exasperated that I can't grab such simple data. It must be something I'm doing wrong at my end and I'm sure it's really simple when you know how.

I should probably be asking this question on an excel forum, not a precious metals one. 😄

 

 

[Prophecy]

5 minutes ago, PJRay said:

I tried both but that doesn't work for me. Google sheets formatting I imagine doesn't work with excel. Thank you for trying though.

I'm a little exasperated that I can't grab such simple data. It must be something I'm doing wrong at my end and I'm sure it's really simple when you know how.

I should probably be asking this question on an excel forum, not a precious metals one. 😄

 

 

You sure you've set it up to actually receive live data from the web? Try Google Sheets. Import your Excell into it and use my code 

[PJRay]

Just now, Prophecy said:

You sure you've set it up to actually receive live data from the web? Try Google Sheets.

Yeah. I'm currently successfully scraping the price every minute from https://www.kitco.com/gold-price-today-uk/

It just seems to swing wildly around +/- £3 from every other gold spot I look at.

[PJRay]

10 hours ago, Mariner1961 said:

No I don't think so, it doesn't ask for one but tbh I've used it that long I can't remember if I had to put one in at the start.

I checked and the data range comes straight from bullionbypost.co.uk

If other excel users are doing it successfully, it must be something on my end that I'm fudging.

[Booky586]

On 22/07/2020 at 12:55, Mariner1961 said:

I use Bullion By Post in excel, have used it for a while and works well although occasionally they change the page and you have to alter the link by a cell.

Would you mind sharing the syntax you use in your Excel cell please? Like the OP, I'm struggling to get this to work in Excel.

[Airhead]

If I remember from my time doing a stint as IT support the versions of Excel do have a bearing on functionality and the handling of macros embedded in historic files. Also make sure that the required add-ins are installed and enabled. It could be something as simple as the difference between http and https in the address.

[RacerCool]

23 hours ago, Airhead said:

If I remember from my time doing a stint as IT support the versions of Excel do have a bearing on functionality and the handling of macros embedded in historic files. Also make sure that the required add-ins are installed and enabled. It could be something as simple as the difference between http and https in the address.

That's a good point right there.

[PJRay]

Neither work for me.

"https://www.gold.co.uk/gold-price/gold-price-today/" looks promising when I choose the 'live metal table', but then it timeout's after 100 seconds. I've tried grabbing other elements of the page but they all timeout.

It won't connect to http at all.

 

[Airhead]

It might be that you need to create a port forwarding rule on your firewall to allow excel to use port 8080 (https). It is a problem I have come across myself when mucking about with internet type things. Computers do not have inherent intelligence they have to be told what to do🙂

[PJRay]

35 minutes ago, Airhead said:

It might be that you need to create a port forwarding rule on your firewall to allow excel to use port 8080 (https). It is a problem I have come across myself when mucking about with internet type things. Computers do not have inherent intelligence they have to be told what to do🙂

With this prompt, I thought I'd almost fixed it. It wasn't my firewall I changed permissions for because I could find no mention of excel being an app that is blocked or whitelisted for web access. It didn't give me any opportunity me to add gold.co.uk as trusted either.

I did have a bit more joy with excels own trust centre.

File > Options > Trust Centre > Trust Centre Settings

then 'enable all data connections' and 'enable automatic update for all Workbook links'. That allowed the table to load. I thought I'd cracked it but unfortunately the time out occured again when it tried to automatically refresh two minutes later. 😕

I think I might have to admit defeat for now and stick with the Kitco scrape, (at least it gets me in the proverbial ballpark). There is someone at work I could ask who is a whizz with excel but that would mean giving up my privacy.

I'll continue to tinker, I don't like it when the computer beats me. It must be something at my end and I bet it's a simple fix when you know how.

[PJRay]

12 minutes ago, PJRay said:

It wasn't my firewall I changed permissions for because I could find no mention of excel being an app that is blocked or whitelisted for web access.

I've added excel to allowed apps for windows defender, but that hasn't fixed it. 😆

[Blockhead]

Don't know if these formulae will work in Excel (the Google Finance one probably won't) but this is what I've done in Google Sheets. 

Some of it may help, I hope. 

In the next screenshot, row 23 removes the $ sign from row 21 result so I can convert from USD to GBP

For gold I used similar formulae but this is the formula for the gold table:

[The1GramMan]

Hi,

I know this may sound rude but does anyone have a excel spread sheet created that they would be willing to share with me to use, it would really appreciated. I unfortunately am no good at excel. 

Thank you 

[ChrisSilver]

30 minutes ago, The1GramMan said:

Hi,

I know this may sound rude but does anyone have a excel spread sheet created that they would be willing to share with me to use, it would really appreciated. I unfortunately am no good at excel. 

Thank you 

I collated a few excel topics together a while ago but had hidden it as I didn't think the list was extensive enough. However I have unhidden it so you can have a look, if you find any more topics that are also useful around the forum that you think should be added into the topic below, please let me know.
 

 

[The1GramMan]

Thank you very much I will do i will take a look, brilliant 

Thanks Chris

[PJRay]

55 minutes ago, The1GramMan said:

Hi,

I know this may sound rude but does anyone have a excel spread sheet created that they would be willing to share with me to use, it would really appreciated. I unfortunately am no good at excel. 

Thank you 

PM'ed