My Gold-Silver spread sheet with live prices

[EL34]

Spread sheet updated 01/20/13

Now has Gold and Silver plus live prices from the Kitco web site
Latest update - Kitco changed the column location for spot prices on their web page and I just fixed that
If you want to change your existing spreadsheet, here's the calculations that go into The Gold and Silver spot cells
Gold =Sheet2!J39, (it was J40)
Silver =Sheet2!J41 (it was J2)

There's a screen shot down below showing how to update your existing spread sheet

-----------------------------------------------------------------------------------------

I use an Excel spread sheet to keep track of my stack
A couple people have asked if they could get a copy and so I am attaching my .xls spreadsheet to this post

It keeps track of what I paid, spot plus or minus and actual spot paid, extra charges like shipping, etc and how much the stack is worth at current spot price.
It does bunch of calculations after you enter some basic info

There are two sheets in the spreadsheet
Sheet 2 is the captured Kitco web page where the spot prices are found. Do not delete sheet 2.

Sheet 1 - There's three sections to sheet number 1
The top section is for gold
The middle section is for US 90% silver coins
The bottom section is for silver bullion, rounds, etc

Below both sections is the grand totals for just the silver and grand totals for gold and silver combined

I have included an example for each section so you can see how it works.
I have been dialing in this spread sheet for quite a while.
Let me know how you like it or have any suggestions for improvement

BTW, this was created using Excel from Office 97 (Yes I still use office 97)

The sheet is protected so that you don't erase the formulas for the blue calculated fields.
The black fields accept data input
If you unprotect the sheet, you can edit the locked fields.
I would re-protect it again after you edit because it is very easy to erase the formulas by accident
I added sheet number two which is a data query

Sheet number two goes to Kitco and gets the current spot prices for Gold and silver
The whole sheet will update when you refresh the data from Kitco

There is nothing to edit on sheet number two because it pulls all that messy data in from a Kitco web page
The way I refresh the whole sheet in excel 97 is on the Data tool bar, I use the refresh all button
The refresh all button will update the Kitco prices and all the calculations at one time
I am not sure if it is the same in other versions of excel

The updated spread sheet is attached to this post - enjoy

The screen shot below shows the button you push to update the prices from Kitco. If this button is not on your toolbar you can add it.
It is called Refresh All and can be found in the toolbar Data menu

[harbl_the_cat]

Aaaaaah that's how you import external data from a website... I'm so dumb!

Thanks for the .xls!

Let's see if it works for me

[EL34]

(06-29-2012 02:45 PM)harbl_the_cat Wrote:  Aaaaaah that's how you import external data... I'm so dumb!
Thanks for the .xls!

Ha, I just figured it out myself
I have never messed with data queries in Excel before

Sheet number one looks at sheet number two to get the spot prices.
Sheet two is the Kitco web site page sheet

The only problem is that if Kitco changes the layout of their screen, the Gold and Silver spot prices might be in a different place on sheet number 2.
If I see that happen I will post back with an update

[Viper_99]

Thank El, you do a lot for our community! I appreciate it.

[EL34]

(06-30-2012 10:59 AM)Viper_99 Wrote:  Thank El, you do a lot for our community! I appreciate it.

Thanks, I love figuring out how to do stuff like that.

I also do programming in vb.net and that is always a rush when you figure out how to do something cool.

[EL34]

I updated the spreadsheet cause Kitco changed the column locations for Gold and silver spot prices.

The new sheet is in post #1
The instructions to update your current spread sheet are in post #1

You just need to change a couple numbers where the spot prices are found on sheet 2

enjoy

[TheShinyWonder]

(06-29-2012 02:40 PM)EL34 Wrote:  Spread sheet updated 01/20/13

Now has Gold and Silver plus live prices from the Kitco web site
Latest update - Kitco changed the column location for spot prices on their web page and I just fixed that
If you want to change your existing spreadsheet, here's the calculations that go into The Gold and Silver spot cells
Gold =Sheet2!J39, (it was J40)
Silver =Sheet2!J41 (it was J2)

There's a screen shot down below showing how to update your existing spread sheet

-----------------------------------------------------------------------------------------

I use an Excel spread sheet to keep track of my stack
A couple people have asked if they could get a copy and so I am attaching my .xls spreadsheet to this post

It keeps track of what I paid, spot plus or minus and actual spot paid, extra charges like shipping, etc and how much the stack is worth at current spot price.
It does bunch of calculations after you enter some basic info

There are two sheets in the spreadsheet
Sheet 2 is the captured Kitco web page where the spot prices are found. Do not delete sheet 2.

Sheet 1 - There's three sections to sheet number 1
The top section is for gold
The middle section is for US 90% silver coins
The bottom section is for silver bullion, rounds, etc

Below both sections is the grand totals for just the silver and grand totals for gold and silver combined

I have included an example for each section so you can see how it works.
I have been dialing in this spread sheet for quite a while.
Let me know how you like it or have any suggestions for improvement

BTW, this was created using Excel from Office 97 (Yes I still use office 97)

The sheet is protected so that you don't erase the formulas for the blue calculated fields.
The black fields accept data input
If you unprotect the sheet, you can edit the locked fields.
I would re-protect it again after you edit because it is very easy to erase the formulas by accident
I added sheet number two which is a data query

Sheet number two goes to Kitco and gets the current spot prices for Gold and silver
The whole sheet will update when you refresh the data from Kitco

There is nothing to edit on sheet number two because it pulls all that messy data in from a Kitco web page
The way I refresh the whole sheet in excel 97 is on the Data tool bar, I use the refresh all button
The refresh all button will update the Kitco prices and all the calculations at one time
I am not sure if it is the same in other versions of excel

The updated spread sheet is attached to this post - enjoy

The screen shot below shows the button you push to update the prices from Kitco. If this button is not on your toolbar you can add it.
It is called Refresh All and can be found in the toolbar Data menu

Kudos, standing ovation. I've been trying to put one together, but lack above advance excel skills. Thank you.

[EL34]

Thanks,
Let me know if you can think of any cool features I can add

[Mark777]

Thanks a lot EL34. I remember when you first came out with your spreadsheet about a year or so ago and recently I've been wanting to go back to it and use it. I don't even know exactly what I own. LoL

Years ago I used Excel spreadsheet at work but have long since forgotten how to use it. It's a very nice program for stuff like this.

This will be one of my winter projects...can't wait to see the total ounces of silver I have and finally to be organized and to find out what my stack is worth. I know it's fiat but still fun...more important is how many ounces I own.

[Mark777]

I tried saving and opening up the program and I get the message:

Microsoft Works

Works spreadsheet does not support hyperlinks.

Hyperlinks will be converted to plain text values.

I told you it's been a long time since I've worked with excel...is something not working right here? It doesn't sound like it's working like it should. lol

[onagoth]

Nice spreadsheet, only recommendation I would make, which I have factored into mine is the buyback premium on certain items. Depending on the composition of your stack, not every item has a spot price fair value.

[EL34]

(01-20-2013 02:45 PM)Mark777 Wrote:  Microsoft Works
Works spreadsheet does not support hyperlinks.
Hyperlinks will be converted to plain text values.

I am guessing that is only sheet #2 where it fetches a Kitco page and then parses out the spot prices for Gold and Silver?
I am not familiar with newer versions of Excel, so I don't know how to fix that.
Maybe someone that knows excel better than me knows how this is done in more recent versions?

Mark, try the version I attached to this post
It does not have the Kitco price updates
you have to enter spot prices manulally
let me know if this one works

[EL34]

(01-20-2013 04:54 PM)onagoth Wrote:  Nice spreadsheet, only recommendation I would make, which I have factored into mine is the buyback premium on certain items. Depending on the composition of your stack, not every item has a spot price fair value.

Not sure I understand
Buy back from who and what coins and years in particular?

[Mark777]

(01-20-2013 06:19 PM)EL34 Wrote:  

(01-20-2013 02:45 PM)Mark777 Wrote:  Microsoft Works
Works spreadsheet does not support hyperlinks.
Hyperlinks will be converted to plain text values.

I am guessing that is only sheet #2 where it fetches a Kitco page and then parses out the spot prices for Gold and Silver?
I am not familiar with newer versions of Excel, so I don't know how to fix that.
Maybe someone that knows excel better than me knows how this is done in more recent versions?

Mark, try the version I attached to this post
It does not have the Kitco price updates
you have to enter spot prices manulally
let me know if this one works

Yup. It appears this one worked but now I have to enter the spot prices manually.

I have to try your spreadsheet number one above again to see if that works.

Thanks.

[Mark777]

I tried opening spreadsheet number 1 and got this message:

Microsoft Works

Problem with formula U43.
The formula is too long or too complex, or contains a function that is not supported.
Click Yes to continue loading and display other errors, click No to continue loading and suppress other errors, or click Cancel to stop loading the document.

[EL34]

(01-20-2013 06:41 PM)Mark777 Wrote:  Yup. It appears this one worked but now I have to enter the spot prices manually.

If I had a more recent version of excel I could figure it out.
Maybe they discontinued the ability to fetch hyperlinks in later versions?

[Mark777]

(01-20-2013 06:48 PM)EL34 Wrote:  

(01-20-2013 06:41 PM)Mark777 Wrote:  Yup. It appears this one worked but now I have to enter the spot prices manually.

If I had a more recent version of excel I could figure it out.
Maybe they discontinued the ability to fetch hyperlinks in later versions?

I clicked Yes and it said 2 errors were found and both errors had to do with formula U43.

[EL34]

Weird, I don't know
Neither sheet1 or sheet to have a U column

Sheet 1 ends at S and Sheet 2 ends at N

[onagoth]

(01-20-2013 06:20 PM)EL34 Wrote:  

(01-20-2013 04:54 PM)onagoth Wrote:  Nice spreadsheet, only recommendation I would make, which I have factored into mine is the buyback premium on certain items. Depending on the composition of your stack, not every item has a spot price fair value.

Not sure I understand
Buy back from who and what coins and years in particular?

For example,

SGB lists the following buy back prices (which is really the fair value today, but varies by dealer)

rounds - spot
Maples, eagles, etc - around spot + $1
1/10 gold maples - spot plus 4.5%

It doesn't make a huge difference, but when calculating the 'fair value' of my silver/gold, I assume I am selling it to these dealers at their specified buy back premiums. Your sheet seems to just be grabbing the spot price and applying it to the total ounces to determine its current value.

Cheers

[EL34]

Hmm, I don't know cause I never think of buy back
That may not happen for many years in my case

And that figure changes all the time and depends on the source and market conditions
And your favorite Sources may change all the time
And every type of coin on the spread sheet would be different

Someone could insert a custom column or two if they feel the need to modify my basic spreadsheet

I am already noticing that buy back prices are rising on Eagles

[Mark777]

(01-20-2013 06:56 PM)EL34 Wrote:  Weird, I don't know
Neither sheet1 or sheet to have a U column

Sheet 1 ends at S and Sheet 2 ends at N

Hmmm. That is really weird! Maybe there is something wrong with my computer again?

[silverflood]

Thanks for the xls. I don't remember exactly what I have paid for my 90% coinage, but I'm pretty close.

[EL34]

Which version of Excel are you using?

Just curious so I can figure out if the live prices works in other versions

[silverflood]

(01-24-2013 07:20 AM)EL34 Wrote:  Which version of Excel are you using?

Just curious so I can figure out if the live prices works in other versions

I used it at work on my office PC running office 2010. I had to change the locations of the spot prices on sheet 1 to match the kitco chart on sheet 2 but I forget which columns they were atm. After I did that the live update worked fine, thanks again.

[EL34]

Ok cool, thanks for the update
Glad you got it working
If Kitco changes the columns again for Silver and Gold prices, it's easy to update your spreadsheets

Look at Sheet #2 and see where the two prices are and then change the column and row numbers on sheet #1

The screen shot below shows what sheet #2 looks like and where the Silver and Gold prices are located currently