Welcome guest, is this your first visit? Create Account now to join.
  • Login:

Welcome to the Australia Sports Betting Forum.

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed.

Betfair
Results 1 to 10 of 10
  1. #1
    New Member
    Join Date
    May 2014
    Posts
    1

    Several currencies

    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.

  2. #2
    Moderator
    Join Date
    Aug 2011
    Posts
    989
    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!

  3. #3
    New Member
    Join Date
    Jun 2014
    Posts
    1
    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

  4. #4
    Moderator
    Join Date
    Aug 2011
    Posts
    989
    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

  5. #5
    New Member
    Join Date
    Oct 2014
    Posts
    7
    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!!

    Quote Originally Posted by admin View Post
    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!

  6. #6
    Moderator
    Join Date
    Aug 2011
    Posts
    989
    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.

  7. #7
    New Member
    Join Date
    Oct 2014
    Posts
    7
    Oh wow, THANK YOU!!! Just tried this and had it working in < 2mins. Really appreciate this assistance, thanks again.

  8. #8
    New Member
    Join Date
    Dec 2014
    Posts
    1
    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

  9. #9
    Moderator
    Join Date
    Aug 2011
    Posts
    989
    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.

  10. #10
    New Member
    Join Date
    Aug 2014
    Posts
    4
    Quote Originally Posted by Tore View Post
    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)

 

 

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •