Geekly Articles each Day
📜 ⬆️ ⬇️

What is Excel Mashup, and to whom it may be useful?

Summary of the post in several sentences




Careful opinion of the author that Excel is cool


I love tabular processors for their simplicity and versatility. Here in one bottle and a database, and modeling tools, and visualization. Of course, specialized solutions in each of these areas significantly exceed the table processors in terms of functionality and scalability. But also often at the price: it is not only about the cost of licenses, but also about the costs of training employees to work with new programs. Tabular processors are good because they are almost free, and most of the people who have ever worked at a computer, from schoolchild to retired, are able to use them in one way or another. Then the question arises: do we need all the power of specialized systems to solve a specific problem? Or is it enough to “rivet a tablet and a pair of charts in Excel on your knee”?

Personal experience suggests that to solve the overwhelming majority of one-time tasks, it’s enough just to “rivet on your knee”. And only if it is necessary to put on the solution of a large number of homogeneous tasks, or to roll in large amounts of data, or to make some 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 has been tasked with looking at some data, predicting possible development scenarios and proposing options for action with an analysis of possible consequences. I am convinced that the first thing you will do is open Excel and start driving your data there and doing calculations on them.

Why Excel, not Google Docs, or, say, OpenOffice Calc? Yes, because "so instituted here." Excel de facto has been and remains the standard for tabular processors. If you are a manager or analyst who works for someone else’s business, and this business is not a technological startup, then you most likely use Excel in your work. And it's not about some unique functionality: just Excel exists for a very long time, just like most companies that create jobs for middle managers today. Many years ago, these companies simply had no choice, and now there are no serious reasons for changing something.
')
However, it should be recognized that Google Docs has a good chance to change this balance of power due to the possibilities of remote access and collaboration with documents via the web. The world is changing, and working with documents in the "clouds" from the exotic turns into a routine. Access to Excel-documents through the web has existed for quite some time, but only by means of SharePoint. I will not go into details, let me just 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 change the situation, and today we can say that office products are transferred to the "cloud". There are online versions of Microsoft Office products: free, but with truncated functionality. You still have to pay for full-fledged offline versions. Both are integrated with OneDrive: a cloud disk from Microsoft (formerly called Skydrive).

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

The author shares his experience creating Excel Mashup


So, given:
  1. The consultant developed a financial model in Excel;
  2. This model should be presented to a very serious uncle, financial director of a very large bank;
  3. The uncle was very busy, almost all the time there were some meetings, but he asked me to drop materials in advance, he said that he would be able to take a taxi to the airport on his iPad;
  4. The consultant has no idea what operating system and which applications are on this iPad, and whether the uncle has a tendency to call his tablet on an android as 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 macros on VBScript in Excel;
  7. And the consultant is sure that his uncle will want to play with the numbers in the model, and he needs to make sure that he doesn’t break anything during the game.

Question: what to send to the uncle? If it's just an Excel document, then the uncle will most likely not be able to read it on the iPad. And if he can, then there is a risk that he will misunderstand, he will not enter those 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 schedule a new meeting, look for free slots in the uncle's calendar, and the project will be delayed for another couple of weeks, or even a month.

In this case, it was decided to design a web page, which, step by step, demonstrated to the uncle the essence of the model being presented, similarly to PowerPoint slides. The main difference from the slides was interactivity: at some stages, the uncle could, via HTML forms or jQuery sliders, add their own parameter values ​​to the model. The new values ​​were translated into the Excel document cells associated with the form or slider, after which the model was automatically recalculated. The results of the recalculation were displayed almost instantly 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 SharePoint client, and the uncle sent a link to the page that he opened in his browser on his iPad (it really turned out to be an iPad), having previously connected to the corporate network via VPN.

In this example, the client had SharePoint, which made it easier in terms of ensuring data privacy. If SharePoint is not present, you will need to upload the document to OneDrive, give public read access and connect to the document via a token. Since access is public, anyone who knows the token can read the document. The most reliable way to ensure the confidentiality of data in this case will not upload them to OneDrive. In other words, an empty Excel template is saved on OneDrive, and the data is recited from its own server via secure channels at the moment of access, after the page in which this empty template is embedded is loaded into the browser. A less reliable, but easier to implement way is to communicate a link to a page to a limited circle of people and provide read access to the file for a predetermined short time (similar to how webinars are done). This option is suitable if the data do not want to disclose, 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 into a web page and use JavaScript to interoperate between HTML elements and cells in an Excel document. To understand the principles of creating Excel Mashup, first of all you usually 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 was launched by Microsoft itself. However, a suspiciously small number of likes and a controversial design raise doubts about the authorship of such a respected company, but that’s not that important.

Another thing is important: a lot of time has passed between the moment when I first read this site and the moment when I implemented my first mashup. This is partly due to the fact that the pieces of code published on the website www.excelmashup.com are not very informative: for example, the definition of the function is given, but it does not indicate where it is called, which scripts should be pre-connected, what should be HTML- markup A lot of things had to be guessed, and I think that many people who had less time or patience could not go all the way. Another problem was that the Microsoft script, which implements the Excel Web Access class, conflicts with jQuery, with the result that the plug-ins start to behave inadequately.

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




All three examples use a small jQuery library, specially created to simplify the integration of Excel and JavaScript. By its content, I am ready to comment separately as specific questions arrive. 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 at all, of course, browsers. We will not point the finger, but on recent versions of Chrome, Firefox, Safari and Opera, this trick worked, but on one very popular browser - no (the embedded Excel document did not open). However, the fact is that you can do your Excel Mashup without installing or renting a web server. This, in turn, opens up 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 who is allowed to use Excel has browsers and access to the Internet.

Well, who may need all this?


In my opinion, first of all by the very office ninja, consultants, analysts and managers. Having a direct relation to this guard, I made a decent number of models and forecasts in Excel for my life: different calculations of profitability, scoring, balancing and other things from the world of finance, reporting and risk management. Each time, oddly enough, the development of the model turned out to be the most difficult, and its presentation to the authorities and customers: various “experienced managers”, “strong business executives”, “iron lady” and “energetic young alpha leaders” who so often climb to the top of the corporate hierarchy. But in order to get the opportunity to report to the "celestials", you still have to go through a filter of slightly less successful gray-haired and not-so-good advisers, members of various committees and other "experts." Some are trying to get into the details, others say: "Explain to us as if you were explaining to a five-year-old child." Some people think with tables and figures, others have figurative thinking, and the third one should definitely touch them with their hands before drawing any conclusions. One thing unites them all: 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 before these comrades, I was not presenting a model, but rather telling a story. And often for the results of the project, how well the story turns out is much more important than how good the mathematical and logical component of the model is. In this case, mathematics and logic can not be neglected. Among your listeners there is always someone willing to dig deeper into the details or to 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 they cannot do by themselves: its capabilities as a tool for visual accompaniment and managing the course of the story that you are telling are too small. Usually for these purposes they use PowerPoint, but here is the trouble with interactivity. PowerPoint is great at telling your story as long as nobody asks questions. As soon as you need to change the model's assumptions on the fly, PowerPoint is no longer your assistant. You can certainly try to make the presentation interactive with macros on VBScript, but it is better not to try. It often happens that the same slides are displayed differently on different devices, and the more complex the presentation, the greater the likelihood that this will happen at the most inopportune moment. That is why in consulting companies it is customary to convert presentations into pdf before sending them to clients. And that is why in the “Uncle” story one of the limitations was non-use of VBScript.

With the increasing popularity of jQuery and the number of useful plugins, imposing a presentation in HTML-CSS-JavaScript has become a little more complicated than in PowerPoint. And for me personally, for example, it is even easier, since 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 gives 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 used effectively. Most of the Excel documents mentioned above are designed so that, based on an analysis of the facts and assumptions, managers can make predictions and, ultimately, make management decisions. 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 referred to as corporate or management reporting systems. In large organizations, such systems are often introduced as a replacement for hundreds of disparate Excel documents. The goal is usually to centralize and speed up the reporting process, as well as generally more efficient use of information as an asset.

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

The fact is that users and business analysts of this kind of systems are for the most part also advanced users of Excel. An Excel document is such a thing that can be done relatively quickly. If you are a business analyst who can do Excel Mashup, then this means that you can make a prototype of your report or dashboard in a relatively short time on your knee without the involvement of programmers. Such a prototype will explain to the development team better than any problem statement what the customer wants to receive. 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, mutual understanding between analysts and developers: what else does a project manager need to be happy?

In conclusion, I would like to note the following: in general, we have a thing with a certain potential that is easy to learn and use. 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.

Source: https://habr.com/ru/post/220839/

More articles:


All Articles