Itay Dafna - Leap of faith: Transitioning from Excel to Jupyter-based applications | JupyterCon 2020

Key Takeaways

hi welcome to leap of faith transitioning from excel to jupiter-based application by me itai dafna the jupiter con 2020 online conference just a little about me who am i i'm a software engineer currently working for bloomberg been employed at bloomberg for around seven years now the last couple of years i transitioned into a software engineer role and i'm focusing currently on building data visualization and exploration solutions my previous roles involved derivative analysis in the capacity of financial engineering and i also led a small team that did quant analytics i'm an open source contributor currently actively contributing to biki plot and ipi widgets so what is this talk about we'll be talking about the evolution of data analysis and how that evolution of data analysis has inspired an increasing number of analysts from various different industries to consider and even migrate to their excel based applications to jupiter the jupiter-based ones we will talk about why jupiter is a particularly popular choice amongst these analysts and some of the difficulties that they face when attempting such migrations just a disclaimer excel is an excellent tool in data analysis and the purpose of this talk is not to bash excel i am a proficient excel user and i use it every day even now that i can program what we're trying to get at is at what point is our analysis stop being a simple spreadsheet and is more of an interactive application that needs to scale with the input size and this is what we're going to explore in this talk so again this is not about bashing excel excel is excellent for what it was designed to do we'll be talking about the stages of migration so this is how we will segment this talk when is the right time to migrate do we even need to migrate how does jupiter fit in when we are migrating why is it the right medium and then the paradigm shift in the way we think about data analysis and additional considerations such as deployment and support when we are doing such migrations so do i really need to migrate this is a really important question that you should be asking yourself if you are in a capacity of producing some of these apps and you have spreadsheets that are increasingly becoming increasingly complex and to answer these questions we will be comparing two implementations of the same app one in excel and one in jupiter this application is purpose built it was built specifically for this talk and in that regard it is not really a useful application it is not real this is not something that is used in the industry we will add features to this application gradually to show and to try and find the the fine line of when it has stopped being a simple spreadsheet and is now a lot more complex than how it initially started and we will try finding that line at which we say okay maybe it is no longer a spreadsheet and it needs to be migrated maybe now is the right time to migrate from excel to jupiter so what are we looking at what exactly is this application it's a very simple application in a sense there isn't too much going on we're looking at the performance of foreign exchange currency pairs so think about how many swedish chronic does one us dollar buys me we will look at two charts in discipline in this application one is just a simple time series showing the evolution of the price or the exchange rate and the other one is the performance which is a term very fancy term just to say i'm gonna look at the percent change from one day compared to the other this application is finance related but that example itself is applicable to many different industries in all walks of life you for example could be interested in doing that analysis if you consider traveling somewhere and you wanna make a decision as to whether you should buy foreign currency an hour later and corporations similarly especially in functions of procurement and salary management payroll multinational corporations will be interested in this kind of analysis as well and at the end of the day we are applying numerical analysis we're just manipulating numbers so this easily could be extrapolated to totally other disciplines such as weather forecasting etc okay let's jump into the live implementation of the live demo of the excel implementation and walk through the evolution of this application okay let's look at the excel application starting with the inputs so we have a start date here and an end date this is for the time series and this is the currency pair that we're retrieving the time series for and we also have a return time series which we're calculating based off based on the the price time series that we have and that's simply percentage return of one day over the previous day the price chart here is just simply referencing the time series data that we're retrieving and if you look at the scatter time series same idea only we're using a scatter plot so far this is very basic an analyst will typically use that to put as a part of a rip to generate and maybe embed in a report either on a weekly or daily basis but really the inputs here don't change frequently and if they do it's very it's very rarely so for this kind of reporting or analysis which is very basic excel is is a perfect it's a perfect tool for the job but let's say that the requirements change and then we need a slightly more enhanced version of this reporting tool so we still have the start date and the end date that hasn't changed but what we're saying now is that we're adding a period and that period will effectively be i want to look at 30 days a period of 30 days ending in the end date here and also for the currency instead of typing the currency pair manually i now can choose from a drop down menu and i can say okay i want to look at what the how many swiss francs one us dollar is buying me and see how that has been in the last 30 days okay and i can also change the period here so i say i don't want to look at one month i want to look at 60 days or two months and then everything here changes now look there are some issues that you can immediately notice one is the border and we'll get to that in a second and there are other features that you're not really seeing which are not coming out of the box with excel so the chart updated with the full time series range when i change my inputs this is not something that you get for free in excel in fact this is pretty tricky to achieve and the way we do this is by naming this cell range giving it a name and a definition and we do this typically on a per sheet basis and we have to do this for each cell range that we're interested in so if you want to have dynamic dates for our chart we have to create a range for dynamic dates we have to do the same for the actual price as well so and if you see what it looks like currency price here that's the one you can see that there's an offset formula here and effectively the way it works is we are counting all the values in the column that this range pertains to in this in this specific case it's column f and we are basically redefining the bounds based on the number of the counts of the values in that column if you ask me we have to do this for pretty much every dynamic cell range that we have if you ask me it is a bit hacky it's not the most straightforward natural way of achieving this nevertheless it works now let's look at how we're doing the same but for the performance and again this would be simple we're looking at the price of a given day over the price of the previous day expressed as a percentage but notice how we have an if statement here and this if statement says okay i just need to make sure that i have enough data data points so i need to to make sure that i have yesterday's price and today's price and then if they exist then i apply the calculation if they don't then i just return or output this empty string here and if you look at the range that the return chart the scatter chart here is referencing you will see that it is just an arbitrarily long cell range and the reason for that there is no easy way of achieving this same calculation without getting into vba so actually if you see here i double click if i double click here that formula is still here is checking whether i have data to calculate the return on if there isn't then i just don't calculate a return and therefore i don't have a value which is then displayed in chart this can arguably you you can get by with doing this kind of excel trickery you could be more you could have a more a more neat result including getting these borders to adjust as well if you were to use vb vba there is no simple way in fact there is no way that i'm aware of of getting the borders here to adjust without resorting to vba programming now we are trying to illustrate the case of your average analyst in a given industry and the time investment for that analyst to then go and say i'm going to learn vba so i can apply this [Music] this cosmetic enhancements it's probably not worth the investment you can just get by without doing it so even still for that for this this spreadsheet and what we have here this specific application arguably is still excel is still fit for purpose it's still the right tool for the job it's a bit quirky but we can get by but let's say that we have something different now and let's say that this spreadsheet is popular all our colleagues across the disks across across across the desk are using it and they have asked us to add in more enhancements so what we're looking at here is the same same idea we're just building again where we keep on adding we keep on adding features to our application we have a start date and an end date given by this period so we're saying 30 days for the time series ending on the 15th of september for a given forgiven asset in this case we're checking how many brazilian realize can one dollar buy us and give this again as as like a time series but what we've added here is the ranking period and what this ranking period does we're saying i want you to look at the end date 20 days ending in the end date and i want you to calculate the performance for all the currencies you have available and we have 16 currencies available and i want you to take the average and i then want you to rank all these currencies and give me the top five for that period as you can see in here now there's a lot of calculation that happens you just don't see it in this spreadsheet but it is there if you look at the kind of the data that we're referencing we're referencing as a different a different spreadsheet within our workbook and that is this data calc sheet so let's quickly see what's happening here i have a period of 20 of 20 days and i have time series that i retrieve for each one of these currencies in that period and there's a formula here that retrieves these time series and i had to manually go and drag that formula across or copy paste for each currency pair i had to do that by hand it may not have taken me much of it wasn't much of an effort to just copy or drag one cell at a time but i still had to do it by hand for each cell so there's no way to easily automate this again without going into vba i had to then do the exact same for the return with that if statement that we saw in the previous spreadsheet and then i also had to apply a calculation for the averaging so for each one of these currencies i had to by hand write a formula to get the time series based on the time series that i got calculate the return and then again find another way physically somewhere in the spreadsheet then calculate the average of that the return time series for that particular currency this is arguably no idea and the reason we're doing it that way is that we're saying okay ranking period of 20 days really what we're trying to say show me the last 20 days within the 30-day period that i'm referencing here a more ideal solution would be to just say hey this is the overall period of time that i'm looking at and i just want to highlight a range here in that chart so let's say i want to look from the let's say the 27th of august all the way to the 8th of september and based on the highlighted range i want to then i want them the table with the rankings to update and also get a return this is something that excel simply can't do even with vba there is no way of getting that that feature implemented and this is probably the the case where this is probably the point where we start wondering okay is excel really the right tool for the job now that i'm really looking for a much more interactive workflow and the answer is probably no and that's when we should start considering migrating our analysis from excel to jupyter and this is what we will continue exploring so we've seen what the excel implementation of the application looks like i think we can conclude that it is no longer a spreadsheet we were able to get by and achieve the functionality that we were looking to implement but it wasn't the most straightforward or clean way of doing that we had a lot of quick implementations it clearly didn't scale well because for each currency that i wanted to calculate the return and then the ranking for i had to add another column i had to for each column of return so then i had to add another column that summarizes the um the the average return the ux wasn't great we were able to alleviate some of the difficulties by adding a drop down for the currency to select the currency pair and also we had a nicer way of controlling the start and end date but it really wasn't fit for purpose it would have been nicer to have something like a date selector or a slider we had some issues and we had to make compromises on styling so we couldn't really figure out the problem for the borders easily again without getting into vba and calculating the border margins and and etc and interactivity was just not not there for us so being able to highlight a range on the chart and use that range then as an input to calculate some to calculate the return and the and the ranking wasn't something that we were able to achieve so probably the right time to migrate to jupiter and let's look at the jupiter implementation without further ado so we've seen the excel implementation and now we're ready to look at the jupiter one but just before we get into the implement the implementation details of the jupiter-based application i wanted to spend maybe a couple of minutes to go over the prototyping and calculation and process of doing the data analysis that analysts who are migrating their analysis from excel to jupiter are going through so typically because an accelerated data is visible all the time whenever you are importing your data into into a jupiter environment typically pandas viax or any other popular data frame package you would want to make sure that the data is there as you'd expect it to look like so you want to visualize it you want to get some sort of feel as to whether the data is in the format that you expected whether the columns are organized as you expected and whether you have what you need so this is the first first and foremost one of the most important and probably one of the more difficult things for analysts who are migrating to to do because there's a paradigm shift in the way they're used to analyzing that data the next thing is if you recall we calculated the return in the spreadsheet for each currency pair manually we had to apply that column by column one of the great one of the great things that the python ecosystem gives us together with pandas and numpy is the idea of vectorized operations actually what we can do here is just simply calculate the return for all of these currency pairs in one go and we have the output so we don't have to this this so we don't have to repeat this manually for each additional input that we have this scales beautifully and it is one of the key differentiation factors for analysts who are migrating their work so once we have that we we save this as a performance as a performance data frame and then in here i am again showing examples of how an analyst in a typical situation would then start applying or experimenting with styling um and potentially you know trying variations of different calculations to get the ranking output that that they're looking for similarly also with any styling if if if we want to create like a table of some sort that we're then displaying um with color highlighting etc typically there will be a separate notebook where all these calculations are done so we're ready to see the implementation of the application that we've seen in excel on the jupiter side so we're going to start with just a simple html widget here that controls the styling for one of my tooltips i wanted the text color to be white and that was the easiest way of achieving that we are ingesting the data here from a csv file that i've pre-compiled but notice this could easily be any sort of api that gets the data programmatically from the previous from the previous notebook that we worked in the calculations prototyping i know that this is the correct calculation i'm confident within it so all we have to do now is just incorporate it into our application this is just a list of currencies that i'll be using to that i'll be using as options for the drop down menu when we conduct our analysis setting up two charts here both in bq plot one is a line chart for the time series of the price of the asset and the other one is the scatter chart for the returns i am inferring the start and end dates of my data set the the the availability of the time series so how much data do i have um i'm inferring it from my data set and i i store them in a variable that i can then use um for my drop down boxes for the date selectors here we're defining some callback functions these will be called by some event listener that will listen to a change in one of the traits of the ipad widgets widget instances that we defined below and then finally we compose all of these into a graphical user interface and we have our app so let's select let's say the euro dollar so how many if i'm a european citizen i'm earning my salary in euros and i want to travel to the us how many dollars can i buy how many euros dollars can i buy from one euro and i'm going to use the power of interactivity here to highlight the range let's say i want to look between april and july 2019 and for that highlighted range i then get the scatter chart updating with the return for that period and i have again all the rich interactivity that the jupiter ecosystem gives me and notice that i also had my ranking table updated again quite easily and based on the input that i am generating from my date range selector so again a very simple app but we are able to seamlessly achieve and streamline a lot of the calculations that that we had in excel without too much additional effort and this scales beautifully if i were to make this if i wanted to analyze 100 currencies i could do that all i have to do is make sure that i have the i have the data in the in the csv file that i'm ingesting and i'll need to update the list here which again i could also probably do programmatically by inferring it from the columns in the spreadsheet in the csv file now this is this is a local application so clearly if one of my colleagues wants to use that they will have to set up an environment with python and all these dependencies here installed this is not straightforward but thankfully we still have tools that allow us to overcome these challenges so i could use a voila dashboard in here and just this just deploy that very same application as a wall application and there you have it the benefit of deploying it that way is that any changes or enhancements that i want to make to this application i only have to make now in one place and then we enter into this publisher subscriber kind of model which works pretty well for smaller firms so we've seen the jupiter implementation of our foreign exchange application there's quite a lot of stuff in there so let's unpack what we've just seen the first thing is there was a paradigm shift when we moved our analysis from excel to jupiter we had to readjust or recalibrate the way we think about analyzing data data analysis in python and therefore jupyter by extension typically follows this pattern you have an input the data input you ingest either a csv file or you query some api and you get the data you don't necessarily see the data you can you might be able to print a subset of it but you don't have full visibility like you have in excel of the entire data set if you want it once you're done with the input phase you move on to processing and that is when you actually apply your calculations um any models munching cleaning up etc that is done in the processing phase in the in the video where i demonstrated the sort of thought process of how you do this as an analyst migrating from excel to jupyter you saw that we had like a separate notebook where we prototyped and we sort of tested right iteratively to make sure that our calculations and what we're expecting checks out and finally there's the output phase here where you either generate a chart you know you have your data it's now ready and you've generated a chart or you you export that content that content to be then used in another context this is quite different from what analysts are used to in excel because if you think about in excel these three operations those three stages really are done in one your data is fully visible it will be somewhere either in a column or a cell you can always check the calculations you get immediate feedback right if you want to change the calculation you just double click somewhere you edit the formula the arithmetic and you get an immediate output this is quite different and this is often where analysts are finding it challenging when it comes to migrating their applications from excel to jupiter because it does require a shift in the way they think about data analysis there are of course other considerations it's not just about that paradigm shift in data analysis a lot of them will be using the opportunity to migrate from excel to jupyter they'll be using that as an opportunity to learn python typically there is a synergy there in the sense that they're adding value to the business by saying hey we're making the application and the process a lot more efficient by migrating it to a much richer more modern ecosystem but then also at the same time they're enhancing their own personal skills so you will often find an analysts absolutely love learning python in parallel when they are migrating their their applications some of them will then depending on how on how much control they want to have over the visual aspects of the application um some of them also get into a bit of html and css thankfully due to the way the jupyter ecosystem is is is working and generally the widget ecosystem javascript is mostly avoided so most of them can do 99 of what they're looking to achieve just by using the tooling that they have available on the python side an interesting point here is that engineering resources can become a thing now if you have just migrated your application from excel to jupyter and you're running it locally at this point you will have set up a local environment with python and the relative and and the needed um dependencies but if you want to ship that over to your colleague they will need to have that same environment on their machine which is not easy but you know in many cases it is done however you will need to have someone who is who can understand and can sort of work around um the different desks to make sure that the environments are installed so it starts getting into a bit of like an i.t support kind of role now if you are if you are looking to deploy this from a central repository which is fantastic you still need to account for some resources and that is you need to set up a server it could be either a binder type of server jupyter hub maybe or a voila but that need that server still needs to be deployed somehow and to be managed and these are this is not something that you can just do you know as a one-off someone we need to manage this and you will probably need to have a budget at some point depending whether you want to go with a local approach or the central deployment approach you will need to have some sort of budget as a business allocated to it some businesses take issue with that um others don't um but i just wanted to put that up here because this is a consideration that you normally wouldn't have when you have an excel based application because excel is by definition giving you the environment on each of the machines that your colleagues have if they have excel and they have the excel file by definition they have the environment and all the needed dependencies to make sure that that file will run so why do analysts like jupiter let's start with the fact that there is a graphical user interface this may seem like a minuscule thing but actually it's massive excel gives you this very familiar environment your data is always visible on a graphical user interface it's fully mutable having a graphical user interface however minimalist is key then there is the idea that you get this instant feedback via the repo this is a big thing right if you want to quickly iterate over your calculation you don't have to you run python space some sort of file i mean a command line each time so the the interpreter the ipython infrastructure and generally how it lends itself to jupiter is again key so you have a gui and you have instant feedback again that ties quite that segways quite nicely into the fact that you can have rich outputs it can be html table um can be just a simple um you know white on black standard out can be a widget this rich output is is adding this layer again um a layer of richness that that bridges that gap between migrating from excel which is fully fully visual into something that is sort of semi-visual but not as bad or not as empty as just a simple text editor with a command line interface so output are persistent that could be useful when you want to send the calculation that you have and you just want people to see the output to an extent not always but in most cases that also works with widgets you can you can serialize them into into static so that they have a static output the python ecosystem this is a massive one your problem has been solved before in the case of calculating the ranking in our fx application we just relied on some of pandas's built-in calculations to calculate the average across all columns and then we just use a sorting function to sort the top five to sort all the columns um in a descending order and then we just took the top five and then i can't say this enough um but really jupiter is probably the ideal environment for learning python and there is i learned it on on jupiter when i first started out and you will often find that people coming from these backgrounds where they analyze data in excel find that again this minimalist graphical user interface with the instant feedback um it's just a very familiar and very nice environment to sort of try things out and get an immediate output of whether that calculation is right or wrong and this lends itself quite nicely to learning python in general and then the final point here which ties all of these together is that there is just less of a gap when it comes to migrating from excel to jupiter because you you don't lose a lot of the features that you you're used to you have a gui you have instant feedback it's not quite the same you still have to apply you know shift the way you think about data analysis but it's much easier to digest comparing again to moving to just bare bones text editors with a command line interface which is you know white on black um standard out so just a bit of summary of the benefits of actually both jupiter and excel we're just trying to compare and contrast here so let's start with the jupyter ecosystem you can again leverage a python um ecosystem to install packages no need to reinvent the wheel we did have to apply calculations repeatedly for each column that we wanted to calculate something based on in excel no need to do that and someone probably already solved your problem in the python ecosystem you can reuse old code snippets across different apps not easy in fact nearly impossible to do in excel because you just have to replicate that calculation each time without overlaw of copying pasting but you know thanks to classes and functions and just saving things at python files you can reuse your code and and generally your your applications will benefit from that sharing deploying apps and updating your code from one location again you can use something like a jupiter hub binder or voila once you have that up and running you can you can move into this publisher subscriber uh kind of model and then when you want to move quicker with releasing updates based on feedback you get from your colleagues these tasks become much much easier then the benefits of excel everything is visible again we keep on hammering this point but everything is visible and accessible to the user on demand you don't have to there's you can always find whatever you want to whatever cell you want to inspect or whatever calculation it is all will be it's all visible at all times the cost of setting up are new you just run the excel if excel is installed it works it's as simple as that you don't have to set up any virtual environments deployment and sharing and deployment in double quotes here because you don't really deploy you just share your file it's not really like a client server kind of architecture nevertheless it makes sharing much simpler because you don't have to worry about maintaining a central server or a central architecture for or an architecture for central distribution of your application now comparing some of the challenges right when it comes to using both systems so in the jupyter ecosystem you need to learn python and depending on your circumstances potentially even html and css that is a big undertaking it's nothing that should be taken lightly when you are trying to build python and at the same time build an application it's a challenge there's a new workflow again data not visible we keep on touching that on that point from various different angles but changing the way you think about analyzing data is key deployment and sharing right it is a strength but also a challenge and deployment needs that means that you may need to have resources and it's not as simple as just sharing your ipinb file and for someone else to open it like you can with excel some of the challenges that that you have in excel is i put here retrieving data programmatically from external resources but a better way of putting it is to just say the amount the number of add-ins and custom libraries that you have available to you in excel is limited by definition compared to the ecosystem of python and that makes extensions um that makes basically making someone else who solve that problem solve it for you a bit more difficult we've seen how quirky some of these dynamic cell ranges calculations are where we had to use the trick within the name cell ranges and then dragging this this formula with the if statement down it works it's quirky it's not always intuitive it's a bit of a clunky interface there's also scaling in here excel doesn't scale nicely the calculations don't scale nicely with the or the size of your input and interactivity is very challenging in excel right just can't get the same level of buttery smooth animations um in excel you don't have a lot of these systems where you could you could highlight a range in a chart and use that as an input to another function just doesn't exist you've made it thanks for sticking with me all the way through the talk i hope you enjoyed it and you got something out of it if you have any comments questions on what you've seen um maybe you are now thinking of migrating your application from excel to jupiter or maybe you just completely disagree with me and you want to tell me please feel free to ping me a message on linkedin or just send me a tweet i mean i'd love to have a conversation of course bloomberg are hiring so if you're interested visit visit the careers careers website and again thank you so much for watching and i hope you enjoyed it