Dynamic Product Pricing for Precious Metals
To do this, I would recommend setting up a table in the database to store the latest spot price from the data source (XML feed). Many of these feed providers offer a subscription to access their spot price data. Some of them have “hit restrictions” on the frequency that you can retrieve the data.
The table in the database will store the most current spot price and the date/time it was last updated (mysql “timestamp")
Then in your script, you can set it up to only hit the data source if the timestamp of the record in the database has been longer than (however long you want the interval to be) ago.
For the XML example shown in the earlier posting, that is gram price so a formula will have to be used to convert that to price per troy ounce if the weight measure you will use for pricing is troy ounces. (gram price * 31.1034768 = troy ounce price)
For a precious metal product with a high purity (.999 or better) the metal value can be fairly calculated (product price = gram price * 31.1034768 * troy ounce weight)
When a product contains multiple metals, the percentage is needed for each that make up the product as shown in example below.
Example: $50 American Gold Eagle
Total Weight: 1.0909 troy ounces
Gold Percentage: 91.67%
Actual Gold Weight: (.9167 * 1.0909 = 1.0000 troy ounces)
Actual Gold Value: (1.0000 * gold troy ounce price)
Silver Percentage: 3.00%
Actual Silver Weight: (.03 * 1.0909 = 0.0327 troy ounces)
Actual Silver Value: (0.0327 * silver troy ounce price)
Copper Percentage: 5.33%
Actual Copper Weight (.0533 * 1.0909 = 0.0581 troy ounces)
Actual Copper Value: (0.0581 * copper troy ounce price)
Product Metal Value = Actual Gold Value + Actual Silver Value + Actual Copper Value
Usually, products of this type are priced with an additional cost over the current Product Metal Value.
For this example let’s say the additional cost is $20.00 (this is $20.00 per troy ounce)
Price Over Spot = Total Troy Ounce Weight * $20.00
Total Product Price = Product Metal Value + Price Over Spot
Ok, now you have retrieved the XML data, stored it, and calculated prices based on it. Those prices have to be stored for the time interval that you desire. (1 minute, 5 minutes, 1 hour)
If the website has 50 concurrent visitors, it will be crucial to be using the stored spot price data in your own database. Using a quick date comparison will let the script know if it is time to go out and retrieve an updated XML spot price based on the interval that you choose.
If the timestamp in your database for your spot price data is greater than (your interval) ago, then hit the XML feed and read the new prices.
It is a sort of “wash, rinse, repeat” process that needs some control. When you don’t have visitors and if you want to keep a log of spot prices to build charts or price history features into the site, then a chron-job can be set up on your host to call the same script that normally performs the XML read at the interval you specify.
The way I set mine up, the interval is 5 minutes for the chron-job and 1 minute when there are active website visitors. I implemented a global price update feature so everyone’s cart items change with the price coming from the XML feed. For my product pricing, I don’t use the actual value of the product, I use the amount over the Product Metal Value that I want to charge for the product.
When I add a new product, I enter the troy ounce weight, metal percentages and price over spot that I want to charge. The spot prices do the rest.
It took about 2 weeks to get it programmed and fine-tuned without any bugs but it is real real-time pricing.