Precious metals/stack Excel spreadsheet - do you need one?

[Paul]

Offer of help 

 

If anyone out there is not to savvy with excel for a spreadsheets of their stack i would like to extend help.

if all your stack is in a little black book and you want to come into the modern age, i can help you

 

Although i am not at software engineer standard at MS excel i have managed to cobble together a half decent spreadsheet to track your stack.

 

Over the past few years, it has certainly took me a few hours to perfect it, as i have added various bells and totally unnecessary whistles like colour co-ordination

 

Features:

• Total invested
• Current stack value
• Current profit/loss in stack
• GSR breakdown
• % breakdown of stack allocation
• Price per (g) per item
• Price per (oz)
• Mean average costs of stack (DCA)
• Mean price (g)
• Mean price per (oz)
• Dates of purchase
• Item description

Basically everything at a glance where your stack is, nice to experiment if gold/silver goes to the moon what your stack will be worth in fiat £'s

 

 

If anybody wants it i am happy to email it through to you for the princely sum of  £FREE, gratis, zero, nothing, ziltch, nadda

 

All i ask for is a 'like' and a 'small thanks or comment' follow up post in this thread

 

Just drop me a private message (PM) with your email address and i will send it through to you

[BaldyBob]

Thanks Paul

 

Bit more high tech than my version  (understatement)......a few minor tweaks and good to go.

[LongJohn]

Thanks very much Paul.  Working perfectly on Excel btw.

[Paul]

great news it works with MS excel, thanks for letting me know - i have not used Microsoft products in years as Open Office does everything i need without the usual microsoft costs. glad to be of help  

[LongJohn]

great news it works with MS excel, thanks for letting me know - i have not used Microsoft products in years as Open Office does everything i need without the usual microsoft costs. glad to be of help  

Just been playing around with it Paul, seems the text ports over no problem but the formulas don't, easy enough

to work them out though.  I know there is a way to port in data from external sources, in particular regard to the

upto date spot prices, did you have a formula for this out of interest or are they to be put in manually?

[Paul]

I just enter the spot prices manually in the top left corner cells.  I don't have the external links out to auto update spot prices.  I know it is possible to do this however

 

Sorry to hear the formlas don't seem to port over to excel.

as i say i dont have excel to see as to the reasons why this would be.  I would assume it is a just a small change needed to locate the problem

 

The forumulas all work fine with spreadsheet Calc within OpenOffice

[LongJohn]

I just enter the spot prices manually in the top left corner cells.  I don't have the external links out to auto update spot prices.  I know it is possible to do this however

 

Sorry to hear the formlas don't seem to port over to excel.

as i say i dont have excel to see as to the reasons why this would be.  I would assume it is a just a small change needed to locate the problem

 

The forumulas all work fine with spreadsheet Calc within OpenOffice

 Probably something i've missed tbh but it's working ok having put the formulas in myself.

I'll see if I can work out how to link to external spot prices and let you know if I get anywhere with it.

[silversword]

I used to find that where Excel uses a comma to separate arguments in a forumla, Open Office uses a semicolon instead. It might be that, or some other variation in syntax that stops formulas from being ported over correctly. Can't say for sure as I haven't tried converting any recently

[LongJohn]

I just enter the spot prices manually in the top left corner cells.  I don't have the external links out to auto update spot prices.  I know it is possible to do this however

 

Just got it to wok on mine Paul with prices updating once per minute.

 

This page explains how:  http://www.mrexcel.com/tip103.shtml

 

And I linked to this chart for the silver price : http://www.exchangerates.org.uk/commodities/live-silver-prices.html

 

And here for Gold : http://www.exchangerates.org.uk/commodities/live-gold-prices.html

 

Just put the charts on a separate worksheet and link the spot price cell to the GBP price cell for each.

[tony]

Hi Paul,

 

Many thanks for the copy of your spreadsheet the other day.

 

You must have spent some time developing this.

 

I have made a few little tweeks to it, to sort for my needs, but it works excellent.

 

The only problem now is ........ the sheet also shows any losses that you may have 

 

Tony.

[Shamatti]

Thanks for the work!! I have mine on google drive and I'm redoing some sheets with ideas from yours

 

With all the hacking that has been going on I think that would be that last place that I would keep a list of all my PM's.

 

Seriously, every intelligance agency in the world will have a copy by now, along with your IP address, and therefore your real address. And probably your bank details, your car details, your shoe size, everything down to what colour undies you like to wear! lol

 

No seriously dude, that is the last place I would keep it.

[Keithoil]

Mine are ONLY stored on my HDD and my micro sd card, and will only ever be stored in these places.

[daz]

cheers paul , fantastic work . just what i needed to keep track of my spending bit scary though as didnt realise i had spent so much already.

[TerryGriffiths]

Great spreedheet, tremendous work Paul.

[Paul]

@terryG/daz

 

you are both very welcome - im glad it helps you both

happy stacking

[JuniorSilver]

Nice spreedheet and thank you Paul.

[greektony]

Great spreadsheet. Thanks for the quick response :-)

[Clens92]

Great spreadsheet - really thorough and I can see myself using it a lot.

[daz]

paul , was just doing some work on my sheet and realised that the total ounces on my sheet  are out because of the fractional ounce coins are not included properly eg polar bear 1.5 ounce

if i enter it as 1.5 it rounds it up to 2 , so ive been entering it as 1 1/2 which it accepts but ive just noticed the total ounce doesnt add it up correctly and therefore my cost per ounce is also out . my sheet shows £18.18 per ounce yet it should really be £17.21.

im sure there is a very simple answer and im doing something wrong, how should i be inputting these fractional coins so they add up correctly?

cheers

daz

[Paul]

thanks daz -

 

I have a good few 1/2 oz and and few of these new odd fractional silver coins.  

I have just checked and it seems OK for me using open office calc, if it has migrated/opened in MS Excel it may have lost a few of the numbering preferences.

 

If you right click the cell(s)--> format cells -->Numbers-->

 

and see what the 'default' numbering has changed to.  Amend it to however many decimal places so it works out to be correct.

 

It certainly shouldn't round up or round down

[daz]

cheers for the quick reply , have changed the default formatting to 2 decimal places and that now lets me input it correctly as 1.5 etc but my total ounces  still doesnt work out properly even though i have changed that to 2 decimal places as well.

ive only got three fractionals a 1.5 a 0.5 and a 0.75 so my total should end in 0.75 but it doesnt.

im using open office calc and its saved as a ods file .

on the plus side, i thought i was on 49 ounces and it turns out im on 51.75 ! (my sheet tells me im on 52 though)

[doogs]

Paul, thanks for the spreadsheet - looks good!

 

Cheers,

Doogs

[SilvergunSuperman]

Many thanks Paul, spreadsheet looks good and has everything I need. The only issue is that my stack isn't in profit, how do I fix that? 

[Paul]

The only issue is that my stack isn't in profit, how do I fix that? 

 

Buy a crystal ball from eBay !

Youll find a lot of folk who have stacking for a few years are underwater with price 

 

buy and hold , buy and hold - good luck 

[junkyardbob]

Is this offer still available? And can you cange form UK pounds to other currencies?