About Galleon-Dollar Converter
According to my theory the WZG to USD rate should vary on the basis of Gold and Silver USD/oz prices.
WZG/USDdate = (0.375 P(Au)date+0.625 P(Ag)date)/28.35 x 1.98,
where WZG/USDdate – exchange rate of Galleon to US Dollar for specified date
P(Au)date – Gold price for specified date, USD/oz
P(Ag)date – Silver price for specified date, USD/oz
0.375 – mass portion of gold in Galleon alloy
0.625 – mass portion of silver in Galleon alloy
28.35 – ounce/gram denominator
1.98 – Galleon mass, gr.
Since I performed all calculations in metric system and the prices are given per once, I have to use ounce/gram denominator.
Gold and Silver prices I found in Internet on http://www.kitco.com website. Data from 1996 to current date is available in HTML format, all others as GIF and BMP pictures, so I had to use text recognition program. ABBYY FineReader 8.0, if someone is interested. It would not work with low quality images from Kitco, so I had to improve them graphically. Standard MSPaint was enough.
To find Galleon to Pound and to any other currency, Russian Rouble for example, exchange rates I took corresponding records from national banks. GPB/USD from www.bankofengland.co.uk , USD/RUR from www.cbr.ru. Russian Central Bank has the history of exchange rates only since July 1992, however trade sessions on Moscow Internationl Currency Exchange took place since November 1991, which results were de-facto actual, though not official. This period is markrd in database in yellow background. January 1991-October 1991 – is the area were I picked only fragments of data and extrapolated them linearly. The least trustworthy data. Marked with red. Please also note that GPB/USD rate according to Bank of England is slightly different from Oanda one, which I used in the essay, but not significantly. So I did not re-write essay.
For proper Excel work data arrays have to be uninterrupted, so I deleted hollow entries, usually referring to weekends, Easters, Christmases, Maydays and other Bank holidays. Gold trade, as I understood, is performed in two shifts, morning and evening, I took, if possible, the later one. In case of short exchange working days, usually Christmas and New Year eves, I used morning session data. When there was no trade of one of metals I used previous day data. There is some manual work, as bank holidays sometimes are different in US and UK, and very different in Russia, London Metals Exchange has it’s own idea for holidays too. My algorithm was the following – I entered any date found either from Bank of England, or from Russian Central Bank, or from Metals Exchange, and if the data was not present in other institutions extended previous day data.
The data is available for Gold since 1833 and for Silver since 1792, but I stopped on 1991, the year when we hear about Wizards currency first time.
The Converter itself works in both directions. It converts Dollars to Galleons and back. Since it is impossible to write down Wizard Money amount by decimal fraction, I made separate converters, using one database.
In case when the entered date corresponds to a weekend or a bank holiday the converter informs the user about this, and offers the exchange rate for latest available date. I used European dd/mm/yy date format, however it must to be no problem to change it to any different one.
If the entered date is before the earliest in the database the converter will ask to change it.
It is not necessary, that the amount of Sickles is within 17 and amount of Knuts within 29. If one would like to know how much in Dollars are 43 Sickles – no problem, the converter will give the Dollar value, and inform by the way that it is also 2 Galleons 9 Sickles.
Knuts and cents are rounded to the nearest integer value according to normal mathematic rules.
Everything is plain and straight, the one who will make a converter for HPLexicon website will find everything in Excel spreadsheets. I am not sure, but there might be a problem of the following matter: functions and months names in my Excel are in Russian, and I am not sure if Mr. Gates team had foreseen names conversion while using spreadsheets in different national versions. They should, but… Please contact me.
“Muggle to Wizard Converter” and “Wizard to Muggle Converter” are interface sheets, where the user enters dates and amounts and reads the result. These sheets are protected, except entry fields, marked in white.
“Historical data” – is the database of gold and silver prices with calculated rate and gold/silver price ratio. Historic average of the latter calculated in the bottom.
“WZG to USD rate chart” – is the chart, for easier understanding.
I also made comparative “WZG to USD and to GBP chart”, with GBP/USD line also.
It was interesting for me what happened in the end of October 1999, when Galleons picked up suddenly. So I made detailed chart for this period. Note, that Galleon strengthened against Pound more smoothly than against Dollar. As if British knew that the Gold will pick up. Maybe British Minister of Finance also had a call from some portrait in his office? We’ll never know, 1999 is out of the Saga timeline.
“Restricted Section” – is the service sheet, where calculations are done.
If format from “restriction format” is applied to any spreadsheet, the intruder will be asked not to proceed with viewing and changing it. In plain Gnomish.
I would welcome everyone to update the converter with more national currencies and with deeper retrospective historical view.
The converter requires daily update.