Solved

Excel macro to determine the price calculation of coins ?

Posted on 2016-08-15
11
87 Views
Last Modified: 2016-09-23
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:
Weight:

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:
Verdict:
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.

Note:
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
0
Comment
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
11 Comments
 
LVL 8

Author Comment

by:Senior IT System Engineer
ID: 41757258
Here's the formula that I can think of:

Price over Spot = (Purchase Price / Weight in Ounces) – Spot Price
0
 
LVL 32

Accepted Solution

by:
Robberbaron (robr) earned 500 total points
ID: 41763996
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

goldvalue.xls
1
 
LVL 32

Assisted Solution

by:Robberbaron (robr)
Robberbaron (robr) earned 500 total points
ID: 41763999
1
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 8

Author Comment

by:Senior IT System Engineer
ID: 41764002
Robr,

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 ?
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 41764008
they are valuations less than 50% markup
1
 
LVL 8

Author Comment

by:Senior IT System Engineer
ID: 41764010
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)
0
 
LVL 8

Author Comment

by:Senior IT System Engineer
ID: 41764011
Shall I convert the Troy ounce to gram first and then enter the value into: Weight (grams) column ?
0
 
LVL 32

Assisted Solution

by:Robberbaron (robr)
Robberbaron (robr) earned 500 total points
ID: 41764036
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.
0
 
LVL 8

Author Comment

by:Senior IT System Engineer
ID: 41764058
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 ?
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 41764075
absolutely use a formula.
the VBA is just a user defined formula (UDF)
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
This article describes a serious pitfall that can happen when deleting shapes using VBA.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question