• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 185
  • Last Modified:

Excel macro to determine the price calculation of coins ?

Hi All,

Can anyone please share some tips and probably Excel spreadsheet to do some calculations:

For example that 0.5g gold coin with store price of $60:
$1341 (spot price) / 31.103 (grams in an ounce) / 2 (as only half a gram) = $27.97 worth of Gold, so that markup/premium is HUGE (more than 100% over spot).

I want the input type of the Excel is:
Coin price:

The Gold price can be pulled from this website: www.apmex.com/spotprices/gold-price (if this can be automated with auto refresh then it is great).

I want the output type of the Excel is:
RED if it is more than 100% of the worth of gold/store price.
GREEN if it is more than 50% of the worth of gold/store price.

31.103 grams to a troy ounce.
32.15 troy ounces to a kilo.

Spot x .75 for 18k
Spot x .916 for 22k

is this possible with Excel Spreadsheet ?

like in: http://www.silverstackers.com/calculators/index.php
Senior IT System Engineer
Senior IT System Engineer
  • 5
  • 5
3 Solutions
Senior IT System EngineerIT ProfessionalAuthor Commented:
Here's the formula that I can think of:

Price over Spot = (Purchase Price / Weight in Ounces) – Spot Price
Robberbaron (robr)Commented:
yes, excel is good but I dont know how to scrape the website for current price so put it in a reference cell for u to edit.
(there is probably a website that provides a webservice to do this)
I then Conditionally format the cell that has the valuation result based on the value.

Public Function goldValuation(purchaseprice, coinweight) As Integer
    Dim goldprice As Single, goldvalue As Single
    goldprice = Range("GoldSpotPrice").Value  'per ounce
    goldvalue = coinweight / 31.103 * goldprice

    goldValuation = purchaseprice / goldvalue * 100  'as percent
End Function

Open in new window

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Senior IT System EngineerIT ProfessionalAuthor Commented:

Many thanks for the reply.

I can see in the Excel spreadsheet, there are 3 colour coded rows.

What does it means by the white / non-red / non-green ones ?
Robberbaron (robr)Commented:
they are valuations less than 50% markup
Senior IT System EngineerIT ProfessionalAuthor Commented:
Ah I see,

so the best ones are the one that is not highlighted red nor green.

Last question regarding your spreadsheet,

When I browse to: https://www.abcbullion.com.au/store/Bullion-Coins/?filter%5Bprice%5D=0&func=search&set=1

Do I just change the column:

Price and Weight (grams)
Senior IT System EngineerIT ProfessionalAuthor Commented:
Shall I convert the Troy ounce to gram first and then enter the value into: Weight (grams) column ?
Robberbaron (robr)Commented:
the intent is to enter new data into the cells (up to 5 rows) under the price and weight headings.

the code already assumes that weight of coin is in Grams and that spot price is in Ounces.

so if the weight of coin is in ounces, you have to convert and enter the gram weight into the cell.
Senior IT System EngineerIT ProfessionalAuthor Commented:
Hi mr. Baron,

So can I use excel formula to convert the weight into grams from Oz ?

Or do I have to open and edit the VBS instead ?
Robberbaron (robr)Commented:
absolutely use a formula.
the VBA is just a user defined formula (UDF)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now