This tutorial is a guest post from Ashwin A.K from Speridian Technologies  - a global business and technology solutions provider. Speridian's business and technology experts help clients modernize their businesses through Digital Transformation.

Exchange rates are subject to continuous fluctuation, hence require constant updating. Countless hours are spent each day by our finance team on this mundane task alone, which could have been utilized for activities that actually require their creative intelligence. In this tutorial, let's see how we can solve this with RPA.

Setting The foundation for our problem statement

Each day, our finance team manually logs into a designated banking portal where they will find the latest Euro exchange rates for a set of currencies, the company is interested in maintaining. The rates from the previous day are extracted using APIs and updated before pushing it back into the company’s database. Once that is done, the next task is to perform data manipulation onto a series of excel spreadsheets with the latest exchange rates.

Prerequisites:

For the purpose of this demo, we will use Microsoft SQL Server, and focus on Euro conversions to the following currencies:

• INR
• USD
• CAD
• AED
• KWD

To retrieve the Euro Exchange Rates, we will use the API provided by https://exchangeratesapi.io/. You have to create an account to generate an access token and receive the URI. Once all of the above prerequisites are set up, you are good to go.

Automating Exchange Rates Retrieval

First, we have to ensure our Database contains a Table with a predefined schema and currencies to pull out. Remember, we only wish to find the exchange rates for a fixed set of currencies, and if there is no table or currencies to work with, our bot will crash. Here is a screenshot of the Table we will be working with.

To pull out the currencies, drag in a Database: Connect action followed by a Database: Export To DataTable. The following connection string is provided after setting the connection mode to Default. jdbc:sqlserver://localhost;databaseName=ExchangeRates;integratedSecurity=true;

You may find out more about setting up the Database Server from here as well as more on the Database actions from here. The Second action will export only the values we are interested in, i.e., the currencies we wish to update. We could also perform other operations, such as retrieving the entire table and comparing today’s exchange rates with the previous days, but we will limit ourselves to simple updates. The operation is performed using the query shown below: SELECT COUNTRY FROM Euro_Exchange_Rate

Retrieving Currency Conversions Through API Calls Once you register an account with exchangeratesapi, you will receive an Access Token and URI with which you may perform a multitude of operations detailed here. Let me remind you – our goal is to simply retrieve the latest Euro currency conversions for the set of currencies our company is interested in maintaining. This will be achieved in the next Step. Hit It! The URI for hitting an API call that will fetch us the latest value is as follows: http://api.exchangeratesapi.io/v1/latest?access_key=*AccessTokenGoesHere*&format=1 There is also an option of embedding the exchange rates within the URI itself. http://api.exchangeratesapi.io/v1/latest?access_key=*AccessTokenGoesHere*&base=EUR&symbols=INR,AED,USD,KWD&format=1

Tinker around with it, and see if you can come up with something creative. In addition to currency rates, we also wish to keep track of the dates on which the exchange rates were updated, hence the DateTime: To String Action. Initialize, Query and Update Once we hit the API, it returns results and stores them into a dictionary variable. The data we require is housed inside the Body, but merely pulling out the body won’t do it for us. The dictionary value itself is in JSON format, so we have to query the values we are interested in, and to do so, we will utilize the JSON Object Manager Package available in the Bot Store. First, drag in an Initialize Action and plug in the Dictionary variable referencing the correct key, which in this case will be the Body.

After that drag in a Loop: For Each Row in Table which will iterate through the Table we just pulled out from our database.

The exchange rates are queried from the Dictionary and updated in the Database using the following query: UPDATE Euro_Exchange_Rate SET RATES = $sExchangeRate$ ,DATE = '$sTodaysDate$' WHERE COUNTRY = '$rTableRow{"COUNTRY"}$';

And with that, we have automated the first task! Performing Calculations in Excel For the sake of brevity, we won’t delve into too much detail – just enough to get a feel for it and to let that intuition guide you towards a solution. Once we have our updated exchange rates, we are set to move onto the final stage, which is to update the excel spreadsheets present in a given directory. First, let’s have a look at the data we will perform operations onto.

As you can see, many of the exchange rates are either erroneous or in an improper format and our bot should be capable enough to handle such operations. Our bot has to look up the currency in Column B(Currency) and multiply the value in Column A(Amount) before finally setting the computed value into Column C(Total in Euros). We have several spreadsheets as shown below:

To process these values, we first have to pull out the updated Table and store the Country and Exchange Rates into key-value pairs. Can you guess which variable is most suited for this operation? Here’s a hint, we have used it before. . . . . . Yes, you are right, it's a Dictionary variable.

With this, we may now move onto the final stage. Automating Currency Conversion For Each Excel Spreadsheet To access each spreadsheet, we first drag in a Loop: For Each File in Folder. The file name and extension is stored inside this dictionary variable, which makes things super convenient for us. After that, we open each Excel spreadsheet and perform the conversion using suitable Actions as shown in the screenshot below.

Spend some time thinking about the sort of errors you may encounter and how they may be handled. Try-catch actions are a must for error handling, but that is only done to ensure our bot doesn’t crash. When the data is wrong, we have to program our bot to either create logs or to include comments inside the file as shown in the screenshot below:

This operation is simplistic, but our automation can be developed even further to perform numerous complex operations, either by triggering Macros or by invoking VBScript. Once the Excel operations have been performed, the next step is to zip the folder and send it across to stakeholders.

And with that, we have not only updated the exchange rates, but we have also performed a series of important operations and delivered the results in an incredibly short span of time. Our finance team can devote themselves to activities that require their creative input. This is one such example of how powerful RPA can be. The bots are only getting smarter, and there is a lot more to come.

Conclusion

Automation 360 provides the ability to build simple to super complex bots to suit your specific requirements. As you can see, in this exercise, we interacted with MS SQL Database, APIs and Excel spreadsheets to built our bot. Before building your bot, have the requirements clearly defined along with the potential datasets which the bot will have to face in the production environment.