Building financial models with a babel fish!

By Nick Crawley on May 18 2009

Working in an international environment

An interesting parameter of building project financial models is that we get to travel all over the world and / or work with clients in a wide variety of locations. Many of them use different Excel versions and I have had lots of fun with circular references in Japanese and Debug messages in Arabic….to name but a few! The babel fish from hitchhikers guide to the galaxy would be really useful sometimes.

Well have you ever been sent a file that opens to lots of #NAME error messages ? Of course you have! The most obvious fix is to activate the Analysis-Toolpak add-in. However I recently opened a workbook that had the same problem and it was because the workbook was written in German (you’ll see formulas like BRTEILJAHRE). When I had a closer look, I noticed something interesting, there were only a few formulas that are in German. 80% of it is in English. How strange, I thought - why not take the all-or-nothing approach?

Excel has a built-in multi-lingual translator

After doing some research I realise that there is actually a built in translator in Excel that translates functions into local languages. Therefore 80% of the workbook that I saw has already been translated and I’d imagine if I were to send that file back to a German Excel, I would expect that all the functions that I saw in English would be in German. I also bet 90% of Excel users out there are not aware of the existence of that functionality - so check it out if you deal with this situation a regularly.

But that’s not the end of the story.

However, the built in capability only translates basic functions (functions that can be run without the analysis toolpak). So, functions like EOMONTH, XIRR, XNPV, YEARFRAC and etc. etc. will not work. However, I have found a solid tool to get around this issue. It is called the “Analysis ToolPak Translator“. This is an add-in which translates all functions into the local language. So all you have to do is select the language source and hit translate! NOW, you can boast to your fellow friends and colleagues that you have built a workbook in Spanish or even Chinese! Like all add-ins and non-standard features test it thoroughly and don’t rely upon it - but its good to know these resources are out there. Let me know if you have any other interesting language pack add-ins.

If you like this then take a look at my recent blog on the challenges of international keyboards.