What is Excel Mashup and who can use it?

Summary of the post in several sentences

• Excel capabilities in terms of remote access and document collaboration today are at least comparable to Google Docs;
• An Excel document can be integrated into html pages while retaining a significant part of the functionality, i.e. formulas and pivot tables will work;
  
• I, like any other person, can publish my Excel document in such a way that your web application, as well as anyone else, can:
  
  1. draw and open it to manually enter values ​​into cells,
  2. directly interact with it through JavaScript;
     
• Microsoft has a website dedicated to integrating Excel documents into web applications; also in nature there are jQuery plugins that make this integration quick and painless;
  
• From a practical point of view, the user receives the following buns:
  
  1. JavaScript provides ample opportunities for visualizing and animating Excel tables, as well as creating interactive applications and dashboards based on them;
  2. Ability to work with Excel documents on tablets and mobile devices;
  3. A convenient tool for creating working prototypes of management reporting systems.

Cautious opinion of the author that Excel is cool

I really like table processors for their simplicity and versatility. Here in one bottle there is a database, and modeling tools, and visualization. Of course, specialized solutions in each of these areas are significantly superior to table processors in functionality and scalability. But often also at a price: this is not only about the cost of licenses, but also about the cost of training employees to work with new programs. Table processors are good in that they are almost free, and they can be used to one degree or another by most people who have ever worked at a computer, from a schoolboy to a senior citizen. This is where the question arises: do we need all the power of specialized systems to solve a specific problem? Or is it enough to “rivet a plate and a couple of diagrams in Excel”?

Personal experience suggests that to solve the vast majority of one-time tasks, it is enough to just “rivet on the knee”. And only if you want to streamline the solution of a large number of homogeneous tasks, or to turn over large volumes of data, or to make some kind of super-beautiful interactive visualization, then it makes sense to think about investing in specialized systems. In fact, imagine yourself in the place of an ordinary manager or analyst who is tasked with looking at some data, predicting possible development scenarios and suggesting options with an analysis of the possible consequences. I am convinced that the first thing you will do is open Excel and begin to drive in your data there and do calculations on them.

Why Excel, not Google Docs, or, say, OpenOffice Calc? Yes, because "it’s so established here." De facto Excel has been and remains the standard of table processors. If you are a manager or analyst working for someone else’s business, and this business is not a technology startup, then with high probability you are actively using Excel in your work. And this is not a matter of some unique functionality: it’s just that Excel has existed for a very long time, just like most companies that create jobs today for mid-level managers. These companies once many years ago simply had no choice, but now there are no serious reasons to change something.

It should be recognized, however, that Google Docs has a good chance of changing this balance of power thanks to its remote access and document sharing capabilities via the web. The world is changing, and work with documents in the "clouds" from exotic turns into a routine. Access to Excel documents via the web has existed for a long time, but only by means of SharePoint. I will not go into details, I will only note that for an “ordinary” person this was almost equivalent to the lack of web access. As a result, “ordinary” people and young organizations began to actively prefer Google Docs. Since then, Microsoft has made efforts to make a difference, and today we can say that office products are transferred to the "clouds". Online versions of Microsoft Office products have appeared: free, but with truncated functionality. Full offline versions still have to pay. Both are integrated with OneDrive: Microsoft's cloud drive (formerly Skydrive).

Surprisingly, the online version of Excel is called Excel Online. Some interesting features of this product will be discussed further on as an example of one of the tasks that I solved when I was a managerial consultant.

The author shares his experience in creating Excel Mashup

So, given:

1. The consultant developed a financial model in Excel;
2. This model needs to be presented to a very serious uncle, financial director of a very large bank;
3. My uncle was very busy, almost all the time having meetings, but he asked me to drop the materials beforehand and said that he would be able to look in a taxi to the airport on his iPad;
4. The consultant has no idea which operating system and which applications are on this iPad, and does his uncle have a tendency to call his tablet on an android an iPad;
5. The consultant really wants the charts to be more beautiful and interactive than those that Excel can do;
6. There is an agreement with the client about not using VBScript macros in Excel;
7. And the consultant is sure that the uncle will want to play with the numbers in the model, and you need to make sure that he does not break anything during the game.

Question: what to send to the uncle? If it’s just an Excel document, then uncle with a high probability will not be able to read it on the iPad. And if he can, then there is a risk that he will misunderstand, he will introduce the wrong numbers into the model, break it and remain with the prejudice that the model does not work. As a result, it will be necessary to make a new appointment, look for free slots in the uncle calendar, and the project will drag on for a couple of weeks, or even a month.

In this case, it was decided to make up a web page, which step by step showed the uncle the essence of the presented model, similar to slides in PowerPoint. The main difference from the slides was interactivity: at some stages, uncle, through HTML forms or jQuery sliders, could add their own parameter values ​​to the model. The new values ​​were translated into Excel-document cells associated with the form or slider, after which the model was automatically recalculated. Recalculation results were almost instantly displayed on pretty jQuery charts. A bunch of HTML elements and model cells was implemented in JavaScript. Charts used from Highcharts. Then the web page and the Excel document were placed in the client’s SharePoint, and the uncle was sent a link to the page that he opened in the browser on his iPad (it really turned out to be an iPad),

In this example, the client had SharePoint, which made the task easier in terms of ensuring data confidentiality. If SharePoint is not available, you will need to upload the document to OneDrive, give public read access and connect to the document through the token. Since the access is public, anyone who knows the token will be able to read the document. The most reliable way to ensure data confidentiality in this case is not to upload it to OneDrive. In other words, an empty Excel template is saved on OneDrive, and data is read into it from its own server via secure channels at the time of access, after the page in which this empty template is embedded is loaded in the browser. Less reliable but a simpler way to implement it is to communicate a link to a page to a limited circle of people and provide access to read the file for a predetermined short time (similar to how webinars are made). This option is suitable if the data do not want to be disclosed, but nevertheless they are not top-secret.

As it turned out, the implementation described above is a practical example of a phenomenon called Excel Mashup. The bottom line is that you can embed an Excel document in a web page and use JavaScript to provide interaction between HTML elements and cells in the Excel document. To understand the principles of creating Excel Mashup, the first thing they usually do is go to www.excelmashup.com/jsapi . There you can find various scenarios for using the JavaScript API provided by Excel. Good people (for example, this person: www.neowin.net/news/microsoft-offers-excelmashupcom-for-quick-web-apps ) write that the site is launched by Microsoft itself. However, the suspiciously small number of likes and the controversial design raise doubts about the authorship of such a reputable company, but by the way this is not so important.

Another thing is important: between the moment when I first read this site and the moment when I realized my first mashup, a lot of time passed. This is partly due to the fact that the pieces of code published on the site www.excelmashup.com are not very informative: for example, a function is defined, but it is not indicated in which place it is called, which scripts should be pre-connected, what HTML should be markup. I had to guess a lot, and I think that many people who had less time or patience could not go all the way to the end. Another problem was that the script from Microsoft, which implements the Excel Web Access class, conflicts with jQuery, as a result of which the plugins begin to behave inappropriately.

For those who don’t want to step on this rake, I’ll give a few examples of how to integrate Excel and HTML by writing a few lines of code in JavaScript:

• Example 1 (the simplest) is to embed an Excel document by token and pass the values ​​of the range of cells specified by the user to the JSON. Sources here: jsfiddle.net/ugBe2
  

• Example 2 (more complicated) - embed an Excel document by token, then change the value of any cell specified by the user via the HTML form, after which the book will be recalculated and the results of the range specified by the user will be saved in JSON. Sources here: jsfiddle.net/RVjLD
  

• Example 3 (interactive with binding jQuery plugins and initialization) - there are three elements, each of which is associated with the corresponding cell of the Excel document. Two of them are sliders associated with cells B1 and B2, respectively. The third is the speedometer associated with cell B3, which is calculated as a formula from B1 and B2. As in the previous examples, the Excel document is opened by token. At the end of the load, the initialization function is called, which assigns the initial values ​​recorded in cells B1: B3 to the sliders and speedometer. When the user “moves” the sliders, a function is called that writes the new value to the cell corresponding to the slider (B1 or B2). After that, the value in cell B3 is recounted and transmitted to the speedometer. Sources here: jsfiddle.net/KMtnc
  

All three examples use a small jQuery library specifically designed to simplify the integration of Excel and JavaScript. According to its contents, I am ready to comment separately as specific questions arise. Library sources are here: github.com/xls2web/x2w

Please note these examples are pure HTML-CSS-JavaScript. If you create a new html file on their base on a local workstation connected to the Internet and open it directly in a browser without a web server, the result will not change. Not on all browsers, of course. We will not point the finger, but on the latest versions of Chrome, Firefox, Safari and Opera this trick worked, but on one very popular browser it didn’t (the embedded Excel document did not open). However, the fact is that you can make your Excel Mashup without installing or renting a web server. This, in turn, offers interesting opportunities for office Excel ninjas. Corporate information security policies rarely allow office workers to install a web server on their workstations, but almost everyone has browsers and Internet access,

Well, and who may need all this?

In my opinion, first of all, by the very same office ninja, consultants, analysts and managers. Directly related to this guard, in my life I made a decent number of models and forecasts in Excel: various profitability calculations, scoring, allocation and other things from the world of finance, reporting and risk management. Each time, the most difficult moment, oddly enough, was not the development of the model, but its presentation to the bosses and clients: various “experienced managers”, “strong business executives”, “iron ladies” and “energetic young alpha leaders” who so often climb to the top of the corporate hierarchy. But in order to be able to report to the “celestials”, you still need to go through the filter from a little less successful gray-haired and not very advisers, members of various committees and other “experts”. Some are trying to understand the details, others say: "Explain to us as if you were explaining to a five-year-old child." Some think with tables and numbers, others have imaginative thinking, while others must definitely feel with their hands before drawing any conclusions. One thing unites all of them: these are extremely busy people and the time they devote to you should be considered as a gift of fate.

At one time, I came to the conclusion that speaking to these comrades, I do not present a model, but rather tell a story. And often for the project results, how folding the story turns out is much more important than how good the mathematical and logical component of the model is. At the same time, mathematics and logic cannot be neglected, of course. Among your listeners there will always be someone who wants to dig deeper into the details or consider some original script at the very moment when you are least prepared for this.

Thus, to make a set of materials suitable for everyone is a great art. Excel is necessary here, but one cannot do it alone: ​​its capabilities as a tool of visual accompaniment and control over the course of the story that you are telling are too small. Usually, PowerPoint is used for these purposes, but here the trouble is with interactivity. PowerPoint perfectly tells your story as long as no one asks questions. As soon as it is required to change model assumptions on the fly, PowerPoint ceases to be your assistant. You can, of course, try to make the presentation interactive using macros in VBScript, but it’s better not to try. It often happens that the same slides are displayed differently on different devices, and the more difficult the presentation, the more likely it will happen at the most inopportune moment. That is why consulting companies have decided to convert presentations to pdf before sending them to clients. And that is why in the “uncle” story, one of the limitations was not using VBScript.

With the growing popularity of jQuery and the number of useful plugins, making presentation in HTML-CSS-JavaScript has become a little more difficult than in PowerPoint. And for me personally, for example, it’s even easier, because finding and connecting a good ready-made HTML-CSS template is much easier than a decent PowerPoint presentation; take the same bootstrap. HTML-CSS-JavaScript is much less affected by the PowerPoint “diseases” described above. In terms of interactivity, JavaScript provides 100 points ahead. Of course, checking the document on Chrome, Firefox, Safari, Opera and another browser will not give a 100% guarantee that the client will see the presentation exactly as you intended it. But 90% will give, and this is much more than PowerPoint can give. In general, it became possible to make an interactive presentation based on your Excel model.

There is another area where Excel Mashup could be effectively applied. Most of the Excel documents mentioned above are designed to allow managers to make forecasts and ultimately make management decisions based on an analysis of facts and assumptions. There are also specialized systems designed for this purpose. This is primarily about systems that position themselves as Management Information Systems, Business Performance Management and Business Intelligence. In Russian, they are often called corporate or management reporting systems. In large organizations, such systems are often implemented as a replacement for hundreds of disparate Excel documents. The goal is usually to centralize and speed up the reporting process,

Projects for the implementation of such systems in the early stages often include stages at the output of which a prototype system is obtained. The prototype is a generally working system, but not reliable enough and scalable to use in industrial operation. It is very important that the prototype must be created quickly and with little effort. It is during prototyping that using Excel Mashup can become effective.

The fact is that users and business analysts of such systems are for the most part also advanced Excel users. And an Excel document is such a thing that can be done relatively quickly. If you are a business analyst who knows how to do Excel Mashup, then this means that you can make a prototype of your report or dashboard in relatively short time “on the knee” without involving programmers. Such a prototype is better than any task statement will explain to the development team what the customer wants. In addition, in the absence of critical functional deficiencies, the customer can use the prototype to solve their problems until a “reliable and scalable solution” is put into operation. Quick prototype, satisfied customer, understanding between analysts and developers:

In conclusion, I note the following: in general, we have before us a simple thing to study and use with a certain potential. It may be useful, especially if you work a lot with Excel. Well, I send the rays of my respect to Microsoft, I think they are moving in the right direction.