 Welcome to this hour in insurance webinars for January 2024 on behalf of the consortium adoption series. The series will run for four consecutive weeks. And in this today's topics, we will start with some relatively simple concepts and as we progress, we will start building up on that. And so for, for today's topics, we have some thoughts and some ideas about how we could transition from excel to in the programming in our, and we will follow up next week on how to move from programming to art to putting art into production. And then in weeks three and four we will capture performance culture topics. My name is Georges Bakolukas, and I will be covering the first two sessions. And my colleague, Benedict Sambrager, who I will allow to introduce himself in a bit, he will be covering the second two sessions. I'm an actually working for three three. And I'm not a programmer from an early age I discovered programming six, seven years ago. And it transformed the way I was working from excel into our and that's that's excitement hopefully I could pass on a little bit on that to some of you. I'll pass on to Benedict for him to do this himself and we will continue with the webinar topic for today. Thank you very much, George. So as already mentioned, I'll cover sessions three and four in a few weeks time. And yeah, also nice to meet you. I'm Benedict. I've also been working for three four a few years. And in contrast to George, I've already started my sort of journey with our in at university and have successfully used it over my career since mostly in data analysis and modeling jobs. So hopefully there's something for you to learn today. And I pass it back to George. Thank you very much Benedict. The background to this is that with interest rate we have a strong internal community, which has been sponsored for several years now. Since 2019 by a group to factory Philip long to adopt programming or day to day. And we have reached the level of around 2000 people in the community with 500 and regular coders that we support each other. And one of the community channels that we have is a Microsoft teams community channel that people can ask questions and the example we will use today is coming from one of the questions that came through this channel several months ago. And I will talk into the topic in into the next slide. I just like to clarify that any, any expressed views today are sorry, all the speakers are now employer. So the running example for webinars one and two is that we have an insurer who covers the remaining balance of loans in case of death or the bill for the borrower. And the, the actually had to provide a quote for this insurer for a portfolio of two many days here circa 300,000 policies. And the insurer had given for each of the policies the loan amount, the loan duration and the interest rate. So the immediate task for the actually was to calculate the summing suit profile for each of policies as it amortizes. So the more the client of the policy base the smaller the amount remaining balance. So if there's an accident or a death or a disability, the insurer will have to to pay less. It's a it's a job of the actually to calculate that profile to arrive at the premium. We will talk through a solution in Excel, which is the immediate approach that I believe anyone in the industry would be quite comfortable with and then we'll see a potential solution in our in the second solution. Second webinar will see why some of the moving from Excel into our trade off base off. So just to give it a little bit more of a background what we mean about the life as soon as court this is a screenshot of the solution that we'll step into in a bit in in Excel but I will just highlight a few things. In this table here we have the balance of the loan of 1000 at the beginning of the month. We assume that there is a loan for 36 months. There's an interest that is being paid. And some of the payment cost towards reducing the principal. And this is the balance that ends up at the end of the month, which carries upwards here BOP means beginning of period and EOP means end of period. And we can see how the balance runs off to zero amortizes. So, immediately we see two, two challenges for for the actually one is given this yellow inputs how to calculate how much someone would be paying every month and this is called the equivalent monthly payment. It's usually when you get a code for buying a car or something what would be paying for month and some of it goes from. And this is constant every every every month. The second, the second challenge will be how to to create this table where amortizes and we'll spend the next few few minutes looking at that and then we look at in the Excel. So, first of all, the solution to find the the equivalent monthly premium equivalent monthly installment is already there. If you Google it, you will find different interface just you can add some numbers and get the output. The question now is how someone can work on it on their own setup so they can add it for all these 300,000 policies. And also the mathematics about how to come up with the with the solution for the equivalent monthly income is easy to search online this is a formula and from when I was looking to that this was a very intuitive understanding of the interpretation so we'll take this as a given. So we can move on on this short series of the webinar. So one thing to note is that this calculation that we need to do on the equivalent monthly payment, essentially applying a formula in Excel with all these inputs. It's quite similar to how we will do it in in our in our we will use an assignment operator to assign values to variable. So this equivalent for this input. We need to calculate some intermediate steps, which is essentially the duration in months and the, excuse me, and the monthly interest rate, and then we can apply this formula to calculate the equivalent monthly installment. If we step into some of these calculations we'll see that the balance at the end of the period comprises of the balance at the beginning of the period this F5 value, the interest that this balance is accruing minus this equivalent monthly income. I will I will show you a next slide that shows the same calculation as before but now instead of picking up the interest from from this column, I'm calculating on the fly by multiplying the balance again with the monthly interest rate for a reason that it will become obvious as we move ahead. And so this is a simple formula that we can apply. The next challenge is how we can bring that formula down and in Excel usually you can link the sale of the next row with the sale of the previous row, and then you can drag down the formula. If you look at the formula view of the Excel file, you can see that essentially what emerges is that this is a recursive calculations where the the payment, the principal left at the end of the next time is equal to the principal at the previous time plus the interest rate applied to the principal of that previous time minus this equivalent monthly income. So we immediately see that the challenge that we have within within R is how we can translate that into into coding we saw previously how calculate the EMI. So some small stepping back to cover a few are concepts. So the first our concept is that our supports vectorized calculations. And what we mean by that, assuming they'll have two vectors x and y. We can put them together, we can use this plus symbol which is a vectorized operator and express the summing up of all the elements of x and y in a simple formula with only to copy paste or drag down. It just appears once. Now vectorization is always easy. A key, a key challenges when we have values of a vector that depend on the previous value of the same vector. One solution potential solution is to explicitly create an iterative loop. I will not go into much detail of that. But I'm going to just notice that for each of the elements, the element, the J element would be the equivalent of the J element of the X and Y, and then have to iterate over the whole vector. It works, but it's often verbose we have to write a lot of code and sometimes it's not clear to see what is the content of the of the calculation. So it's, it's, it motivates us to try to find the most succinct way to express this calculation. So one, one ways to find most succinct calculation is to use recursion. We'll end up using the recursive functions reduce and accumulate, but to warm us up, we'll start with some and cumulative some, which might be more familiar to people but they also can be considered as special cases. So I started with the plus symbol here. This is a binary operator for addition. If you some one plus two, you get three and under the hood is just another function. So if I use the special characters back to treat as a function, I can say add one and two. But this because a binary function, it only accepts two arguments. I cannot use the plus involved for one, two and three. I get an error. I wouldn't have to iterate and say, use the plus for one and two, and then use the plus for the number three, and there is out of these two. But this is cameras I mean convenient. So, thankfully, we can avoid that because we have the sum function which essentially does this recursion without us having to explicitly write it. So someone wrote the function some in from the the our core team that allows us to succinctly describe this. So it's a special case of a recursive calculation. And if I give another example for the cumulative some support that we have again 123 as a vector. If I want to calculate the community sub I will still have to say the first argument is the first element of the vector the second is the first and the second the third is the first the second and third. That's also cumbersome, but with cumulative some we can arrive at the same result. So where I'm getting with this. So we abstract away what the, the, the sum calculation does, and we use this reduce operator. Essentially, if we apply the reduce operator as a concept to a specific instance which is the function plus, then we can see that we can get the same result as some. So that we have to, we have the flexibility with the use function to define which function want to apply. And if we want to use a cumulative some set of some, then we use this accumulated once through which is works like the cumulative some it just keeps all the immediate values. And now, why this is relevant to our case. To do that, I will show you two alternatives for the reduce and accumulate that come from the pair package that have some benefit compared to the base our functions when we are going to be using them in the second webinar for production. So, some of the data output is more type is type stable, and we have some more consistent way of expressing the arguments within the function. So, now let's do a couple of exercises so the first exercise is to use the accumulate and define the function. The function is a square plus be and use the function iteratively successively on these two three and five. So according to our definition of accumulate the data is x the vector and the function is a function. So we can see how this works the first argument is the result of the previous application. So essentially, when we start this just to so essentially, we have nothing plus two and then we have to. And then for the second argument, we have the previous which is the two to the power of 24 plus three, seven, and then we have seven to the power of 249 plus 554. So, any, any function can be calculated with these accumulate very carefully. Let's make a little more difficult exercise. Now we don't have a function with two arguments like we have here and be we just have one argument. And instead of having a vector like we had here we had just one value. So essentially we have a single value. And we have to iteratively apply a function to the single value. So the accumulate function again. We will allow us to define an initial value x, which is this tool and apply it for the length minus one times. And again, we see that we have the two to the power of two becomes four to the power of two 16 and so forth so forth. So we can see that this accumulate can work for a single value and a function with one argument. Now, if we go back to the data that we use before the inputs that we had, and we have already defined or observed from using at the Excel file, what is the value of the loan, which is the previous value plus the interest minus the equivalent of the monthly installment for each of the next one. I will translate that as a function. And I have only one argument is PNSA and I'll call it a and the argument be I'm not defining it because I'm going to use it so it's a plus a times I minus DMI. I use this function onto the data set that has the length of my arguments minus one using the initial value as a I get the output that matches the output of the Excel file. So, I believe I have sort of solved this problem as well. So, if I put it all together, what I hear is I have a data and parameter inputs, which is a then years into in interest in years. And the immediate calculations, which are the monthly, the number of months, the monthly interest rate and this equivalent monthly installment. I'm defining the function, which I call it function three before I will use the more evocative name amortize for for the loan amount. I will apply this accumulate function over over the results. Now, just to save myself of space, I will print the first six values. So, what I observe from here is that many working patterns are common between Excel and R. And the claim I'm making here is that it often pays off to switch my simply to express its computing and programming. So, from this space saving succinctly describing the is the problem statement. The really the benefits are the scaling of the solution to several thousand or 100,000 lines without even getting any, any bottlenecks in the calculation plus how to production life, which will be what will cover next week. So, this, this webinar sets us up for some more advanced topics that we'll cover next week. And the, the first topic would be how we can build functions over this logic so we can reuse them, because we have 300,000 policies to to apply we don't want to copy paste it, and also abstract away the complexity. We will have understood what the function does we give it an informative name, we don't have to look at it again unless we want to even to to to understand what's going on and then we can use it as a building block. How can iterate over all the data and we saw in one example before how loops sometimes are quite the both. And for the iteration over the 300,000 data we will use some functional programming approach, which again will allow us to succinctly describe what we need to do. And then, and then we'll think about how to communicate that with others. So, first of all, to other programmers so quite often when we build a solution, we might want to share it with others this essentially other programmers is done by putting this functions into the package. So it's a programmer to program and if you if you allow me to say interface, or like we saw on the screenshot of the website I showed you before with the graphic interface. You might want to expose it as a graphic interface to to a car someone that doesn't care about coding. And this can be done in our using the sign framework. You can be able to build something more sophisticated and then you want to create a computer to computer interface. So how can this function then, instead of turning it into a package or a shiny application, how you can expose as a web API. So, so the in the next week's webinar, we will build upon on this and so how, how all of this can happen. I will, I will open for for Q&A in a minute. I just want to also make one more remark. Switzerland joined the our consortium two years ago, because we believe it helps us protect our investment in open source, and also allow us to learn from others in the industry. I will note that the our consortium has strong membership from from the pharmaceutical companies who collaborate on on this technical aspect, and it provides a lot of benefits to to the our community but also to the companies themselves we have the same benefits in our, in our membership as Switzerland by inviting experts from the Microsoft membership to our conferences and learning from each other. So I would, I would encourage organizations if you, if you are planning to invest in our, or you are already investing in our to consider joining their consortium. I'll just open now for for Q&A and I will also invite Benedict to join me for the Q&A session. We have a few minutes available on the official time, but both myself are happy to stay longer for for informal chat or discussion around what we have seen today. Thank you very much. I believe you can either type your questions, or potentially allow yourself to unmute is that an option. I see a simple of the chat. Okay, yes, we can type the questions. Okay, I think a couple of questions are coming up. Hi, the first one. Hi there from Francesca. Hi there. Do you offer quality assurance support for colleagues who are coding in our and want to check the work. Yes, we do offer. If you want to know more about the Atelier program, if you search online on the Atelier program and the name of our group to factory Philip long, you will find an interview he gave to the actual magazine. A few months ago that described a little bit more approach. First of all, we have an internal guidance, best programming guidance note. Essentially, we try to borrow ideas from the professional software industry of how to apply DevOps and DevOps practices. And quite often all this information is available, but the examples are not really written for for our users in mind so we have taken some of these best practices. We have written examples that can be quite relevant, but we also run more formal code reviews and more formal live coding sessions where we, we interact with members of several months on a weekly basis where we provide some feedback and improvements of coding and then we invite them to our internal annual conference to present to the community that's opportunity for them to build momentum. And happy to pick up the conversation on this topic. Outside the benefit as well. A question from George. What are the benefits of using R versus Python? Why do you prefer R? And Noel also says second they are Python questions so we have a Python question here. I will, I will say, use whatever you, you prefer. The reason I prefer R is I discovered it when I was in university many years ago, but then I never used it because I remember I was even scared to load it into to request it to be a put onto my machine. When I first joined Swiss history, but then I accidentally discovered that whatever I could do in Excel I could do in R and because I already knew. And all the material was there and the community was supporting I became good at using it and the community grew as it is. That like in human languages, but in Switzerland they are for formal languages, the questions between which language we use it typically is a history question rather than a question about all what is in the vacuum considered the best programming language, but this is a very big topic and we can, we can always discuss it in a bit more detail I'm very happy to engage on that. Will the slides from the presentation the future ones be available. Yes, they will also the presentation I believe is recorded. Hi, did you have any challenges implementing R studio from an IT security perspective and also for hosting local sign ups. That's a very good question. We are preferred to allow Benedict to answer this question because that's the topic of expertise as well. Yeah, happy to give it a go. See, I believe, taking the two aspects of it so I think we are very happy it's a three that for, let's say, our studio desktop and regular on your computer or laptop. We support from IT through some IT certification that makes sure that it's probably configured and secure from an IT security perspective for use for hosting shiny apps I think more recently or more recently as we are also managed to get some of the positive formula studio from the company products that allow us to easily share shiny applications or auto documents or presentations like the one that George is currently showing on a server. We also supported by or by a very small internal IT team, we sent you make sure that the servers up and running and that people can deploy that say they're shiny apps there and it's available. And yeah, as I think George already mentioned as was very valuable if there are some active user community that helps guides or helps motivate or formulate these requirements to your internal IT teams to make sure that they are heard and implemented as useful to the users. Thank you very much. And as you rightly noted, this presentation was done in part of published on our talent positive connect and sharing it live here. A technical question on the topic at heart by Russell in your accumulate example how does I know what the previous value is do you need to ensure the data is sorted group in a particular way. That's very good question. Yes, the data are sorted and both in Excel and in our by the month which they have been occurred so it's a would you be able to say time series. Yes, it's a it's an order data set. Absolutely. Okay, Fran Federica, could you highlight a few key advantages of transitioning to our for these tasks. I think if the simple task for this one is just like one task and you can build it in a quickly next cell. That's that's a very good stop. In this specific case, the person that was doing it had to run 300,000 policies on it, and also not just once. Similarly, when you run to produce some casual analysis you might have to use different basis. Essentially, you might have to have a best estimate basis some more prudent basis basis that the regulators are requesting you by basis I mean different scenarios on the parameters that influence the calculation so it's run might be five or six or seven parallel lines of this 300,000 policies. And then it becomes quite time consuming. The other benefits would would also show on the on the next week's how you can put into production and and say more more professionally. I think that covers Federica but please feel free to approach later for more question. I will ask, I like to ask Benedict to respond to the doesn't object oriented approach help developing your programs in our or is it unnecessary Benedict. Great question and maybe also lightly touches upon the other spy from once a broadly speaking, it's probably a question of style. I mean, I personally, it also depends on how intimately, you know, are but broadly speaking, I think what George is shown today is a more sort of functional style and less than object oriented one. You essentially say you want to apply a function over different objects or here like a vector of cash flows and want to get the results that way. And I think that is a style of programming that is very well supported by our and that at least I found very helpful also for many challenges or issues that I wanted to solve in my work so I definitely think that sort of strictly or encapsulated object oriented approaches, not necessary in our, but I would also think that depending on like you and the team you're working with it could also be a valid choice to solve your problems. Thank you very much Benedict. I've been asked how long has there been a programming culture at Switzerland even for business people. Is it a fairly recent or it has been there for some time, asking because there is no such culture at my company any advice to make such transformation happen. I think that over several years there are pockets, they have always been pockets of people that have been coding and programming. The, the Italian program was kickstarted in 2019, late 2018 and was essentially a program that was sponsored by a group to who have had just joined as a group to factory on this role, previously a different role within Switzerland, and the discussion with the board was about how we can make sure that the actuaries are equipped with data science and communication skills. And we started quite, quite slow and quite basic running workshops in different locations and then the community grew as Philip would say people came out of the woodwork and actuaries are typically were problem solvers and when we're doing Excel we are almost programming. It's, some people have written papers about how Excel is a what's called a complete programming language. So, so it's been focusing on the last five years, I think. But if you want to implement something similar to your, to your company. Please feel free to reach out to us to discuss in a bit more detail, but also I believe the our consortium can also help the membership to the concert would help because that's the catalyst for some of the intra, intra company collaboration. So, no, this was a great introduction will be available to see the recording so we can walk through on our own. Absolutely. Yes. Now, Federica and oil. What motivated the decision to focus on the transition from Excel to our in the talks of insurance and actual science. I will try to attempt to answer that but also invite Benedict to give his use here as well. I believe that the sciences is the confluence of information technology and statistics essentially and actually typically we come from a background of statistics. We discover our university, and we come to the office and we started working in Excel and the functional style of our base quite similar to to the Excel so it made the transition easier. Plus, our is a language that doesn't require a lot of computer science background to get you started. So, so I think it will find it easy to get started. But I think the most important element is the community. I believe that quite often are users are not professional programming so they have a domain expertise and also are using our to get a job done. And quite often, they are getting paid in order to communicate their insights rather than build a solution that it will be just judged on how fast it runs or how little fewer bugs it has so that the production farm quite often for for actually is the PDF report you have to be prepared. But still you have to be able to rerun it quickly when the data change to have a transparent way of demonstrating to appear that there's a data provenance. So I think these elements are the ones that are the most striking in favor of of using our, but I will also welcome Benedict to to give his own view. So I think that were very good examples, maybe also touching upon one point that will be part of the somewhat part of the seminar said I would later on this. I think there's also the need or the at least I want to be more efficient so I think everyone enjoys to be able to do their work quickly and see the results for the calculations quickly. See, I think it's a new particular secret that generally data volumes are increasing and the more they do the less appropriate Excel may become for certain types of analysis. I think like George mentioned if you want to calculate something for one policy that may be easy to implement in Excel but if you want to do it for 300,000 that may be more kind of cumbersome. I don't even know I don't think that Excel supports 300,000 tabs that you can copy over anything like that. And I mean like real examples for my work was like a very skilled colleague of mine was doing a calculation in Excel and was just refreshing the sheet to get a new result and was taking a while and in the meantime, I created a sort of equivalent calculation and R and I had the result before he did from starting from scratch when he was just refreshing calculation and that's the situations like these data where people see oh they could be more effective if they transition their work. I hope this also covers no else questions as well. So we have a zero inbox of questions, but we had many questions which was very, very happy to attempt to answer. We are eight nine minutes over time but that's fine because we, we thought we might be and we will be happy to stay few few minutes longer if there is any, any question or bill has come up with a question. And so we will, we will answer this and maybe I would suggest that we close afterwards if there is no other question coming up before we answer this. How do you manage analyst access to packages and any limitation which package is allowed. It's very excellent topic I'll prefer Benedict to give an answer on that. Mine might be with a lot of emotion so. And I guess it did different angles to this question as well I think one typical angle is the one of since let's say the packages may just be a different person writing some code how do you really know it's doing what it's supposed to be doing that there's no error or no numbers that you get out of it. And I think that's probably more challenging. If you go into the areas that are not as well supported by the community so essentially you have some very expert small niche area that I may not be as much of a community that sort of peer reviews these open source packages and build some confidence over time that they're doing what they're supposed to be doing. And I think, yeah, that's, that's more challenging and then I guess depends on you on what kind of trust and responsibility and testing you have done with the package in order to verify yourself whether you trusted or not. And it's results. I think more broadly speaking they are thankfully many very well used and. Many packages again receive a lot of feedback and you on GitHub and you can see the issues, and you know that they are used by millions people over the world, I mean of people over the world so you would actually be able to see that they are widely used and you can also see the source code and probably trust the morse and some proprietary software by some company that says that they have checked it but you don't really know how well they have checked it or whether they actually did. So I guess that's one angle, the different angle maybe from the T security perspective again. That can can also be challenging depending on your view so clearly with with a commercial use or three you need to make sure that we don't violate any software patterns so we don't make any use of software against its licensing agreement, then I think what we also often employ in our context is some sort of automatic scanning for right it licenses and for potential vulnerabilities and software. However, I think we also have a relatively open approach so why we have to sort of created or more well managed set of packages. He also allow people probably speaking in some environments allow people to also, I guess, go out and use the packages that the needs on their own responsibility. Do you want to add anything to that. Just a point of the open source solutions are used by all the companies and by us on a daily basis and we need to be able to find a way to to to utilize that otherwise we cannot operate so companies have come up with solutions of scanning is one that you mentioned as well. We have a quite a strong and I would say robust digital governance framework within Swiss three quite often what the challenges is that although these frameworks have been created with the professional Infotech community in mind actually is there are users sitting usually on the business front might not be able to access some of these tools or might not be able to have a guidance written in a way that can be adopted by them so one of the challenges that we have faith in the communities in the community and we're trying to address is how we can bring the it world and the business world closer together. Instead of operating to binary worlds, where we are divided by Excel, we actually can coordinate the collaborate and some of the examples we'll see next week with this web API is where the domain expert can create a model and then pass it on to an IT professional for a data and system integration this go into into solving those. So I believe this interactions and the sections are things that we cannot value by working together. Not sure if we answer your question but or anyone else, but feel free to write either to benefit from myself directly or find us on LinkedIn or communicate with the app consortium. Hopefully to see you again, again next week. It has been also it will be quite helpful if you provide some of the feedback that it will be requested for you because it will help us shape the webinars and improve the offering. I would then suggest that we bring the meeting to a close now and I would like to thank you for your participation and for your for for spending time with us today. Thank you very much from my side. Bye see you all next week.