Updating currency exchange rates in Microsoft Dynamics CRM 2011

by Dmitry Kirsanov 22. November 2011 00:36

As promised earlier, I’ve created a small utility which updates currency exchange rates for currencies used by Microsoft Dynamics CRM 2011 server. It’s fast, reliable and can be run in unattended mode, so you can set it once and forget about it.

A bit about Microsoft Dynamics CRM first

Microsoft Dynamics CRM 2011 is the most advanced CRM system created so far. It allows you to keep track on your customers, partners and rivals and monitor relations in real time. So you can create marketing campaign for all your customers in Denmark with turnover higher than 200 000 euro and who’s main industry is Finance. And send them personalized proposal. And each of that customers will reply to manager, assigned to that customer. And you will track the success of that action in real time, being able to test your genius as CEO. Well, not only that.

CRM contains very powerful reporting, based on SQL Server 2008 reporting services. With more than 25 reports available out of the box, you can easily create new ones. And without knowledge of any programming language you can make modifications to the structure of information you keep.

But if you know any of .NET languages, then you can make CRM to become the engine of your business, as it will be capable of keeping the workflow of your business running. I was once part of the project, quite ordinary one, for the company who’s business was waste disposal for a 1M city. CRM tracked not only the customers of that company, but even created schedules for truck drivers and could make an effective itinerary for their GPS navigators. And it knew what waste disposal container should be moved where and when, and prepared all the documents for that.

Well, not only that, CRM is available in a lot of languages, including not only “big” ones like Swedish and Danish, but also some very “local” ones, like Latvian, so if you are having very geographically distributed international organization, each of your employees could work with the same system using his own language, so that would shorten the “learning curve” for each new employee. And talking about the learning curve – the help system is awesome, with informative video clips and localized help.

Another thing to add to this heap of praises – it’s collaboration with another Microsoft product – the Microsoft Outlook. Be it either 2010 or 2007 (or even 2003, I think) – you will have access to all CRM functions right from your Outlook client. Or from web browser. And what’s important – you will be able to add correspondence with your customers automatically from Outlook to CRM, so it will handle new contacts belonging to each particular customer and you won’t waste your time on filling endless forms anymore.

And last, but not least, is the cloud offering from Microsoft, which allows you to rent installation of software with monthly payments, instead of owning your own server side installation. Software as a service (SaaS) in action.

It’s not a bug, it’s feature!

As you see from the description, the system is complex. It’s tied to other complex products, such as SQL Server, Exchange, Outlook, other Microsoft Office products, but from all the complexities one of the biggest ones is the database of Microsoft Dynamics CRM.

You can manage multiple companies with single installation of Microsoft Dynamics CRM and even rent access to CRM to that companies, and for each company CRM creates a new database in SQL Server, with quite complex and sophisticated structure, and one of the major features of that database is that all data in Microsoft Dynamics CRM 2011 (or even in previous version, Microsoft Dynamics CRM 4 – it’s been that way like always) is stored in form of metadata. Metadata basically means that you can have your own structure of data records, with your own custom fields, and these fields may have relations to other fields in other records, custom or not. All that – without modification of the database itself.

And this adds to the complexity of solution, although this alone allows tremendous extensibility. And because of that complexity, it would take a lot of time for Microsoft to design and implement a user interface to edit many of settings and data of CRM. Remember Windows Registry Editor? It allows you to change system settings for which you have no graphical user interface (GUI) or any other means of editing others than through editing the registry.

And so it is with Microsoft Dynamics CRM. CRM developers know the rule of a thumb – never change the structure of the database of CRM and never add anything to it. Because Microsoft didn’t document it and can easily reshape it when it suits the needs. Therefore Microsoft Dynamics CRM has Software Development Kit (SDK) which allows to extend CRM and transform it into something incomparably bigger.

However, some things just needs to be changed, but officially they “can not” be changed, just because Microsoft didn’t make a GUI for it. And so, if you want to change the default base currency to use in your CRM, you (that’s official, I’m not kidding) should create new company and transfer all data from old one to the new. In my first post about Microsoft Dynamics CRM 2011 I described how you can do it by changing 4 values in one record in the company’s database.

But what about maintenance?

As international company, you’re  most likely working with many customers using their national currencies. Even in European Union, not every country is using Euro, and there are more than 150 currencies in the whole world. So, one of your employees needs to check the latest exchange rates and update them using web interface. Nothing terribly difficult, just time consuming.

In my installation of CRM I am having just 7 currencies, one of them being the default currency, and that’s Euro. So at least once per week someone needs to change the exchange rates for 6 other currencies. That means – someone should not forget to do it, and when CEO or one of managers is generating one of many reports, it’s not precise by default. But could it become such?

Solution

To solve that problem, I decided to write a small command line utility which would update currency exchange rates right in the database. I decided it to be a command line utility simply because it could be running without user interaction as a simple scheduled task, or by systems administrator / dedicated personnel.

Instead of using CRM SDK, I decided to have direct connection to the database through the Microsoft Entity Framework, although the only thing I do is updating one field in one table. Program could not corrupt any data. If the schema of the database table will change, the program will stop working, and that’s it. Using CRM SDK would require installation of add-on through the CRM interface and I didn’t want any overhead.

I noticed that the structure of the table related to the currencies didn’t change since the version 4 of Microsoft CRM, so I am pretty sure it won’t change during the life span of Microsoft Dynamics CRM 2011.

Because of using the Entity Framework 4, the .NET framework required by this utility is .NET Framework 4 Client Profile (lightweight version of .NET Framework 4).

On the next step I needed to get the reliable source of currency exchange rates. Fortunately for me, I had experience working with currency exchange rates before, so now I didn’t have to waste time on research, I already knew what I should use to make a simple but reliable unattended utility that will change the rates of all currencies to the current ones.
So I’ve chosen the service of Mondor Software, their Currency Exchange XML Web Service. I’ve chosen it over the alternative from Oanda because of huge price difference and proven reliability. Also, I had a good history of cooperation with Mondor on Captcha project, for which I wrote a small ASP.NET sample and math functionality. I am also the official maintainer of that project at the moment.

It took about one hour to write and test everything. So here is the description, screenshots, binaries, source code and installation package for it. It is freeware and you can use it as long as you want.

However. It depends from the mentioned XML Web Service, which is not free, although very cheap. As you can see in the price list, it is from US$ 5. For my needs I took “Annual 100” type of license, which means I can use it for one year and make up to 100 requests per day. However, I need only 6 requests per week. And if I would take “1K requests” license key for US$ 5.85 (price as of the day of writing this post!), it would last for 166 weeks, which is more than 3 years, or about 14 cents per month. That’s much cheaper than to ask someone to do it manually, don’t you think?

So, in order for this thing to work you have to acquire the demo key, which will work for 2 (or 3?) weeks and so you’ll be able to start. Then you can get the key you need.

CRM 2011 Currency Rates Provider utility

CRM-currency-rates-changing-utility

In order for it to update your database, you should simply run it. There are few parameters to make life easier:

/v – verbose. You will see more informative messages than usually. For example – the information about each update it makes to the database.
/s – silent. No messages excerpt warnings and errors. It will warn you when your key for Mondor service is about to expire, by the way.
/l – (that’s small L) – prepare the Event Log. You should run program with this switch just once, and do it as System Administrator. After that, you will see warnings and errors, if any, in your Application Event Log and the source will be “CRMRates”.

CRMRatesSShot2

It will look like that.

As you can see, the first line informs about the balance of your XML Web Service key. The date of expiration and if it’s calls based – how many requests can be done, either today or at all. Just to make sure.

Before running this application, you will need to have 2 changes in the configuration file. The connection string to database and the key to currency rates web service. If you are installing this program using the MSI installer, it will ask you questions in the dialog box and will change the configuration file for you. If you are downloading the files as binaries or source code – you will have to change everything by yourself.

By the way, there is one glitch in the MSI package – the checkbox for creating the Scheduled Task doesn’t work. I will fix it later, and when I will do it – delete this section of the post. However, it doesn’t break anything, you will just have to create the Scheduled Task manually.

The last thing you need to know – you should run this program as someone who has rights to edit the CRM database. Either give domain / local user rights to UPDATE and SELECT information from TransactionCurrencyBase table in your CRM database, or just run it as system administrator or someone with dbo privileges at SQL server.

Well, here are the files:

1. MSI installation. You can simply run MSI, but running EXE will ensure you have .NET Framework 4 Client Profile installed. You will be prompted to install it if you don’t have it. In that case it will be downloaded directly from Microsoft’s website.

CRMRatesSetup.zip (1 Mb)

2. Binaries in ZIP file. Remember to edit the .config file before running the EXE, or it will fail.

CRMRatesBinaries.zip (21.30 kb) 

3. Source files. This is C# project for Microsoft Visual Studio 2010.

CRMRatesSource.zip (107.84 kb)

Click here to get the access code for currency rates web service used by this utility. It should be unique for each user, so it’s not provided with the utility.

Modifying Precision


If you would like to increase precision from 4 numbers to more, edit this line of code:

cur.ExchangeRate = Decimal.Round (newRate, 4);

And change “4” to another number of digits after the comma.

blog comments powered by Disqus