Excel SolutionPalooza! Get inspired by the latest greatest Excel extensibility - BRK3377
Hello, all right good afternoon everyone, welcome. To, the excel solution, Palooza, I'm. Care and. This is one and we. Love, excel. Our. Goal today is to get. You guys inspired, to. Try out deploy. And, build. Different, excel solutions, what, we're going to do is provide you a set of different demos, and consider. These building blocks. Will. Have these building blocks and, then. We're going to ask two, of our great partners to come on stage front, line systems in Han Hana to, demonstrate, how they're taking these building blocks and built. Plugging, them together to build, solutions. That unlock, business, value for their users. Oh yeah. Okay. So. Told. You I we, love Excel, who, needs PowerPoint. We're. Gonna talk, about office, add-ins, office. Add-ins. Enable. You to make, your solution, a native part of Excel and if, you're a business, decision maker you may, want. To ask yourself hey do I have users, that are, using. Excel, not only traditionally. As they've done on Windows desktop but. Now, are they accessing, Excel through the browser like in this instance or, through Mac or through different devices, likewise. You may have a different solution and, people. May need to contact, switch from, your application. Into Excel a lot, of times we see developers, provide the infamous. Export, to excel, button or the, export, to CSV button and then, people load that in into. The context of Excel but, the problem there is it's static, and so. This is where by, you bringing, your solution, into. Excel you, can actually make, it way. More compelling. And things. Like that all, right the, next part is if, you're an IT Pro who's. Ever had to deploy a calm add in but, or into. Their organization. Alright. A few of you guys who, like doing that. Alright. Nobody that's, that's, why I thought. With. Office add-ins you can easily, manage, and deploy. Your. A divs and control. Them so you, as a admin. Are in control of that, add-ins that people use in your organization. And. Then developers. So. Office. Add-ins, leverage, web technologies. That. Allow you to call. Out to any service. Powerful. Services, having your logic stored, in Azure or, even. Calling, out to office. 365, services, through the Microsoft graph so. There's a lot of, goodness. Here, I. Did. Want to ask. Folks. Who. Here identifies, themselves, as a business, decision maker I'm gonna try to get a count. All. Right so one two three or four five six, all. Right. Let's. Just fudge the numbers a little bit but alright, who's an IT bro it's about. Six all, right so let's say. I'll. Give it around 70 and how many developers do we have in the audience. All. Right I'm gonna go a, hundred. Of you. Okay. So, the, first. Demo that we wanted to provide you. Was. A. Data. Visualization. So. Here, I've gone into the insert tab in Excel online and I've clicked office but Adams button and. Pretty. Quickly we can see within, this dialog, a few. Different add-ins that I have here, in this case my. IT. Admin, has already. Deployed, to. Adams one. Is the people graph another, a script lab which we'll talk about in a second now, the way the IT admin deployed this, was. Through, the. Office, Microsoft, 365, admin Center and within, the admin Center there's a service and, services. An add-in and here. You can see all the different services your, users have access to, likewise. Here. You, can see the, admin, has already, chosen, to deploy the people graph into, their organization. And it can't and you have control so you you, can deploy, to specific, users or specific.
Groups Or everyone, or even. Yourself as an admin, to test things out before you open. It up to the world. Now. In in this instance, I'm. Downloading, a. Or. Inserting. A. Content. Visualization. Adding it to my document, so, I'm going to click, Add. Load, come on. Come, on Wi-Fi, there we go so. Here we have a visualization. That. Shows us, numbers. Within the app so let's change this a little bit and. We. Want to know. Not. Only. We. We are all Excel, lovers here. So. I can select, yep. And, then. Also, I can go and customize this, to my heart's content so. With the theme, yeah. I like the root theme the. Shape let's go with some hearts and there, you go. And one. Of the things about this as I mentioned is it's, not static so, if we were to say hey a, bunch, of BDMS. Walked into a room. And. The. Data. Would react, through a data changed event and update. Itself so, here, we have an example of an, add-in, that showcases two building, blocks these. Building blocks are a web view which, shows dynamic. HTML, content as well, as the it. Talks through our api's. To. Interact, with the. Data inside, the users document. So. I wanted to also talk, about a few other building, blocks that we have. One. Of them is hey. As. I mentioned I had, the IT admin had deployed to you add-ins to me one. Of these add-ins is called, script lab and so, I didn't have to go and acquire this I just opened up Excel and, here. In my ribbon there's a script, lab tab now. Likewise. We. Have front-line systems who. Also. Created, their own ribbon. Tab or. Hana. Actually. Has just a simple button that, they use to show their task pane. Alright. So. The next part is we, have web views so I showed a Content, add-in now let's see at a screen so. Back, into script web I. Can. Open up. The. Code test pane. And. This. Will open up and I. Have, pre-loaded some. Custom, functions here. Which. Are our next building block so. Custom functions are in Developer Preview I'll talk about those and then, I'll hand it off to one who will talk about our Excel APs. So. As. This loads, alright. So. Here we have a set of custom functions here there's, a contains function, there's a translate, function let's. Try. Them out. We. Already having some issues. So, well. When. We look at custom functions, custom. Functions are really just a part of an atom, they, should they look, and feel like native, built-in functions so it does, really feel like some and I've, actually taken that quote from one of our partners who said hey you should make it really feel like some. The. When. You look at functions, they're actually just formulas, that it actually that travel, with the document, the code itself does not travel with the document the code is hosted on, a on, your. Web server you're, actually just. Having. The function be a pointer to, your logic. And. When. I think of functions there's, three. Or four main scenarios, I think about first one is. Complementing. The rich library of Excel functions with your own if you want to have reusable, logic you can go and create your own so. It's. Ignite and it's. A long week and it's hard to attract what day of the week it is so. I'm going to just you. Try, a custom, function. With. Another add-in that I've already pre deployed, which. Is hey I mean. That day and so. This will actually make a call and. Return, the, day of the week which, thankfully, it is Wednesday so I know I'm in the right place hope, you are too. Likewise. One. Of the new capabilities with, custom functions is the built-in, ability to stream in real time data so, here we have the canal, a, set of stock quotes. We're, in Orlando so I chose Disney I hope that's the right stock ticker and. Here. We're gonna say hey let's stream in some. Of their stock wolf, functionality. And this. Is going to go get that data. And I'm just gonna go fill down and I'm, just using, Excel I'm. Just using familiar, Excel, operations, that I know and love and now. I've, gotten that data. This. Is hard to see but there's going to be a working. Thing. So this, tells me hey it's going out and continually. Pulling to see if there's a change, in the stock quote data. Today didn't seem that volatile. But. If there is oh there we go Microsoft, or Disney just lost a penny. Maybe. We should all go and help their. Stock. Price go up all. Right, the, next scenario is around, what, I think is, applies.
To A lot of us where. We have a particular, service and how do you make that available, to. Users in Excel and, in. This, scenario we're going, to call a. A, sure function, which, is calling. Microsoft's, translator, services, so this is a scenario of bringing, intelligence capabilities. Right into Excel. So. I can just call a translate. And. I. Want to translate hello world and es. Yes is a locale for Spanish. I'll. Do that and here, we go hola mundo. And. You. Could say if, you want to know how to say we love Excel, you. Can do that or, my. Favorite. Donde. Esta el Bano very, good. For. The last. Demo. There's. You can also import. Data into Excel. Using. Custom functions with, a new feature that was announced at ignite, dynamic. Erase, so. I'm going to switch over to excel, desktop, to highlight, that. All. Right, so. I'm going to open up script lab and. Load. Up the code. So. Here's, an instance of that same atom that was running on Excel. Online working, on Excel desktop. And. Within. Script lab I can it. The. Alpha script up know. We. Have a different. Environments. For script lab I switch, over to beta so. We are. Adding custom function support into. A, script lab that will be rolling out in the next couple of weeks but. For now we're in beta mode and the. Next thing I'm going to do is, go. To github and get. A gist, this. Is the same gist that I had. Which. Had like, the translate function as, well as the import, HTML function. So, let me take a quick. Minute and just give, you a. Brief. Look at the code so, here, was the Translate code all. We're doing is we're returning a promise and making. A simple, web request to. This URL and. This. This. Could be any service here, all, I did was follow. The cognitive, service with quick wrap. That that into an Asscher function deployed. It and with, a few lines of code I can bring that that richness, into Excel. Right. So. Let. Me open up this. To, import. And. I'll import this snippet here. And. Then, i'm gonna click the functions button. To. Run that. Alright. So now that these, functions, have registered, and i. Want to know hey, for my next trip down to Florida. Where. Should I visit base, off over the weather it's been really hot I like Florida. Is. There, a place that's, cooler. And so. What, I've done here is I've. Gone. And fetched. Out weather, information from this website it was a table brought. That in, real time into Excel and before. A user. Would have to know oh this function is returning five results so it I have to select five cells control, shift enter, that. And guess, what not many people knew, about that and so with dynamic, arrays and custom functions working together we've, unlocked a new scenario to import.
Data All. Right with, that I'm gonna pass it on to one who's going to talk about our awesome excel, APs, thank, you very much guy your yeah. This is awesome to see I mean III. Your. Custom functions was what, used to be UDF's. So and, the good news is that you. Can actually combine custom. Functions with your audience in order to because we have the necessary API, steuby subscribing, to you. Know range. Change events, as well as. So. Basically you can create add-ins and integrates with custom functions and use, both technologies, to create you know amazing solutions, like the ones we're going to see later today so. I'm. Gonna go back to the I do, love Excel, but I have a few few, slides for you just for and then I will do a bunch of demos okay, so. By. Now everybody that has do salinger quick show, hands who has you who has created an excel adding here. Many. People in the room okay, so this, is a technology that actually shipped back, in office 2013 and, by. Now it should be very. Clear and, I've seen it with customers, and partners I've working with that's. You, know the value that we give us a platform is enormous. The fact that you can actually create an add-in and run. It in multiple platforms, it's just by itself you know an amazing capability, that our customers, actually love. The. Other thing that it's actually, super. Interesting. For our partners, and customers is the way that we deploy audience as Kelly was saying you. Know the fact that just. By deploying, doing an X copy deployment, or our web apps you know the. All. Our customers, get to get get the updates so that's actually. That's amazing, if you think about what previously previous. Technologies, do with with office. But. We still saw some, you, know. Resistance. To to actually you. Know attract Excel developers, to create add-ins and one, of the things that's. Inside. Our our companies. We. Need more api's you, know clearly, Excel. Developers, today don't have the number of API. It. Didn't actually. To. Create solutions you, know how can we create great items it will obviate. So. What I want to tell you today I'm super happy to tell you that we took this feedback, very seriously. We. Focus, on creating a PS for Excel and as you can see this chart sorry. About the small, fonts, but, it is showing every release that we have shipped and how, many api's were in that release and as. You can see the last three which is basically the last year we've been working, extremely. Focused, we. Literally. Added, many. Hundreds, of api's for you so, now the API said it's reaching a point where were it's, really about, just. Enabling what you have and in fact if. We. It's. Also interesting how we choose to prioritize. With ap as which, ap as we look first first, we. Of course you know based, on telemetry that there. Are the. Most used, but. We want to be very careful about it we, actually what we have in mind is to actually, enable in, a set of categories, we. Think about end to end we know, that the most common ways of the more common common solutions. That are built on Excel we. Try to do a categorization of, them and we, find really interesting, information about, you know what, patterns. Of api's are used in each of those categories so we, were able I mentioned two men just to mention a few of them and I, will keep this in context, for all our conversation you. Know we know what API is you are using for example we want to do reporting, if you want to do, reporting. You have to do not only support. You know charts, and tables and people tables, but you also need to have all. The necessary API, Stu pink. Still many people put reports, you wanna see in my demo order, now, we need all those capabilities we need a bunch of charts enabled, vein analysis, audience we need a bunch of API.
To Make sure that we. Can insert fast they excel, have. The necessary events, to create awesome visualizations, and so on and so forth I wanted, to think keep in mind that this is the approach that we that we chose and you. Will see that you, will have API see I think, we have if you're adding is in these categories, and others I think, you should be able to actually. Create a new Allen today and. With. That I will close. This. Is what I want to share with you is the the history of hopefully, you can see the. Problem that I have with this is light now is that we have so many API so I'm having a hard time finding, space for it for all of the objects we have today in. Build back in May we actually released our 1.7, API and we ship a lot of good stuff like protection, for workbooks tiles. Freeze. Panes we ship a bunch of events as well charts. We with a bunch of investments. Today. Add add. Ignite conference we. Are announcing, the GA of the, 1.8, api's. So. Starting today you can actually start, using. This API s we. Actually, turn on the bit back, in June so, today 95%. Of office 365 users actually, can use this API so you can use them today and. They. Say this release includes, data. Validation, which was supposed to be really actually a recent 1.7, but, we it wasn't stable, enough so we actually wait. For it now, it's rock-solid it's in 1.8, you can use it today we. Also included people tables, we, have originally, shipped something on 1.3, you. Know that you, were only able to refresh people's but now you can actually create them and do more interesting, things. We. All have more. Charts we have more events for charts we. Have created workbook which is an API, that many customers are asking for creating, new documents out of templates. And. We. Also have. Feed. Headings, removed and removeduplicates charts. And so on and so forth, we. Also announcing, today. Another. Like 400 api's that. You can actually try today on our better, endpoints and. These. One includes shapes all the, charts in Excel so basically we are declaring charts, complete, all, the charts mostly, mostly most of a chart you can use we, have out to filter for ranges. Arguelles, for the first time in the API history, we are able to support, these.
Continuous, Ranges so, you will find the functions to do that we. Added special sales page layout copy page save and close filter, capabilities, and so on and so forth so. All of this is properly documented in our websites, the open specs are also up and running. And. Then, basically. I will just. Proceed to show you a few demos on this. Let's. Go back to 7:00. Right. We. In, there. You go I. Want. To show you a bunch of demos. So. The. First one that I want to to. Show you it's around. Some. Of the perfume business we're doing actually. Later today when you see what Daniel joins the stage he's. Going to show you a solution. That we are working, shows. Shape. Ok. Script. Lab. So. The team is not only working on enabling more api's we're also concerned about the performance, of those api's, and. I want to show you a little bit about that, so. The. My. Snippets and into github mmm. Sorry about this and let me sign up to get my scripts. There. You go. Oh. Ignite. Demo. Let's. Run the script. Demo. That you're gonna see later today it's about basically. A topic. That we all know, and. It's. About an, airline wants to know how, many, number of tickets they need to over sell in order to be more and. More. Maximize. Profits, as. We know we many, flights are oversold but how many how many should we oversell and. We were thinking about you know getting some data from an airline indicating. The number of no-shows for each flight ok, so you will see more details about that on the demo what. I was telling this guy say when, will you also use this to test up the performance of the API so. I create, a simulator, of a ticket you know that it's able to have many. Years of data into a workbook right, and many many people have complained about the performance, of the JavaScript API how. It compares against VBA so you're going to show you a quick a quick look at that so. I'm just going to pull right now three years of data, this. Is actually two hundred and fifty thousand cells. That. I inserted in a workbook in just one point two seconds and.
This Will be a little bit slow because I, don't know why but this one is a little bit less than this so. 250 thousand I. Think, it's very wishing all the time let's, add more years here if we had five years of data, are. We allowed forty four hundred twenty thousand, cells, in. 1.8. Seconds. And. Well if you're really extreme and say, thirty, years of data and see how that works and. Insert the data this 2.5. Million cells. That. We are inserting right now in Excel, and. It's, gonna be probably. Less than ten seconds. Nine. Point nine seconds. Thank. You. So. The. JavaScript API czar still not as fast as VBA but are very close, guys so don't don't worry about that okay. The. Next thing that I want to show you is the people to API so. Which we have here you know 30. Years of data so, how can we, summarize. It into a people or something so, I have here some something that you can actually see how we can actually create. A pivot and in this case, we. Have in 1.8. All. The API is that you need to create p-pods and in fact one, thing that i want to share with you guys is let. Me actually. On. The island again i want. To show you something really interesting. Happens. To be that. We. Raise all these data. We. Have a lot of press because we recently shipped a product, that is that is the ideas, maybe you guys care about it right we, just talked about it in the news this. Is a very interesting probably, because that it's actually an add-in and this Allen actually works on top of the JavaScript API so these. Guys one were one of our main customers and. It happens to be that ideas, can actually suggest. You, know what, types of charts I can use in a given data set, and. If you use it here you will see that I am I'm generating this data randomly but. I added some, outliers. In, this data, and. What. I want to see is probably the. Ideas if it's as awesome as they say we're gonna give me that those outliers and you can see here exactly. What I was talking about, you. Know in, this chart that they are suggesting me to insert. Actually. There's an outlier you can see here that the flight from Seattle to Orlando, on the September so, just before the conference was getting a lot of no-shows, so. People from Microsoft coming, here didn't show up to the airport now it's, just data. But you can see that in the incidence is very clear that this parent outliers, on the top of the other ones so. If ideas was able to build an add-in as powerful as it is today with our API so that, for. Sure can can solve your chart needs. Ok. So. Far so good. Let's. Go ahead and have. A look at. Events. So. We did a very significant, investment in events, in Excel. As. You will see here and I. Would, like to show you one. Quick demo. About. Events. They. Copied uh just. Here, so. Events. It's a. Many. Developers, are asking us to to, to implement them and. We have some sort of support earlier in the early api's. But. Hopefully. He's not gonna call me right now there. You go. So. We. Really did have events but really, there were not meant not not that useful but for example a change event we were unable to know exactly what cells were changed, and we didn't know what were the old values new values, and. Many many other information, now so today we the events API that we have we. Actually are. Able to. To. Get a number of events and as. You can see here, I, am, in in Excel online and. I'm gonna load that there motorist and by the way all these demos you're going to be able to use them. They're. Gonna we're gonna polish them on github okay. So as I run this example I. Want. To show you some of the event capabilities. This. Is very useful for for. You. Know for visualization if. You want to create a great visualization you need to know when, the data changes and. When you select different workbooks, and so on and so forth so this is just basically a simple map, as. You can see on the tasks pane and there's, an event tab here that I can turn on or off the events, and.
I, Can do many things I can register to the collection events so we added a bunch of events for collections, when you add a new chart when you have a new sheet maybe you have new stuff to the workbook those, events are going to get triggered. We. Also it register for events on. For. Worksheet events and I restore. For the Bing Map events and. So I'm just going to a bunch of events on this demo so. Let me go back to the map you, will. See that every, time I select something on. You, know on the on. The table it's gonna get refreshed and, one thing that is really interesting on, this demo that. I actually caught, the. The. Console here but I and hopefully, you will you'll be able to see that. All. The information that we are getting out of those events. See. The event still on, its. Subscribe to the events again sorry about that. Subscribe. To the Bing, Map events there, you go so the events are registered, and when, I do a selection. You're, clearly gonna be able to see in, what works it was event what cells were the ones that were selected and, so, on and so forth if I insert a chart here and I, try to do, and I switch from the table to the chart, sample. In the one. Chart like this you're. Gonna see that now I select the chart I'm gonna get the event what chart was selected, and so on all the context right this, was a number one request and we were able to serve. It. Okay. So. That's events. Let's. Go back to that my workbook, I want, to show you another, demo I. Want. To play the Excel right now so let me go ahead and install script lab. And. I want to show you another. Demo about charts, the new investments we have done in charge this is useful for solutions. That are you. Know you're doing business instead against you want to do charts of summarizing, data and stuff so, let me go ahead and pull up the demo here. There. You go by. The way there's a blog I need to do some pre work here. Okay. Let's. See what this this demo is it's all about well. I want to highlight on this demo is it's a lot of the chart API as we're doing. So. As you can see well let's add some sample data here, this is just data from from a store you know they have lemons. Oranges, and stuff like that and. I. Have, the sales data and other information, associated, with the sales for example the temperature, of the date when the sale was done and so on and so forth so. I can create a line chart but that's nothing new we, do two series. Of datasets. If, you want you can split them so I will have lemons. In one chard and oranges in one charge but as you can see you. Know the. Let. Me zoom. A little bit here you, know. The. Data. Is not the the, format is not in sync, right there, are different units and on both of them so we added api's to actually change the data, as. You can see when I click this button you can you can have a fair, comparison between lemons and oranges, I can. Also format the axis. Something. Like an atom before, I can, add secondary, axes, if I need to and I can format them, very. Powerful and. Then I cannot other types of charts I can do formatting, like this, I. Can. Also create scatter, charts. Okay. So there are a bunch of new chart types and you'll I will see I will Google see more in a few seconds but, then one of the things that that developers. In Excel also want to do is they. Want to print, a copy of these reports, they want to be able to create you.
Know Pages, of documents. In very, specific formats. So. In the EPI we also added, access. To the page layout object, and the page layout object, enables you to create margins, and then, able to set up break pages, and stuff like that and. In this case you, can also add headers and footers and, you can do everything, that you can do on the page layout you can do with with. The API and, the API as you can see it's just, the. Only way to visualize what's going on here after I click is if I do the print preview right, so. The Print 300. Seconds. Yeah. I. Really. Want to show you this demo because there's, something I have to disconnect, my printer, from from campus because. It was getting slow but. Okay. Scrap. Its. Set up a. Print. Area before doing this. Okay. Then. You got some sample data, they. Make sure that it's working prepare report and then. I can go and go. To print preview. For. Whatever reason is God. Demo gods are not helping, me today but it. Is working so this. Is basically showing you a report that has many pages and headers and you can do all those changes. Okay. So. I'm. Going to show you some other, demos, in the iPad, so. I'm gonna go ahead and switch. To this monitor, here. Great. What. I want to show you now it's the new charts, that are in better today. But. I want to show you them in the in an iPad I. Have. This device here I can actually run my ad, in. And. Again this is the we, will redo all this charts as a result of a request form and from the ideas team and. Now you're going to be able to again add some sample, data into the workbook, so. You can see that my my. Some, data here and you cannot now the the, new map charts. So. As you can see here. There. You go there's the map there are some but so this this data has geographic. Information about, the US so. It automatically, identifies, what geographic, data you are due you are you are using and it adjust. The chart as needed if this was series. In the world it will it will map to a work world. Chart and. Then one's going to have the Charter you cannot labels you can split the the data into different states with certain criteria, you. Can also add whispered. Charts you, know if you want to do stock stocks, charts, and stuff like that. You. Can customize if you want you can remove the axes. And stuff. Another type of chart is a waterfall chart, you. Can see here you can also have access to that songbirds charts, there. You go you can do a Pareto so you can't represent your 8020, s as well. You have three maps. And. You get the idea so on and so forth so, if, your solution is about charting, you. Definitely. Have. A solution, in. Our platform and it will be awesome to see your solutions. In our store and, finally. I want to give you them about shapes. What. We're doing in time between, perfect, so. One. Of the big API is that we are doing in the team today. Is that. It's. A shapes API and, in. Case you don't know about it. Shape. In. Excel. Everything, that floats in the workbook it's considered, you, know a shape, and that. Is true for this. Picture that, I took recently on a trip to China where, all the Excel API team is located and this, amazing work we. Went to see the pandas there so here's a picture of this this is a shape a textbook is a shape this triangle is a shape all the lines are shapes even, Agabus are shapes and. You. Will see a well how. Do people use this you know to create solutions well they use it in many many different ways well they use it for example to create reports like this they are charts, and this is nothing but you. Know, just. Shapes. And squares with numbers and different formats, they. Also do it. To. Create dashboards like this one you will see probably the demo later today this is this is a very, awesome. Trick, this is just an saved. It's. Grouped but. If you remove the group and you go to the details here you will see that you. Know the temperature, is really a rectangle. And depending, on the values this is just in size increase and stuff like that so, that's done with shapes. You. Also do it for usability so you can actually include like funnels like this close, to your data and then make the developer click on those shapes and do some action and.
We. Could do any of them of that you know what I was telling at let's, do something more fun it's. Let's. Try to see if we can do something really crazy here. And what I what. I asked our team to do I think it, was a challenge that I wasn't, sure if it was possible or not, but. These guys in, fact created. A, Tetris. For, Excel and, I want to show you really quickly the. Book. Here, and, again this one is also going to be. Public. So that you can actually download it and see all the logic here, but, basically it's, very simple it's just a tetris but, that which is required shapes to be to be created, so. There. You go so if I click on play I'm. Gonna see a very beautiful picture of Mount Rainier and there. You go there's a Tetris and I can actually, you. Know move move the shapes, again. Able. To move anything here. Let. Me try it in a different page, hey. There. You go I. Can. Split the only thing that is missing is a music no but I am able to hopefully. Create some. At. Some points here and so on and so forth well, it's, fully functional. English. Again is we've taken the focus here in order to work properly, well. Anyways, the summary, here is if you can create this type of solution. Imagine. That what you can do with your with your business applications, and. With, that I, would like to let. Me go back to the slides. Number. Five. So. This is really just a glimpse of what, you can do with the new 1.8. DPI's and the on ipi scene better and, now we're going to see how you can actually combine both, custom, functions and add-ins to. Actually create, really. Business business solutions and I will invite Daniel file straw to the stage to show us the. Solver. Adding thank, you very much. I. Think. That was really impressive what. These with these guys have been doing and. We, as IOC. Developers, have been watching, and, starting, to use this and, it. Makes a huge difference for us so. Which machine is active. On the. The. Slides right now. We. Go there's, a there's one two three four five scenes. Plus the iPad here okay. All. Right good. So. This. Is my one marketing slide so frontline systems is is. A vendor, of advanced. Analytics, software that includes both predictive, analytics, like data, mining machine learning and that sort of thing and, prescriptive. Analytics, which. We will cover will actually be showing the use of Monte Carlo simulation, risk analysis and mathematical. Optimization, and. The. Whole idea there is not, merely to make predictions, but to actually make, business. Decisions that, have a payoff that improve. On what would be just the prediction. We. Have. Used Excel, for many years we were the the. ISPs, who originally, developed solver, that's in Microsoft, Excel it's been there for a long. Long time and. And. What. We have done you. Know outside of the OEM context, for companies like Microsoft. And Oracle is we, have built a set of a very, rich set, of capabilities, excel. Based and using this this. Cloud-based offering, called raised on that. Enables. Companies to solve, pretty, significant, problems, solver. And Excel is kind of the baby version for. Us in, analytic. Solver which is our B commercial product which, is sort of a 1,000 to $5,000 per user, product. Is is something that you would use to solve like a, optimization. Problem with a million decision variables something like that so. We we've been around for a long time as you can tell because we built saw her like more, than 20 years ago and. So we've talked a lot of organizations. So most of these companies are large, companies, and so, we have faced these issues about that. One and here, we're talking about about. Installing. These things and dealing with IT and dealing with security policies, and so on in large companies. We, also began, and I'm going to go to the next slide here. Starting. In 2013 so it's been almost five years when, the very first versions, of the, JavaScript. API eyes became available we began using them and we. We, built what you could build at, that stage and so, for example, solver.
Which Is actually, built into desktop Excel, it's. Not actually, built into Excel online. But. It still is available for free in Microsoft. Apps sources, from us and. It actually you know that means that everybody, who uses it is using, our cloud, servers, but that's pretty economical, for us to do for everybody as long, as it runs on Azure so, that's that's solver, in the apt source this. Is something called risk solver it's a baby version of Monte Carlo simulation, and, this one how many people ever use the analysis, toolpak in desktop Excel, yeah. Some of these some of you so that's also not in Excel online but, with, this add-in it is so you know everything, that's in analysis. Toolpak is available. In Excel. Online now. Our commercial. Product the the is a comet, in and like I said it's sort of a 1,000 to 5,000 dollar product, that people. Work, with and this is sort of a little snapshot, of the data mining and machine learning part, and this is the. Conventional. Optimization, part there's a simulation, and risk analysis. This, is actually what we call stochastic, and, robust optimization. So, this is a big piece of software, and. Here are some statistics on it it's a couple million lines of code, mostly. Algorithmic. Code and then c-sharp GUI, code, there. Are a hundred and eighty custom, functions, and one of the things we have not been able to do until, now and, like literally. The, reason I'm sort of here today as much as anything is because custom. Functions in, JavaScript that, work. Across, device isn't so on or something you can now build. Another. Big issue here is you know when when this thing runs as, a conventional, comment and it's about a 200 megabyte setup. Program that you have to run and. You have to have administrative privileges to, install it, and all that sort of thing that. All, goes away with, the JavaScript, add-ins I think, one, and kir have been talking about this but, the. Only thing that shows up on a user's machine with. One of these JavaScript add-ins is a little, text file it's like hundred lines it's, called the manifest file and there's no other footprint, on machine and then. The add-in is actually running in a sort of JavaScript, sandbox, so from an IT perspective from, a you, know company's perspective it's really it's. Really a nice alternative it's, a much safer or some more secure alternative, so. Back to us you know we have this this very large piece. Of software with a lot of dependencies. In common, VST oh and so on but. Customers want to move to office 365 we actually have as you saw earlier there's. More than 500,000. People who. Have downloaded or installed the. Those free add-ins, from, the from. The EPS source so. How are we going to do this well. This, is really where the interplay between what the one and Keir was showing you and what we've been doing really. Comes to force so. The. The, computational. Part the part that part. Of the reason we were able to do this in this timeframe is we have in addition to having our Excel based products, and in an SDK based product we, have for over the last few years we built this thing called raise on which is essentially, an analytics. API, it's, a REST API that. Can do optimization. And simulation and, data mining and so on and, by the way you can use this in your own applications, as well there's. Even a free account here on you've, got a raise on con there's a front in 48 and, then. We've had some weeks we had a fair amount of experience with, the, free versions and so we've been, able to get to the point where we can kind of run these as your servers and you, know keep everybody. Keep. It responsible, for all the users and. Then these new Java JavaScript API s have been crucial for us so. If you remember that chart that one, was showing you about more and more API is well we we actually need like most of them like, all of them not, not everyone but a selection from every new least so, that's been kind of crucial for us to to. Be able to use it alright so our, goal now is to move this this two million line you know product, and and with. All these hundred, eighty custom functions and so on to, make it work in in, Excel, online so. Now I'm going to try to give you some demos, of that so, sort. Of a word of warning we absolutely are on the bleeding edge here and. There are things that have just begun to work so. Let's. See. We. Want to switch to six I think right. So. Now we're on my machine. And. We don't want NASA. Earth we don't want King right now I want. To bring you back to this because this is a this. Is actually from one's workbook, where he was doing the pivot table demo so.
The, Raw data which. Which. Which he started the source data for the pivot tables actually passenger, tickets and, there's. A little indicator on on each ticket whether, the passenger showed, up for the flight or not so. How many people actually flew, into Orlando, and, let's, see a real show hands yeah okay so, the few people who either can't. Raise their hand or or drove, or something like that almost everybody flew did. Anybody miss their flight. Everybody. Was really really, good this time and, did anybody bombs, this is gonna be interesting, one person, okay and, we probably have about a hundred. Hundred. People, here so that's right that that's. Suggest that these algorithms that we're talking about are working. So. We're. Going to look at this from the airline's, point of view now so. So. The pivot table which, was summarizing, the individual, passenger, ticket data right it's. Just doing a sum and, it's up, to no-shows so, each column, in this, pivot table is, one flight that, occurs, over and over again right every day that, that it flies so we're showing day, by day and, we have a certain number of no-shows on, each, day so, we're the airline and we have the status now what are we going to do about this the, problem, for an airline is that. What they sell is seats. Right on flights, and this. Seats are like perishable, inventory. Sort, of like the you know the apples that that go bad in one day who the, problem for an airline is as soon as a flight, takes off any. Empty seats are lost you know for revenue, just that's it, so, and then the the further problem is even if the airline sells every seat to, a passenger, people. Sort of expect if they miss their flight to not, have to buy a new one and so on. So there's. Kind of an asymmetric, bargain between you and the airline they promise, they guarantee, you a confirmed, reservation and you sort. Of but not really guarantee, to show up. And. So actually that what the what the when, the airlines consider over booking you could think of this as sort of adjusting. That bargain to say alright. We the airline are going to confirm. You a seat, with a high probability that. You. Might be bumped and. That is in fact the reality and you, on the other hand are going to promise, to show up with. A pretty high probability, okay. And so then, the idea is, can. You how do you actually implement that. Okay. So, what. We did here is we just picked this we picked a flight Seattle, do Orlando. And copied. The data out of pivot table into this workbook over here and in this workbook I have this I'm on the tab of this, analytic solver adding so this is a case, where in JavaScript, were able to create, a ribbon, like this with a bunch of choices. On it and there are you know dropdowns from these, these things and so on there are dialogues, that pop up so.
The, First thing I want to do is. Do. I want to analyze this this, historical, data of the number of no-shows and, I could actually build a predictive model with the other tools that we have in the data mining portion, of the product but but. Because, we, have a we're, going to move into a Monte, Carlo simulation here, I'm going to use a tool that we call distribution, fitting so. I'm just saying control shift down and picking the, data which was in that column, from the pivot table and I'm, going to use a tool, up. Here and if all goes well it's. Going to show up and all this depends on the Internet action and everything else. You. Know we've been practicing, this demo for. For. Hours and hours and and, the, and behavior very much depends, on, on. How, good our internet connectivity and in a place in a facility like this it's really pretty, iffy. Let's. See. Well. I'm not getting anything right away. To. The whole page yeah because this machine has been asleep for a while. Okay. We're gonna try to go to this you know we're on this tab, we've. Got this data selected. So. Let's try this one more time. Low. And behold that comes up so, we'll fit to a continuous, distribution in this case and, what. It's going to do is look at different probability. Distributions, it's, trying to fit them to this data show. Us a rank list evaluating. Them by these statistics, AIC, and B I see, so. That's actor so now what's happening that's it sort of a JavaScript, you, know a little dialogue that we're popping up but then it's going to the. Raise on server in the backend so what's going out over the web to run. This and hopefully, it comes back. Reasonably. Quickly. And. Maybe, it doesn't. Alright, so, I'm. Gonna try. Something else here to see if this is live, yes, okay. Show. This one more time. It. Is kind of amazing how you do this the same thing more than once and it on the third try it works okay, so what. Happened here is we took. The data sent it off to this back-end, analytics. API server and it, it fitted certain probability, distributions. To the. Data and. Normal. Came out on top but I don't actually want to use that because normal. Can go negative and I, could never have a negative number of no-shows it's. Going to deliberately choose this Weibull distribution and, it's showing me what the shape of that distribution would be and then these other tabs show. Me how well, the data fits. So. I can choose this distribution and, then, over, here I've. Fitted the parameters, and in fact if I close. This it'll ask me if I want to save this and put. It somewhere and we'll be creating, a JavaScript. Add-in function as we do this I think, I'll just put this right now and like p1. So. I actually put it on top of my data but there's there's, the function now. Now. We have to think a little bit because all we've done is is fitted distribution to the historical, data and now, I can actually use this to simulate. Future. Flights and what, I simulate, will reflect, what historically, happened, that's. Not actually what I want to do though because I. Want. To change the policy I want. To change the number, of tickets I sell that is how much I oversell the flight right. So, I don't actually want to use this just. As it is in, fact, I'm. Gonna. Wipe. That out so it doesn't participate. In the model anymore. What. I've done right here if you can see this and unfortunately, I can't expand, it but here's what I'm doing I'm taking this, Weibull distribution which, is by the way a classical. Distribution that's used to analyze failures, like component, failures or machine, failures in this case it's kind, of analyzing the fact that you, failed to show up for. The flight, so. So. What I'm doing is the numbers. That it's fitted we're about 2.9, something, you know a lot of digits and then and, then the second parameter is seventeen point nine something, more digits I rounded, them off because it won't matter that much but, then I am scaling.
Those. Two parameters of the distribution by, the number of tickets I sell divided. By the number seats on the flight, okay. Now why do I do that well, if, you think about it if you do change your policy and you oversell the flight by ten, percent what. Do you think will happen to no shows, now. They should go up and they should go up proportionally, right, so, that's why we do this. All. Right so now the. Rest of this model it's a very simple, Excel, model and part, of the message, here is that. In. Like data, fitting or or machine, learning to historical, data is not enough if you, want to take prescriptive. Actions if you want to do prescriptive, analytics where. You are going to do something in a new that has not been done before your, historical data and you, know just prediction is not enough, by itself so we're gonna simulate what, we could do if we order solve a flight now, at the moment we're just. We. Have, 300 seats and we're gonna sell 300 tickets and that's it nobody no over selling what. We're gonna do now is run a Monte Carlo simulation which, means that we're gonna we're going to simulate this flight taking off a thousand, times and. Each. Time we will randomly, sample, from the distribution, so we'll get some number of no-shows then, we collect statistics, and make, charts and all that so. To do that all we. Need does the model is pretty simple and what the model calculates. With a little Excel formulas, is, like this is that. Our net revenue, after. Accounting, for no-shows. Where we give money back and in effect to, the to the people who didn't show up and, overbooking. Compensation, if we do oversell the flight and we have to no pay people. That we bumped in some, fashion that's also taken to account with just a few formulas. All. Those well we'll just run, this again but going back to the to, the backend REST. API server, and. Sure. Enough it comes back for us and, this time it's showing, us a, distribution. Of the outcomes, we could, expect that we've simulated over. A thousand, case, of this flight taking off as you, can see our net revenue, it, its average is 58 thousand four hundred but. It can get as low as you know all fifty six thousand maybe five hundred and you can very, occasionally, touch sixty. What what do what it mean if it's if it hit sixty thousand if. You think about it you probably couldn't see this because it's in the back of the screen now but my ticket price is $200, I've got 300 seats sixty, thousand is the best I can do and now, if you think for just a minute you realize if you are the airline and you only sell 300 tickets you, can't win I, mean. Like occasionally. You'll fill your whole airplane but most, of the time there will be no shows and your revenue will be less within sixty thousand effective sort of always will you less that's. Not so hot, here's, actually our sensitivity. Of revenue. To the number of no-shows which is a negative, number of course and this, also shows our revenue declining, with. The with. The number of no-shows we get. So. One thing we can do is just play what if right so let's let's. Sell. One hundred three hundred and ten tickets, those, were the the, adding. Functions. Updating. For. A moment there don't, be too surprised if we say see getting data and it stays there for a little bit but. I'll run the simulation, again. And. Comes back pretty fast and notice that our mean, revenue has gone up to sixty thousand two hundred fifty nine the. Whole distribution is shifted a little bit and, so that kind, of looks pretty good and so. Let's get. Aggressive. It's. A. Cell, for our tickets you know maybe we'll have a hundred no-shows and. Is that is that a good idea is basically, the question here. So. It, comes back again and, well. Would be Hallward our revenue went down fifty. Eight thousand, two hundred and, forty. Four is the mean, and. There is a little clue about what's happening and one thing is that this is now shifted over to blue instead of red that is but, over here is really the information. We just we are over selling by so much that that, as. We, get more Morneau, shows our revenue goes up but, we never we, never hit our over selling. Point. So, we actually are, worse, off, okay. So we've played around with that now. What we'd really kind of like though, is to. So. A little bug probably in our software is this flashing, part over here please just sort of bear. With us on that but, we'd really like the you know the software to find out the optimal, number of tickets.
To Sell that would you know best balance. The. You know the the. Effect of no-shows versus the cost of over, booking right well. We have a model, that already, computes. What. Happens, over. A thousand, trials this. Sell down here, is the. If you can see it says side I mean it's its commits, returning, into the spreadsheet the mean value, of revenue. Over all those trials, and, over. And here. We actually have the, our decision that we're making the number of tickets to sell so. Remember, we we built solver you know so we should help to do this so. I'm, sitting on this cell and I want to say that is the decision, variable I, want. The solver to figure out what that number should be and. Then this cell down here, is what. I want as my objective, and I, want to maximize, it and. Then, there's one little thing I need to do which is to tell it over here that I want. To do the simulation, optimization, so. What we're actually asking it to do is go. Off to the server, run. An optimization, where it's adjusting, the number of tickets through so you know try tried lots of different value switches to sell each time run, a simulation, of a thousand trials, and compute. The results, and and help. Us find the optimal. Result, if. All goes well. No. Wait that's not what I wanted or expected. I'm. Not sure it sees. The. Optimization. Elements. And. That's. It. Might be us we might be waiting for, Excel online to save. Try. Running it again. Okay. It's, it actually has come back and told. Us that three hundred twenty three tickets is the optimal, number of tickets to sell and for, once we have getting data in the in, the cells but but. Hopefully this shows you what I wanted, to show you. Okay. So I think that's it and I think we want to switch, off here to, to. The other live demo thank you very much. Can. We move to the presentation. Hello. Can you hear me hi, everyone i'm matteo from hannah uk-based, start-up, so. Let let, me give you a little bit of background hannah, is an IOT company. Which. Aim to provide, solutions. Communication. Data, in, across. The globe for, IOT companies. And. We. Are here to present Simba. And he's a Dean for Microsoft. Excel. So. Let's. Start by saying I'm, impersonating. A. Supply. Chain manager, from an SME, and, I. Came, to to Microsoft, ignite and, I've, discovered some visa so. I went off on my own this. Hannah. Consolation. Visa website, and I purchased. Few. Of these with. An investments, of 300. Bucks and, actually if you if you go they're. Really, on sales, and you can actually use an ignite 18, promo code to get an 8 a 10 percent discount on, those boards I.
Got. These devices, and and I'm going to show you what I did after well, the unboxing, procedure, was very simple, because I simply took the device and I, went on the portal, I registered. Myself on. The Hannah's flash simply, support tool and I blinked, these devices, to my account, then. What I did is that I act entered, in the, Excel. And I, downloaded. Sbisa. Add-in which. Appears. Now on the right. Corner. So. When I open the sim visa at Dien now I have visibility, across all, my devices and, they. Appear with, a serial, number and an alias and I can actually customize, the alias. So. What. Happens next well. Let's. Go. And check what. The symbols are adding offers, what. We offer is that we developed, a bunch of custom functions, and we're going to start by. Maybe querying from. The physical. Device. The. Actual battery, level so. I'm going to go I'm going to get the battery, and I'm going to pass the serial number of the unit. And. If. Seems. Like it's. Not. Yeah. Out, closing, and open again thank. You. That's. The beauty of live demos. Here. We go back. Oh. Here. We go so suddenly. A cell, in Excel became. Actually a window to, the real world this, is a live. Data. Feeding, from a physical device within, your Excel spreadsheet. It's. Quite powerful but this is a static, data I just query what was the last. Battery. Available. The battery value available. To. Be sincere I want to be able to constantly, monitor I want to be able to have a live. Stream of what is going on and that's why we also developed, a bunch of custom. Streaming functions, those. Streaming functions, for example to check what, was the last time the, device updated. Some, data, and. I'll do the same i'll query the function and pass. So. Here we are both but it by the way it is a GMT, time so. There's. Some hours of delay between. Here and and where, it the, server's is hosted, so. Just. To give you an appreciation, this. Is the number of currently, available custom. Functions, in the Excel syn. Visa atom they, range from. Environmental. Data so, temperature, humidity pressure. Life. Feeding from the device, kinematic. Data so, we we have something. Like the teal detection, we see we can see, the orientation of the device we, can see if. Device dropped, and, and. We also have some historical streaming, functions, that allow you to have actually. Multiple, data set across the time so the the latest X number, of payloads, if you want to see trends if you want to see. Analyze. Trends and visualize those, so. Ok, now now a. Can I have understood, a little bit what these custom functions do when yes. I I, can see I can feed. Live data inside, an, Excel spreadsheet but. Let, me take a step back and tell. You about something, what. Can I really want to do with these products, is to, solve a problem, is well, known. Circular. Dependency, problem, so every company outside there, has. Three. Person. A person. With a problem a person. With, the money and the person that actually has the technical skills to solve the problem and the, person and the. Person with with, the problem needs, to convince the person with the money and the person, with the technical skills to be able to provide a solution with. The amount of money available and. That's. Spittin, seer this always, ends, up being a very. Very long process and, sometimes. He. Actually, doesn't. Let companies innovate, and, bring solutions. To their business. When. We think about I when we think about Excel. We, think about +. 1 billion installations, and let's face it every, single business man outside, knows. How to make his way into a spreadsheet and, on. The other side you know we, have a product, there that is, affordable. A product, that is as, simple as you, can buy it you actually get free credits. Inside the device and the, only payment that you have to incur is the, single, transaction, of the single data transfer. From the device into, your Excel, spreadsheet. So. Now. We've. Seen a little bit where we are and, we. Don't introduce what is the, first, let's. Say sim bisa dashboard. The. IOT, version. Of Excel. We. Can see there are a bunch of different columns. Ranging. With different type of data from, temperature, humidity, location. But. What's really interesting here, is that thanks, to Microsoft. Office, 365, excel. Shapes, we. Were able to build custom. Shapes custom. Shapes a give a look and feel to this dashboard that looks like almost a, web-based, dashboard, but. It's everything here in Excel so now I'm gonna take. This device it. Was under, the, light so you. Could see that the light level expressed, in lakhs is quite AI 300. And the orientation of the device was, sitting. Flat and, I'm holding in my hand we. Have to give it some little, bit of time unfortunately, with the 30 thousand people in. This building. GSM. Sometimes Oh take. His time and. Yeah. I, was. Gonna update the game is now updated, the vertical one I was gonna pay yeah here we go and the. Light came. Back to 45, lots. But.
When We think about this kind of solution we, also wonder, well, we're, where are the workflows, how, can I actually make. The. Best usage, of it well, when we say that this add-in. Connects, Excel, to the real world we. Really mean it in fact. What, we can also do is that. We can send a message from. The cell in. Excel down, to the device and. You will appear on the screen so. Now I'm gonna try to fire the. Camera. Should be on to, correct. How. They move it on one. There. We go. And. I'm gonna send. Something. Like, excel. Rocks. It's, taking some time. Here. We go. Up. The. Camera, is not a passive. Device. Just. Updated here but the camera is not showing the updates, if. Someone. Wants to walk here and see the device I don't know why the the. Camera looks, like he's actually frozen. Which. One. If. Okay. Okay. Well, I'll, see if you like the device around you can actually see the message. So. I, think. If we have last. Couple of minutes to close, these we. Wanted to prove. Another, solution. And under sort of. Use. Case for these so, I come, back to the fedot I was this supply. Chain manager, and I, purchased a bunch of devices so. What I did, is. That. Number. Six I. Purchased. A bunch of devices and I actually deployed, them across, the world and. Should. Have been open both at the same time yes. Was not. A wise idea. We. Go and now what I have is another, kind of dashboard where thanks. To. Conditional. Formatting and. The. Ability of adding a big, map inside. -. Brr itself I can, actually see, and monitor my, assets, across the globe I have one device sitting in London one device sitting in Brazil. One, in Phoenix and one here in Orlando, the one on stage and. The. Power of these fusion. Between. What. Microsoft, Office. 365 offers. With, Excel and custom, function and streaming functions, and the ardennes combined. With the solution, Hanna wants to bring is what. The real innovation is, I hope. You enjoyed the demo and. I hope you enjoyed this presentation. Please, come, and visit our website. Www.rinaldin.com. Thank. You very much myself, that's. Great great demos guys number. Five. Sorry. We're I think we're kind, of at, the end of the presentation, I want, to thank you very much Daniel and Hunca, for for the demos it was awesome. Last. Message for all of you guys please, connect with us your, opinion, and your feedback absolutely. Matters on the team if. You have any question, you have any problems, with the API feel, free to go to stack overflow we. Have an office - just tag there and the team is extremely serious about responding there in less. Than 24 hours we also have a github repository where, you can actually go and, actually. Raise. Issues this is not for specific questions but issues you find with the API so with custom functions and in, user voice if you feel that there is a feature that is missing, or if, something. That we should do better or any recommendations. You have you can actually up both requests, or create new ones. Also. Here are some useful resources if. You want to know more, about custom, functions let's go just go to this website a kms. Custom functions there's. Also an open, spec so we are one of the few teams at Microsoft, that actually has the specs online so. You can actually go ahead to this website, and actually see the api's, that are coming the, API is that our own preview the ones that you can actually try using our beta endpoints, and of, course please. Try the platform, and happy. Coding guys thank you very much for your for. Your presence, here and we're going to be here round for questions if. You want to do, thank. You very much.
2018-10-15 11:39
