StrikeIron Blog

Consumption of Web Services in Excel - Foreign Exchange Rates Example

I've always thought of Microsoft Excel as a great client for consuming Web services. It is much easier to get an individual to use something that they are already familiar with rather than learning a whole new set of software tools and rules. As the old saying goes, people will use anything new, as long as it's 90% old. With Microsoft's claim of 150 million users of Microsoft Office, a significant number of people know how to use a spreadsheet and possess at least a basic understanding of how to operate one. Anything introduced within a known framework has a much greater chance of adoption, and this what makes Excel a great choice for building composite applications that use live Web services APIs.

With that in mind,  one way to demonstrate the value of SOAP and REST-based Web services as gateways to rich, constantly updated data sources is an example that uses Microsoft Excel. Here is a spreadsheet that utilizes StrikeIron's Foreign Exchange Rates that are updated every 30 minutes for 160 different currencies around the world. The source data comes from Interactive Data and is delivered via StrikeIron's Web services delivery platform that handles all of the administration of the data source. In order to get free trial access to the data, one must register their email address on the StrikeIron site. Then, the first time the service is invoked, the trial will be auto-provisioned.

As currency rates can fluctuate quickly and sharply at times, the capability to feed real-time currency rates into any spreadsheet-driven analysis scenario is crucial to anyone performing serious analysis. Otherwise, one is reduced to using outdated rates (which could be grossly inaccurate) or otherwise having to perform tiring iterations of look ups from multiple Web sites and cutting and pasting to achieve accurate analysis of global financial data. Fortunately, automating the collection of real-time currency rates is now much easier.

In this example, I am using StrikeIron's Live Data for Excel add-in that can be downloaded and installed in a few minutes. This is required because consuming SOAP and REST-based Web services and all of the XML interpretation that it requires is not something Microsoft provides out of the box.

Then, in order to incorporate the foreign exchange rates Web service, I simply need to "add a data connection" via the add-in to access the desired data.

When the Web service is invoked, data from the cells is sent to the Web service (in this case, the two currencies to convert) in order to obtain the requested exchange rate. When the rate is instantly returned, a simple Excel calculation is used to convert the current value in the cell from US Dollars to Euros (cell E5=B5*D5). It's that simple. This example demonstrates how easy complex financial analysis containing global variables can be achieved with great accuracy using the most relevant, timely data available.

If you would like to see a recording of a Webinar that walks through this example step-by-step, click here.

Also, if you have your own data that you would like to access similarly in real-time, that can be accomplished with the IronCloud platform. The data source can be become a "private" Web service and delivered via StrikeIron's platform to a controlled set of users who can access it with Microsoft Excel or any other tool of their choice, as long as it has an ability to consume XML-based Web services.

The Benefits of Putting Data “Out There”

When thinking about new ways to drive business, a good question to ask is if there is data that exists within your organization that might somehow benefit from being liberated (paradoxically, in a controlled manner) and available over the Web for integration into other applications and Websites. And if that data was available and accessible in a controlled, metered way, would it therefore create new opportunities to drive third-party innovation that ultimately leads to more business?

This of course won’t be true for all data that is maintained internally. Nobody wants private employee salaries available for integration by others on the Web of course. So perhaps they best way to demonstrate the value and the types of data that make sense to expose to third parties is via an example.

Starbucks (and I happen to know this for a fact) is in the business of selling coffee. So how can making data available on the Web as a service available for plug-and-play integration help them with the goal of increasing the cappuccino curve?

Let’s look at the database of coffee shops that undoubtedly exists within their organization. In fact, it is available to be queried on their Website, so we do know that this database exists. Now, anybody can go to their Website and search for nearby store locations. But what if this data was made available for integration so other applications could leverage that data when desired?

Imagine that within a CRM application, such as Salesforce.com or Netsuite, there was a “Nearest Starbucks” button, so during every conversation with a prospect, one could simply click this button and set an appointment at the nearest one for a meeting. Or, it automatically appeared as a field within every contact record.

What if Starbucks store locations could be integrated into real estate applications so before buying a home, one could consider whether or not there is one nearby and how far away it is, or perhaps a “Starbucks Factor” based on distances to nearest locations that could be calculated for fans?

What if dating sites auto-suggested the nearest Starbucks for a first cup of coffee meeting? What if my Facebook page automatically determined the closest coffee shop between local friends visiting my page and suggested it as a meeting place?

What if there were hundreds or even thousands of applications out there that utilized easy-to-integrate Starbucks coffee shop location data to enhance each of these applications? Mashups, widgets, rich Internet applications, social network applications, cloud applications, mobile applications, Twitter clients, and more could all leverage this data in amazingly creative and new ways. Wouldn’t this undoubtedly lead to Starbucks ultimately selling a heck of a lot more coffee (or CD’s, or whatever else they sell these days)?

And if Starbucks could then analyze the usage of this data to see which applications, locales, and “coffee drinker profiles” seem to utilize the data service, couldn't these usage trends provide a significant level of business intelligence that could be incorporated into strategic decisions?

And finally, what if Starbucks could have this up and running in days or weeks rather than years, with no internal infrastructure costs and maintenance associated with it, and yet delivered an industrial-strength, high performance, 24x7 reliable environment developers could count on? Would it then be attractive for Starbucks to unleash this data onto the world?

While this is a simple example, the point is clear. Organizations potentially have a lot to gain by selecting the proper sets of information contained internally and making it available to the outside world in a controlled and measured way with zero friction to those applications that can then leverage that data and enable it to be a source of value creation.

There are many examples of different types of data that when “on-boarded” for use on the Web can result in substantial value, from price lists, to live product information, knowledge bases, events, vertical data sources such as generic financial information, medical statistics, to several other types of data for public consumption. This same mechanism can also be used to deliver private sets of data to select partners, employees, and customers where access to that data is limited to a pre-selected set of users. Some of this data it might even be pertinent to charge for, especially when publicly available, creating whole new revenue streams for assets that already exist.

And the concept is not limited to just raw data. Business processes, functions, and other “units of business value” can be exposed over the Web in the same way for the same collection of benefits. The possibilities here are endless. One just needs a little caffeine to think of them.