Silver Spreadsheet Available Here!

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.

 

Haveblue

Thanks for your email will give you some feedback after playing around

 

I am just in the process of making one, thanks this will help laying it out

 

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

Haveblue.

 

Uhmmm Spread what??

REDBACK

 

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

 

I would love a copy of this when it's ready thanks haveblue!!
)

 

Why use spreadsheets when there's this.....

http://realmoneytracker.com/

It's free and anonymous.

 

Thanks again Haveblue.

Great Spreadsheet.

 

and you have to enter your name, email adress and country to register ?

how anonymous is that ?

 

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 .

 

I have my spreadsheet on a USB stick which only has the spreadsheet and is encrypted with truecrypt .

 

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.

 

+ 1

Thats bluddy right mate - its the first thing the government goons will seize is you pc! :lol:

 

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.

 

+1 My harddrive is encrypted...and then my actual spreadsheet is encrypted as well.