Accessing data from anywhere with Excel custom functions and JavaScript API - BRK3080

Accessing data from anywhere with Excel custom functions and JavaScript API - BRK3080

Show Video

All. Right. Good. Afternoon everyone, my. Name. Is ko Patel and I'm a p.m. on the office platform, team, today. We're, going to talk about your. Application, your, service, and your data we're. Going to talk about how we can take that and, bring that to where users are and. If. You're, wondering where users are this, t-shirt should tell you. But. It's. Excel and I love Excel I actually. This, morning. Came across a interesting, story so. There. Is in Cincinnati, there's this marathon it's called a flying pigs marathon, and, there. Was a runner there who actually, found. Instances, of cheating using. Excel and so. He found people that weren't going, to the. Different stops as they, would and this particular, person who cheated ran. 12-minute miles but, on the marathon that he. Finished a, fast, Iran actually ran seven-minute, miles so, all that analysis, was done inside of Excel so. Take, a moment just think through, like right this instant, just across the world just. The number of spreadsheets, that are open right now and. What people are doing. They're. Getting that data they're organizing that data in rows and columns they're. Using. Formulas, sorting, filtering and then, once they have insights, into what that data is they're. Taking that and creating charts to visualize that and then, now they're sharing it with others, and, more. And more people, are not only consuming, excel data on. Windows. They're. Consuming, excel data across. A variety of devices on Macs and through. The browser as, well as mobile devices. This. Is where office, add-ins come, in, office. Add-ins like you bring your solution, to, excel to, provide. A rich. Connected, experience, that's intelligent and meets, users, where they are. So. How. Do that work so, it it, starts with. Your. Web app. We. Then have a manifest, file this manifest, file essentially. It contains, a reference to your. Web app as well. As additional. Capabilities. And settings. So. By creating this manifest, file you've. Now, created an atom and this, atom can, be deployed. By. It2, users, inside, of office regardless. Of whatever, context. They're, in. Likewise. End users inside of office can discover your solution. Through. The office Store. Now. Since. It is a web app. It. Can access all the rich web. Api's, it, can access rich Asscher services, we. Love as your functions, use. Cloud storage table. Storage, any storage you name it. It. Can also. Harness. The full power of the Microsoft, graph and. We. Have a JavaScript library called. Office, j/s lets lets, you interact with the. Data within, the context, of these applications. Alright, so with that we're gonna dive, right, in with some, demos. To. Get. The right window. All. Right. So. The first demo that we're gonna do is we're, gonna highlight. A. Added. Call. The people graph and so. The way you get the people graph as you go to the insert, get. Add-ins. Menu. And. This. Will open up office. Add-ins dialog where you can see. Different, add-ins that are available in the office store, you. Can see add-ins. That are centrally. Deployed so. Here I have a script lab out in that we'll talk about in a minute and then. We also have add ins that I've acquired, so. Here's the people graph added and the people graph add-in will. Show you. Some. Visualization, let, me. So. I'm, curious, of. Yesterday. With, a show of hands how many people attended. The. Azure keynote. What, Scott got three. Higher. About okay. I think, that's about 20 and I'm a good good counter. So. Let, me put. Scott and then, we'll. Put Rajesh his keynote after so. We had about 20, and then, how many people attended Rajesh is yeah. All, right that, looks like a hundred. We. Are talking about a. Excel. Over here and this. Was, Microsoft. 365, so. I'm. Gonna select that data and let me make.

Let. Me select the added, and within, this add and I can. I'll, see this, icon. Here and, it's really hard to see let. Me zoom in a little bit. So, I get the select data icon let, me click that and now I can select my data and, here. I'm just going to go select. Select. That and now through, office. JavaScript. API I can go go, and visualize, that data within, the context of my add-in. And since. We are using, html5. CSS. I. Can. Go and change the formatting as I would like. Change. The color theme and, make it all dolphins, for all I care. And, then, if I want, to change this and, this. Data is now tied. Or, the visualization. Is tied to my data so, if I were to go and change this to, a. Bunch of people from. Scotts. Keynote walked in the, data. Change my, add-in, has a chance, to respond to events and. Update. Itself. Likewise. If, I. Move. This data around. The. Day of that and can refresh as well so it really is this connected experience. That we're highlighting. Okay. So. The next. Demo. I want to share is let's. Talk about the script lab Bagon how. Many folks are familiar with script lab. Okay. Just a handful, so, script, lab is a, awesome. Add-in, and if you're building, web add-ins it will be your best friend it. Helps you create, and, run, different. Office. JavaScript snippets so, if you're familiar with Microsoft graph and the graph Explorer this, is our, version. Of that so. As. I said my, admin has already, deployed, the, script lab added to me so all I had to do was just open up Excel. So, here, I have Excel, and. By. The way Excel has a dark mode as well if you're interested. So. I'm going to open up my code editor here, and. What. I see now is let. Me resize. This and. Here I see a few, different examples so. If I click, the hamburger menu I can click some samples and. Scrolling. Through this list there's. A, ton, of different code, snippets, and examples that you can run to see, how you can interact with office, data. So. Let's start with. The basics I'm going to just do. A basic API, call, and. Let's. Talk about how the API works so. What, we're gonna do here is just highlight a certain cell yellow so, here's a basic, snippet, where we're, asking. To. Get the workbook and then, get the selected range of that workbook, range. Really, means what cells the user has selected, and. Then we're just saying hey I really want to format this what's color, yellow. Likewise. We're gonna load the whatever. Address the user selected, and. These. Calls if, you've built, on office. Or Excel before they should appear, familiar. One. Difference is the office, JavaScript, API is asynchronous, so what we do is we batch those calls up at any, time you run this context. Dot sync, method. That's when we'll go and execute that batch so. If we go and run this I. Can, choose to run side-by-side or run in this pane I'll. Just choose. To do that and, now. I can highlight, the selected, range here and so. It, highlighted. The cells yellow and then it. Showed. Me in con console, dot log what. The cells address what were. Okay. Let's. Take a look at one more example, and. In. This example let's. Go to samples, I'm. Going to try, for something a, bit more complex this is our report generation. Sample. And what. We're doing here is we're creating a report. With. Some sales, data that, we have here. Going. To do apply some formatting and. One. Of the things to. Highlight here. Which. Is I may not know the size of my data. I'm actually pulling that from a service right so I'm pulling, in some JSON, blob I need to actually and, then I need to import that to excel and so, in, this case what you there's, a method where I can, say hey get me. Get. Me the particular cell that I wanted up. Here so I started, off with the title cell which is at the top left cell, I'm. Gonna get the range and I want to get the resize ranged, based, on my, array, of header names that I had so, this could be a dynamic, array. That. I'm getting and then, I'm setting that values, back.

So, This does a bit more with formatting another, quick. Example to highlight here is this, example will also insert. Some charts and so, the. Charts API is very similar to the, range API you're, saying hey sheet charts, add. You're. Applying some properties like a chart title a chart legend. Data. Labels, and. So. Forth so, if I go and run this. And. Click. Create report, and. This. Will insert that data, into, excel, and, they this, is using. The. Same. Same. Built in chart functionality, so if I were to change one, of these values real, quick and make it really large. Excels. Built-in chart, will would automatically, update right, so it's not just like a you're, taking whatever the context, is of your application, and you're bringing that to excel. So. Excel is this rich. Flexible. Powerful tool and so we do need a lot of api's to interact with that, today. We have roughly. 1400. Api's available. And. Proud. To announce that. With. Our 1.9. Release, we're gonna have over. 18. Hundred different api's, available. And, the. 1.9, is GA today so. Thank, you for, folks, who have contributed for, that. And. By the way we, have Raymond, and Tuan from our Excel. Api's team from Beijing so, if you have api's questions. Come. Talk to them afterwards. So. A ton. More capability, in this release new. Shapes, access. To all the rich chart, types that are in office 365, greater. Cal control. Ability. To insert, save close workbooks, so, a bunch more and then, we, also have a beta channel so beta. If you're on the insiders. Preview. Channels. You, can to get access to the new betas, new, API s that are coming soon specifically. Comments, slicers, and we have a bunch more and. We'll have pointers to our deaf. Program and community call where you can stay engage with us to provide us that feedback as we move forward. So. With, this we have, api's. For, every step in. The. Excels user. Journey right, the the platform. It has a. Rich, set, of api's that you can use in your applications, today so. With, that I'd like to invite Gilbert, to talk about how they're working with our APs. For. Having me on the stage. Let. Me just prepare, that. Ok. So. Hello, I'm. Gilbert Kaba I'm. Working for sa p. SI. P as, a market leader and enterprise, application, software. While. Most people might. Know, us because. We serve. 92%. Of the Forbes Global. 2000. Companies. We. Also have eighty percent of our customers being. Small and mid-sized customers. In. Fact altogether, we'll more than four hundred and twenty-five thousand customers, so.

With. That many customers it's, easy to see that, more than three-quarters, of the. World's, financial. Revenue, flow. Through SFP systems. We. Cover all aspects, of enterprise, application, software, from. The back office to the boardroom, and also from the storehouse. From. The warehouse, to the storefront. I'm. Personally working, in the field of analytics, and here. We, have a product that's called ASAP. Analytics, cloud and, it's a software, as a service solution that. Enables business, users to discover. Analyze. Plan. Predict, and collaborate. All in, one place. And. Today I would, like to give you a lab, preview, of a new product that, brings. Analyst, AP analytics, cloud into. Excel. Online. So. For, the demo I will, be a controller. That, would like to analyze financial. Data. For. There. I'm using the web client of Sapa analytics cloud. Please. Observe the, URL, up there as. ASAP. Cloud platform, separates, and. Protects. Customer. Data by providing, customer. Specific tenants. This, URL here is specific, to my customer, it's, planning, reflecting. My company planning incorporated, EU, turn for the data center and Danna sa periodic structure. Please, keep that in mind it. Would be important, a few, minutes later. So. Here, I am and I'm, opening, a story that I've got prepared. That. Shows my operating, income data and a spreadsheet application. This. Has a very nice UI, for. Example, I can single click on a cell to. Drill down, on, my. Account structure which, is hierarchical. And. Seeing. Like my gross revenue, I see, that some. Of my goods are. Returned, and I. Would like to calculate the, percentage, of the of the goods returned and. As. A, controller, I'm. An Excel guy so actually. I'm used to Excel workflows, so what I would expect is, to, insert. A row here and then, calculate, the, percentage. With a formula. Unfortunately. It, just worked, a little bit differently than I then, I'm used to it as an Excel user so. I'm. Know. How to do this here and it would be easy but as, a controller and feel more familiar. With Excel so. What. I could do now is I, could. Export. This data as. Comma separated, values, but. Doing so I would. Lose. All the metadata information and. In. Addition, it will only export a snapshot of the data so, whenever. The data changes I would need to do, that over and over again so. Instead. I will. Do the same in, Excel online and, as, my IT department, does, not want to be bothered with installations, I will, do that in the online version of Excel so, let me switch to that. Currently. I have to do this manually but we're working on a seamless. Transition, from. The one to the other so. Here. I already have my my, web at NSA Pianist cloud installed, it's. It's a pre-configured. By mighty department as kay I just showed.

So. When. I click on login a. Task. Pane shows up and it. Presents a, landing. Page and, in. This landing page I need, to. Provide my talent specific, URL. So. Let's. Get. That, from. Here. I, need. To do that. Because. As, care showed the Adhan manifest, specifies. The location of. The web application. Running in the task pane, as. The, add-in, is. Available, in the store. This. URL needs to be the same for our customers, but. In, our, case the, web application, is, 10 on specific, so. Therefore, I need to enter this here. And. It. Will redirect me to this, to. This tenant. Specific web application, I. Only. Have to enter this information once. Because. It will be cached and in fact we're working on our together with the Microsoft, graph team, to. Pre-configure. This. Information, for every, user. So. Let me click on go. And why. The landing, page was, publicly, available, the. Actual. Turn on the application, is protected. By. Our authentication. Step. Prepare. And. Now I'm seeing the welcome page of the. Actual web application, here so. Now let's bring in the data. Who. Are you so. Press on select source. Now. Browse for. The operating, income. Data. Model. And. I enter it and it's here. Let. Me just. Add, the. Version, to the columns. And. Now you see the the. Same, data that we have seen in the web application it's. Nicely formatted as. A crosstab, here in the spreadsheet. We, need to to, expand, the account structure initially. Because. With, the current version of the office, Jas API. It's. Not possible to trigger an action on a single click but, we ask the requirement, to you. To Microsoft, and. They. They. Already solve it and it will be in one of the next version of the. API so. What, I can do now is I. Can. Add. Some more characteristics. To analyze my data. For. Example I can add regions, here. At. The product group and. With. Each change of the layout the data is retrieved live, from the back end. So. Getting back to my. To. My initial. Task. I. Would. Like to know the percentage, of the goods returned. Now. Let's, just. Enter that new line. Okay, and then just calculate, the, value using. A formula. Let's. Do this also for the land. Values. For. Merit with axial means. And. Here it is. So. With. This new add-in we. Bring the. Functionality. As, AP analytics, cloud to. Excel online and I. Would like to end with. Showing. You. Some. Code examples, how. Easy it is to, use the office. Jsapi, the, first one the upper one does. Show, you how easy it is to merge ranges. We. First create, range by. Giving some parameters, like start roll start column row count current count, and. After we have created the range object, we simply call range to merge and, the second one is. A little bit more complex but, not. That. It's. About, the styling of the cells usually. You. Would need to style, each cell individually but, if you have a lot of data that, could. Be very. Costly. For performance, so what we do here is we loop over the over, the columns and the rows check. The style of the cell and. Collect. All the cells with the same style and only if the style changes we. Create range. Of. The, cells and, assign. The. Style to. The to the whole range object so clockwise, starting. So. With. That I would, like to hand over again to care thanks. Thank. You Gilbert. Right. So. Gilbert, showed us an example where they, they, took the data to new to, excel so they could perform. Custom calculations and. So. What we're gonna do is now talk about how you can extend excels, function. Library so. With. Inside at excel as, part of its calculation, engine there's, a hundreds. Of different functions that you can use that are built in natively and. One. Of the things that was. One of our top requested items. On user voice was. The ability to allow. Developers to extend these functions, as part of web Adan's so.

Now. You. Can do that we have been in, preview, for about, a year now and. You. Can use custom functions, to do, calculations, or bring in data likewise. You can use custom functions to bring in, dynamic. User data that can stream in real time so, folks have built, with like the real. Time data servers, the RTD servers, in the past this, capability is now built in for you with. Office. Web Adan's. All. Right so I'm going to jump back into a demo. You. Can tell oh. There. We go so. Yesterday. Satya talked about our, cognitive, services we, demo that in them 365. Keynote so, one. Of the first functions, I'm going to show is a. Function. That. We'll, pull in. Will. Call Azure cognitive services to, do translation. So. Here we have some code to the right which we, have some metadata which registers, the function to excel and. Some. Code that is going and calling. Cognitive, services now, in this case I'm actually calling an azure function, and. So, my, cognitive. Services code was written in c-sharp and net, and. I really just have five lines of JavaScript, code I think that's five ones to. Actually make, that as, your functions, endpoint call so. When I go and do. This now. This. Is going to take a little bit because I'm on my free version of. My. Free subscription, of azure functions, so I do need to prime the pump a little, bit but once it comes back the subsequent. Calls will. Be. A bit quicker and so, what, this is doing is, for. Demo. Purposes, it's calling. My Azure function which is calling cognitive. Services, way. Yesterday, at the M 365, keynote demo a, sentiment. Example, that was calling cognitive, services directly, so, here, we did this and my result, came back so, now I can just use the, rich Excel capabilities. Around formulas. To. Manipulate. My data and now, subsequently. The data results. Are coming back much more quickly, now, each of these calls are doing. Single request. So. If, I did really need to use the restroom right now this is what I would say in Spanish. If. I, wanted to see, what that was, like in French. It. Would go and calculate and, I could see that in French. Just. Real quick so. I can highlight that as your function, if it'll show up here. Okay. It timed out I will move on. So. The, next demo I want to do is, a. Feature. Called. Dynamic. Arrays and. How, custom functions work, with. That so. If, you've, built xol. User-defined, functions vba user-defined. Functions, and you have to return multiple values, the. Way your users actually, entered those values they'd have to know this magical, shortcut, ctrl shift enter, and they would have to know how many cells to, fill now with. Excel. Dynamic, arrays Excel. Will automatically. Detect. How, many cells. Your, function, is returning and spell that for you so. Here's. A function. That calls a free stock quote trading. API and it, returns X number of days of closing prices, so, here I have a dashboard, which says if I invested, 10 days ago in. Some, companies so if you're here in Seattle you might as well invest in Northwest stocks, and. It's. Going, and calling, getting. Stock quotes for some, companies in the northwest so, let me just rerun, this to. Show you what, it looks like with one function, o press, enter and realize, it in cell. B4, that. Updated and then the other cells to the right cleared so. If I do that again. You'll. See that that, data automatically. Spill out for you now, if there was values. In the data Excel. Would know and it would return a pound spill error so, I think this. Should yeah. So, now we see, we, get a bounce build error here. So. This is actually super powerful, when we talked to providers. Who have built custom functions, before, because. What they'll tell you is hey, the code, to call the functions, and return the values was fairly easy but, to actually make it spill that. Was where they were spending so much code so, much time so much energy. In, maintaining. So. Just. To complete the demo. The. Markets been fairly volatile, as you can see from the chart below so I'd really did not make any money in the last ten. Days. Alright. Last, example, that, we have here is an example of a streaming function, so I'm, going to go back to our samples. If. I type in custom, function, here you can see some custom, function samples in script lab. Here's. An example of a streaming function, in. This, example here we're getting, we're. Just incrementing, the value a, few, times but, just. To show you how, you can, return. Data dynamically, to excel there's. A few things here one is you, specify. Streaming. Invocation. Parameter. Type and what. That will do. Is allow you to return. Results. Back at an interval so excel will maintain a, reference. To that handle, so. You could do things like call back on a set. Interval or if, you're connecting. To more real-time data you can have WebSockets, that go and say hey, when you have new data you can ping Excel simply.

Through This set. Result command. And. Return. That value back so. If I run this function here. This. Is just going to count up of. Every. Second so. That's hard to see there. You go. Alright, so those are some, examples of. Excel. Custom. Functions, so. Use. Functions. To. Create reusable of multi-platform, calculations. Real, time data streams and also. We showed out how you can do compute, in, the cloud so you're no longer limited by the computation. Of the clients, machine right you could you, could use the cloud scale out that computation. And then bring the results to Excel and. Then last we also showed a dynamic, race so, dynamic. Arrays is, a feature that's currently, on. In. Preview. Right now but we do think hey, we with Excel custom functions and dynam mekka rays coming, out it's, going to make your, life much. More simpler. As. I mentioned we've been in Developer, Preview for, about a year now and. Over. The course of the year we've seen a lot of we've. Worked with the community and, we've. Added a bunch more capabilities. That we realized, the computing community, needed, so, we talked about streaming we also added the, ability for custom functions to be volatile, so, if, you've seen the ran function, and volatile functions, calculate, every time Excel will recalculate. We. Have optional, parameters, I, demonstrated. Like the script. Lab dot name. That's, a namespace. That can also be also optional, as well, we. Support multiple different. Data types and. We added the ability for any data types. We. Have scenarios, where, partner's, needed the calling address of that particular, cell so, they could do further analysis downstream. And so, then we have, that and then the other thing we partner. Has told us is hey we, have users that have built, these. Workbooks over, a large, number of years using, our existing comm atoms and Excel UDF's. And we really needed that to, work in the new world as they move to Excel online and so, with that we introduced, a compatibility, mode for, JavaScript, custom functions in Excel all UDF's, and we have a bunch more coming soon, that. Said, custom. Functions if you didn't hear yesterday there now GA you can use them in production, across.

Windows Mac Excel, online big. Thank you to all. The, developers, who. Gave us feedback tried, it out, during. The preview. So. With. That let's, actually see. How one of our other partners is. Using custom functions sahil. Thanks. Q hi. Everyone I'm. Sal Serrano, I am, a senior engineer, who. Works with the Bloomberg score applications, team, we. Build. Add-ins. To get Bloomberg data and analytics, into, Microsoft, Office. I'm one of the five thousand, engineers who work at Bloomberg. Bloomberg. Is a leader in global business, and financial, information and, news. Bloomberg. School product, the Bloomberg terminal quickly. And accurately delivers. Data news. And analytics. Through. Innovative technology. Bloomberg. Terminal was first introduced, in 1982, to bring transparency across, global, financial markets. Today. It's, used by more than three hundred and twenty-five thousand. Influential, decision-makers. To, turn knowledge into smarter, and well, informed financial decisions. To. Give you an idea of the scale we deal things each. Day we, process, more than a hundred and twenty billion, market, data text in real time that. Means normalizing. The data cleaning. It and making, it consumable, for our clients, across global, capital markets in mere, milliseconds. We. Also build in deliver analytics, and trading, solutions, on top of just market data along. With email, and instant messaging. Each. Day our clients, exchange more than a billion. Emails. And instant messages. Now. That you have an idea of what, Bloomberg, does and how the Bloomberg, terminal is an integral, part of our clients workflow, of conducting. Research, making. Trades managing. Their portfolios, it. Should come as no surprise that. A majority, of our clients also, use Excel to drive their financial, models. But. Bloomberg data. So. One, of the one, of the client workflows, that I want to talk about today is the investment, idea generation, process, a. Portfolio. Manager who. Makes decisions about investing. In different financial, instruments, consumes. Information, and research about those instruments from various sources, these. Sources can be financial. Information services. Like the Bloomberg terminal. External. Brokers and dealers and. Their own research analysts, now. These research, analysts, who work for the portfolio manager, they. Visit companies. Our executives. Attend, investment, conferences, to, get an idea of what a company is doing what. The pipeline, of product, looks like and would, recommend and, investing. In that company. They. Then share this research with the portfolio, manager. Along. With supporting financial, models so, that the portfolio manager, can act on this information. Let's, look at this workflow, in, desktop, Excel. Switch. Over. So. This. Is a financial model called discounted. Cash flow on, a very high level discounted. Cash flow is a valuation, model which estimates the value of an investment. Based. On its future cash flows so. Let's. Say I'm a research analyst and I'm visiting Microsoft, I, look, at that pipeline of products, I made their executives, and based. On a growth, rate of 4.3%. Let's. See what the value, of Microsoft, stock would be at the end of the year. So. Based, on a growth rate of 4.3%. It says Microsoft. Stock will grow by 4%. This is pulling in real. Data by a Bloomberg, add-in and Bloomberg formulas, to give you an example of a Bloomberg formula, the very simple one is be DP or Bloomberg. Data point I can, put in the. Ticker. And. Let's say I want today's. Price. I can say a last price, and. It will go and pull in the. Data in real time so. After. Meeting Microsoft, executives, and you know looking, at the pipeline of products I think Microsoft is going to beat this growth estimate, and let I believe it's going to grow at at least 5%, so. Let me put. In my estimated, growth rate gives. Me a target growth rate for the Microsoft, stock at 15%. So. This has all the, prior this, financial models all the data it needs you. Know it's pulling in all the estimates cash, flows everything, that needs to power the entire financial model, I. Can, put in my comments, also that. This is why I think, you. Know the stock is going to grow by this much and. I'm, recommending it, recommending. My portfolio, manager to buy I can.

Share This information with my portfolio, manager, using, the Bloomberg added. The. Click of a button I can, upload. This to the Bloomberg terminal. When, I upload it to the Bloomberg terminal my, portfolio, manager would get a notification that, a, research has been published and, he. Can go to. The terminal and. Look. At at real, time he, can drill in further, look. At my comments, look at the entire, financial model, that I published. Along. With all the supporting, numbers. So. This, is what I just published two. Minutes ago you can see growth. Rate of five percent target growth rate of fifteen percent and the, entire financial model, he. Can also use other analytics, that is built into the Bloomberg terminal like. Charting, so he can go to. Graph. Look, at the Microsoft, stock over the year look at all the research that has been published, over time so it is these tiny I can see see at the top this is the research that was published over the year he. Can go and clicking. On any. Research. Go. In and read, that research along, with the, entire financial model. You. Can also look at other statistics, about Microsoft, so he. Can go in, into. Financial. Analysis, look. At revenue. Gross, profit, look. At income statements, balance sheets etc, and, then make his trade recommendations. So. So. This has been fairly straightforward. For us to accomplish in desktop. Excel but the Bloomberg add-in and. Bloomberg. Functions, but. As research. Analysts, are increasingly on the move, it's. Not always possible for them to have access to desktop Excel in. This, case they would have to wait till they can get to, desktop Excel then, write up their reports, and recommendations and, share. It with the portfolio manager. Now. Very often this research is extremely time critical and the portfolio manager would want to see it right away. This. Made us rethink on how we can support our clients workflow, on the move, so, that they can quickly write up their analysis reports. And recommendations and, then easily share it and. This, is where custom, functions in Excel come into play. Custom. Functions in Excel give us the ability to seamlessly migrate, our, clients workflow, from desktop, Excel to Excel online, by.

Enabling Our clients, to. Query, the Bloomberg API to, fetch and manipulate. Data in, real time and drive, the financial, models from anywhere, the. Web, browser made a tablet, beat a smart phone we. Give them the flexibility, and the choice that they want in. A more complete solution, that supports the way they work and most, importantly, where they work. Now. Let's look at the same workflow in Excel, online. So. Here. I have the same data, model but, this time loaded in a web browser in Excel online. What. I'm going to do first is authenticate, myself sign, in with my credentials. But, in my credentials it asks, me for my two-factor. Authentication. Authenticates. Me why am i fingerprint, this is a bloomberg supplied device which checks my fingerprint, and gives, me a one-time, access code so. This, flashing thing actually is a code, which can be read by this device, securely. I'll. Put in the code. Now. Again. In. A-put Microsoft's, talk. Can. See all the custom functions fire the entire data model will evaluate and, give me a target growth rate. Networks. A bit spotty but you will see data come in and we'll. See the growth rate come, up here. Still. Calculating. Still, fetching. And. Any. Time now. That's. Conference. Network mix party. Right, it. Will eventually get there. Let's. Go look at the architecture. We have in place to support. Custom. Functions so. Here's. A very high-level view. When. I put in my credentials and authenticate, my, credentials are sent over to an authentication, server which responds, authenticate. And responds with a token which is valid for the session. Anytime. A custom formula fires that, token is sent along with the data request to Bloomberg servers where it's validated, and that request is forwarded, to the Bloomberg API. Bloomberg. API responds back with data which flows into the same path into. Excel online. Another. Thing we did is to introduce client-side. Batching, to our formulas, so when a bunch of formulas fire we batch them together and then send it over to our servers, and. That has led to significant, performance gains so. As an example. I'm. Gonna pull. Ten. Data points for each of the securities, in the S&P 500 index, so, that's 5,000 formulas, I can use a Bloomberg.

Popular. Table functionality. To put in all the formulas, together as. You can see all, the formulas fired together and. If. The network would help me the data would come. There. We go. So. With batching, and a, good network this would have worked, this. Would have worked you know I would have fetched all the five thousand formulas in about two seconds and to. Give you an idea if Pierre had made five thousand separate requests without batching, that's what I've easily taken, me close to fifteen seconds. So. That's. Magic. To. Accomplish, this batching what we do is when a formula fires we, put it on a cue and that cue is flushed, based on either a time interval or the cue exceed a certain size. There's, a link here which leads to the patterns and practices, for. Using. Batching, which we work with Microsoft, along, with other patterns and practices, should, really check this out it's going to help that really, help at performance. They've. Been working really hard over, the past year, with Kaos team ever, since custom, functions were announced. To. Get support custom functions so that our clients can support, their workflows, on the go and all. This hard work has paid off in something being known that our our times would be eager to adopt we're. Really excited that. Custom. Function that Bloomberg custom functions would be available later this year for our clients, thank. You. All. Right. So. We've talked about our, what. Add-ins are we've talked about our api's and, we've talked about custom, functions. I'm, curious who, has a office. Or combat, and/or, a VST Oh at in an audience. Okay. About. Half of you, so, one of the things, Sahil demoed, was he started off on Windows. And he. Demo there the, Bloomberg's combatted, and then. He he. Went to Excel online where, the web ad and was, running and so. Essentially. What was, accomplished, here was what we call the com2 web atom bridge so. What, we will now. Allow you to do is, to. Essentially, have that on Windows, if your users are using your combating, they, can continue to use that combat and in the in their flow as. They would if they're, not using that combat. In well then they would get your web add in and then, on Excel online, excel. For mac that's, when they would get the new, web, add-in experience, and. With. The compatibility. Mode with custom functions, and XML UDF's, you can have co-authoring, sessions, where, you have a user on your, combat and working. Within. The same session with someone who's on Excel online and so. Here's, a quick, example from, one of our partners frontline solvers. Daniel's right here he's going to be here after. This session at, five o'clock talking, about their journey moving, from calm. To the, web, Adhan world so to encourage folks to stick around for that. So. The, way the combo web Adam bridge, works is you, essentially do add, some. Capability, at some declarations to your manifest XML so. There's two parts to this the first part is. You're. Specifying, your UI portion, so, where you're specifying your Prague ID for your existing combatted, and you. Just a type, is calm and so, this, is essentially. What we use to trigger hey should. We. Show the web add in UI. Or, not because, the last thing want is IT, deploys, a web. Adhan and someone. Gets two of, your ribbon, buttons and they get confused so, this will help mitigate some, of that. Likewise, if the as I said if the combat and isn't installed or IT, can uninstall, the comment and by setting group policy, to. Move folks over to the new model the. Second part of the, equivalent dead ends is you can specify.

A Compatibility. Mode of your Excel, functions, so, here you just specify the name, of the your Excel oh you say type equals Excel and that will. Essentially. Put JavaScript, custom functions and it into, the same file format as, Excel. All user-defined, functions so. This, capability, is. Available. On the. Insiders, Channel right now. Try, it out it's going to roll out shortly like it probably, in a month or so to. GA. As well. So. To. Wrap things up I didn't want to say it's. We. Have a bunch of capabilities, and it's. Easy to get started, to. Highlight just three things, first. We. Demoed. Script, web so. I highly. Encourage everyone, if, you, after. Today go install script lab run it try a few snippets just. To get a hang of hey, how the office. JavaScript, API. Is work. We. Can we have, the ability to build add-ins, in any editor, of, course we love, vs code and we love Visual Studio so we have. Tooling, support for both of those editors. Likewise. To. Get to help you get started we have a yeoman generator that can scaffold, out different templates and these, templates can be in the framework of your choice so, if you're using react we, have a template for you if you're using angular. Or. Even plain old jQuery, likewise, we have templates including custom. Functions. So. To. Recap. Platform. Does have all the capabilities, you need to, at least go out and start prototyping. Custom. Functions in Excel 1.9, s or 1.9. Api's, RGA along. With the combo web atom bridge coming soon and, we. Have the tools to get started so let's, get started, a. Few, different ways you can engage with us. First. Is here, are some resources where you can go and learn. So. We have links to our documentation, site we. Talked about our beta. Api's. So, we have this process, it's called our open spec process, where. As. We get new aps we release. The designs for them even while we're doing implementation so. It really is a community driven process. Talked. About custom functions as well and then, there's, other channels, where you can engage with us directly. So the, first channel is Stack Overflow so, the team is. Super. Involved just, tag your questions, with office Jas and someone. On the team actively, is looking for that we get emails all the time to. Say hey someone had this question and, so someone will take, that on if you, have issues, with the platform github. Is. The right place a, quick. Way you can get, to this is if you just go to our documentation site, and click Edit it'll. Take you to our github repo and you can just log it an issue there. And. Then if you have feature requests, user voice so we have user, voice and custom, functions is a great example of hey. That. Was one of our top feature, requests, that we do look at that to, help prioritize our, backlogs. If. You're inside of office that's send a smile we get those as bug reports, directly as well and in it that includes additional logs that help us investigate, and then. The, last thing, I want to point out is we. Have a monthly community call. Where. We talk. Like the calm to web Adam bridge custom, functions capabilities, that's that we've talked about it every. Month through our community call that, community call isn't just for Excel it's for word PowerPoint. And outlook as well so, if you're interested in those api's I highly. Encourage folks. To join, those calls for. Folks who couldn't be at build there's actually a community, tomorrow, morning. And. I. Encourage, you to take this, easy first, step. Go. Join our office, 365 Developer. Program, what. This will do is it, will give you access. To office 365. So. You can use that as a developer to start building solutions. Start. Working. With our latest, and greatest API start, working with custom. Functions it, also, gives you like all everything. We talked about with community calls things like that those are available, to, you directly.

Through This portal. As well as tools training and it gives you another channel to keep, in touch with us, so. Thank. You thank you, for being, up build thank you for continuing, our, journey we. Even. Have about three and a half minutes to spare so, we're. Here, told, the next session if not come to our add-ins, booth tomorrow. Yeah. And have a great build.

2019-05-10 18:19

Show Video

Other news