Silver Spreadsheet Available Here!

Mickroy said:
Does anyone know of a free web service that gives out spot prices on demand? I'd like to have a single spot price cell in my sheet that can be refreshed on demand i.e. a "Refresh" button that fires off a VBA macro to fetch the price. I can't find anything.
Click to expand...

I've been working on silver log 1.1, which updates a single spot price when you load the file, and every 10 mins thereafter. This is achieved by using external data from the Perth mint website and a 2nd sheet which "talks" to the first one I have set up.
 
How do you do this? Is it just a web data source as explained earlier?

In the meantime I'm using data from the Perth Mint web site also, but parsing the html returned from a web request. It's a bit flaky though as you need to know what you're looking for when searching the document. Here's code for silver from Perth Mint if anyone is interested & technically inclined.

Code: 
Sub uxUpdateSilverSpotButton_Click()
On Error GoTo Err:

    Dim httpRequest As Object: Set httpRequest = CreateObject("WinHttp.WinHttpRequest.5.1")
    Dim regExp As Object: Set regExp = CreateObject("vbscript.regexp")
    Dim regExpMatches As Object
    Dim silverPrice As Currency
    
    'Fetch html from perth mint prices page
    With httpRequest
        .Open "GET", "http://www.perthmint.com.au/metalPrices.aspx", False
        .Send
    End With
    
    'Find silver price with regex
    With regExp
        .IgnoreCase = True
        .Global = True
        .Pattern = "<span id=""lblSilverAskAU"">(.+)</span>"
        Set regExpMatches = .Execute(httpRequest.responseText)
    End With
    
    'Set spot price cell value
    ActiveSheet.Range("L21").Value = CCur(regExpMatches.Item(0).SubMatches.Item(0))

Err:
    If Len(Err.Description) > 0 Then MsgBox (Err.Description)
    Set httpRequest = Nothing
    Set regExp = Nothing
    Set regExpMatches = Nothing

End Sub
 
I followed the steps as outlined by Hiho above. I changed the data connections properties to always allow the connetion to the web data so I don't have to manaully refresh everytime I load the sheet. While I had the properties open I changed the refresh rate from 60 mins to every 10 mins, you could change it to every 1 min if you wanted to. I haven't entered any macros or code at all, hope this helps, cheers.
 
Cheers...oh yeah.....I've got a sheet for gold now as well. It's mirror copy of the silver log but it updates the gold price automatically, happy to email that one as well if anyones interested :D

Haveblue.
 
And, without wanting to step on toes etc... because I had no clue of the price I paid for some lumps of silver I bought back in 2007, I have a home brew special that simply asks for Quantity - Item - Oz for Silver and Gold, on one screen/sheet. In the background, it rips off Perth Minth and Kitco pricing, then displays your stack in AUD (PM), USD (PM), USD (Kitco) pricing for each metal, then total.

Happy to mail this to anyone who wants it, or wants to improve/break/plagiarise :)

EDIT: To save mucking about with PM's and return e-mails, it's here for anyone that wants it: http://www.freefilehosting.net/silverstackersstackercalc
 
Hi haveblue,
sent you an email for the spreadsheet but just discovered the link so I have downloaded it.
Wow am I going to have fun playing with that.
Thanks heaps
 
I have a spreadsheet its on my bed. I dont know why so many would want a record on their pc of everything they own. . Im amazed . Call me old but i want sfa on my pc of my wealth or holdings .
 
renovator said:
I have a spreadsheet its on my bed. I dont know why so many would want a record on their pc of everything they own. . Im amazed . Call me old but i want sfa on my pc of my wealth or holdings .
Click to expand...

I have my spreadsheet on a USB stick which only has the spreadsheet and is encrypted with truecrypt :).
 
fishball said:
renovator said:
I have a spreadsheet its on my bed. I dont know why so many would want a record on their pc of everything they own. . Im amazed . Call me old but i want sfa on my pc of my wealth or holdings .
Click to expand...

I have my spreadsheet on a USB stick which only has the spreadsheet and is encrypted with truecrypt :).
Click to expand...
Nice !!! good to see a bit of commonsense fishball
 
Never thought about having a spreadsheet for anybody to stumble across. I really like the idea of knowing what I have so might have to do like fishball and usb the sheet.
 
renovator said:
I have a spreadsheet its on my bed. I dont know why so many would want a record on their pc of everything they own. . Im amazed . Call me old but i want sfa on my pc of my wealth or holdings .
Click to expand...

+ 1

Thats bluddy right mate - its the first thing the government goons will seize is you pc! :lol:
 
kookaburra said:
Why use spreadsheets when there's this.....

http://realmoneytracker.com/

It's free and anonymous.
Click to expand...

Thanks for showing me the website, I haven't seen that one before, cheers. Personally it's not for me, after the Playstation Network was hacked I was glad I never stored my Credit Card number on that network. Many people said that the real reason that network was hacked was to get peoples passwords, as most people use the same password for everything.
 
fishball said:
renovator said:
I have a spreadsheet its on my bed. I dont know why so many would want a record on their pc of everything they own. . Im amazed . Call me old but i want sfa on my pc of my wealth or holdings .
Click to expand...

I have my spreadsheet on a USB stick which only has the spreadsheet and is encrypted with truecrypt :).
Click to expand...

+1 :D My harddrive is encrypted...and then my actual spreadsheet is encrypted as well.
 
You must log in or register to reply here.
Back
Top