Several currencies

[mito199]

Hi,

first of all I have to tell you that the spreadsheet is really great. I have just started to use it, but it really helps.

I have one question, maybe it is also suggestion:-)

I use a few different bookmakers with various currencies (euros, dollars, local europe currency) and I would like to track all my bets with all these currencies.
May I ask you for your recommendation how to do it?
I was thinking about to add automatically updated one sheet with exchange rates from some national bank, and all the bets will be enter with original currency and than recalculated for picked currency in performance summary.

Sorry for my english grammar:-)

Thanks in advance for your respond.

[admin]

Hi mito199,

Currencies are tricky because each time you make a deposit you are using a different exchange rate, so you may have, say $500 USD in your account, but not all of it was deposited at the same rate. Also, historical performance would have taken place at different exchange rates if you have made previous withdrawals.

The easiest way to get around this is to take a crude approach and simply use current exchange rates when calculating aggregate performance. To do this, my recommendation would be to add a currency list to the settings sheet. If you want to use your local European currency as the base it would look like:

Currency | Exchange rate
Local | 1.00
Euros | 1.41
USD | 1.07

I would then add a currency column to the Bookmakers table in the settings sheet, so the spreadsheet knows which exchange rate to apply based on which bookmaker you are using.

I would then then add a "Bet amount" column to the bets sheet and then have the stake column convert the bet amount into the base currency. The calculation would use a vlookup(), etc. to obtain the latest exchange rate from the settings sheet based on the bookmaker you have selected.

If the above seems too difficult, the simplest way to get around this problem is to maintain one copy of the spreadsheet for each currency. Obviously, however, this becomes a pain if you are using more than two currencies.

Hope that helps!

[germanovitch]

Hello,
I have seen your answer regarding the different currency.
You recommend to do some modification to the Excel spreadshett but I don't know how it it possible to do it.
Even, I would like to know of I can "personalize" my spreadcheet (suppress some texts, put mine...) ?
Thank in advance for your help and sorry for my English, I am French.
Thierry

[admin]

Hi Thierry,

Don't worry, your English is a lot better than my French

Currencies are difficult because exchange rates constantly vary. You may place a bet in one currency that consisted of deposits and two different times, which confuses matters. It would be possible to implement a Macro to obtain the latest exchange rates each time you place a bet, but I'm keen to keep the spreadsheet free of macros.

Feel free to modify the sheet for your own uses. The password to unlock each sheet is sport

[Sybawoods]

Thanks for a fantastic tool!

I have exactly the same need, but am stuck at the formula to insert in the new "stake" column. I need to convert my local currency to USD for one bookmaker. I have basic Excel literacy - sufficient enough to unlock all the sheets, add columns, create my own very basic formulae if necessary. I have followed all your advice:

- created a currency list in settings sheet
- added a currency column to the bookmakers table
- added a bet amount column to the bets sheet

All I now need is the formula that does all the work . Is there any chance you can give an example of what this formula might look like, assuming one has followed your instructions above? Much appreciated in advance!!

Hi mito199,

Currencies are tricky because each time you make a deposit you are using a different exchange rate, so you may have, say $500 USD in your account, but not all of it was deposited at the same rate. Also, historical performance would have taken place at different exchange rates if you have made previous withdrawals.

The easiest way to get around this is to take a crude approach and simply use current exchange rates when calculating aggregate performance. To do this, my recommendation would be to add a currency list to the settings sheet. If you want to use your local European currency as the base it would look like:

Currency | Exchange rate
Local | 1.00
Euros | 1.41
USD | 1.07

I would then add a currency column to the Bookmakers table in the settings sheet, so the spreadsheet knows which exchange rate to apply based on which bookmaker you are using.

I would then then add a "Bet amount" column to the bets sheet and then have the stake column convert the bet amount into the base currency. The calculation would use a vlookup(), etc. to obtain the latest exchange rate from the settings sheet based on the bookmaker you have selected.

If the above seems too difficult, the simplest way to get around this problem is to maintain one copy of the spreadsheet for each currency. Obviously, however, this becomes a pain if you are using more than two currencies.

Hope that helps!

[admin]

Hi Sybawoods,

I would highlight the currency list in the settings sheet (both columns), then right click and select Define Name and input the name CURRENCIES.

Presuming you have placed the currency column right next to the bookmaker name in the bookmakers table, I would highlight the bookmaker name and currency columns in the bookmakers table, then right click and select Define Name and input the name BOOKMAKER_CURRENCIES.

For the bets sheet, I would enter all of my stake amounts into the new bet amount column. I would then have the stake column use the formula:
=IF(LEN(VLOOKUP(C11,BOOKMAKER_CURRENCIES,2,FALSE)) >0,K11*VLOOKUP(VLOOKUP(C11,BOOKMAKER_CURRENCIES,2, FALSE),CURRENCIES,2,FALSE),"")

Note that this is for row 11. For this formula the referenced cells are:
C11 - bookmaker
K11 - bet amount (new column)

You would need to tweak this formula to reference the appropriate columns based on your spreadsheet version and where you inserted the bet amount column.

Hope that helps! Let me know if you have further questions.

[Sybawoods]

Oh wow, THANK YOU!!! Just tried this and had it working in < 2mins. Really appreciate this assistance, thanks again.

[Tore]

Managing in less than 2 minutes is for sure impressive!

I have spent ca 2 hours on this, and still can´t figure it out.
I am pretty sure I have managed to do all the steps correctly. And it still does not work. I assume there are some basics I am missing. I am using Norwegian excel, does that mess things up?

What kind of formatting of the columns is needed for the
- currency list in the settings sheet
- currency column in the bookmakers table, it is only one, I assume?
- bet amount column

[admin]

Hi Tore,

Using Norwegian Excel shouldn't mess things up. Formatting of the columns shouldn't matter either.

The main posts in this thread to follow are #2, #5, #6.

Send me a PM with your email address if you continue to have problems. Let me know which version (version number and advanced or standard) of the spreadsheet you are using and I will email you a copy with currencies enabled.

[mynik]

I am using Norwegian excel, does that mess things up?

Hi Tore,

The problem here might be (as with other Excel-Versions in different languages) that the use of English functions (eg. IF, LEN and so on) isn't possible.

Two ways to solve this problem:
*) Install the English Language Pack for Microsoft Office 2007 (or whichever version you're using) Norwegian version
*) Use the Norwegian equivalent of those functions, eg. IF = HVIS and so on (see http://www.piuha.fi/excel-function-n...norwegian.html for a function-name translation table)