Collaborate on Bill for Excel for Financial Services with Ease Using airSlate SignNow

Bill for excel for Financial Services

hello and welcome to our webinar brought to you by Jed brains I'm Elena gar a product marketing manager for datalore and I'll be your host for today's session today's discussion is especially for those teams who are still using Excel for financial data analysis and Reporting this often results in mult multiple complex and manual analytical processes woven together into a spider web of files spreadsheets and databases frequently this tangle is nearly impossible to unwind but don't fret we have two amazing guests from venerable today who will share their journey of moving Excel Centric analytics to Streamlight approaches with SQL bison and Jupiter notebooks our guests will unfold the story from two angles Alexandria represent presenting investment risk analytics Alexandria say hi to everyone hi glad to be here and Stephen encapsulating the perspective of Enterprise architecture hello everybody after their successful talk at ODC Eastern Boston Stephen and Alexandria are here today to share their Journey with all of us watching as we allow a few more minutes for people to join let's hear from from Steve and Alexandria about their experience at the audc event could you tell us about your favorite part of it yeah I guess I I'll start here so I I I want to start by saying thank you uh so much for the opportunity to come out and speak um this was a unexpected honor for us um from my perspective I think probably the the thing that was most a little unnerving was going to an AI conference and then talking about something that really didn't have a lot to do with AI um we were talking about Excel and using duper notebooks which I guess is a pretty good precursor to getting into AI analytics but it really was a little bit of skew of the main topic and when we got there there was a lot of fear from us that we would actually get an empty stadium with nobody there and I have to say I was really surprised when the people just started flowing into that room and coming in and standing in the back and it was really outstanding opportunity and a great experience for for both of us I think Alexandria was your thoughts on that yeah I thought it was wonderful not only were people really engaged during the talk but they stayed an hour after just to talk with us oneon-one and then a question I've had people reach out to me on LinkedIn saying that they have the same problems at their company they're hoping to move away from Excel themselves and ask for some advice so it's been a really wonderful opportunity wonderful I'm yeah I just wanted to say I'm so happy for you guys that you've had such an experience yeah so uh without further Ado the stage is yours and let's start this webinar welcome while some Industries have made huge leaps towards automating most of their work the insurance industry still has a long way to go our processes are stuck in Excel spreadsheets we act as though our file directory is our database for some of us our file directory is the closest thing we have to a database we rely on V lookups and spend countless hours manually updating our web spreadsheets at venerable we wanted to focus our efforts on automating these manual reoccurring processes and hope our story will Empower you to move away from Excel towards automation say goodbye to those mly processes you dread the ones that take up all your time and are so mundane you question why you needed a degree for this job before we get started let's talk more about who we are and what we do we're an insurance company that focuses on reinsuring variable annuities we pride ourselves on our decades management experience our comprehensive head strategy and our ability to manage our liquidity needs with our conservative Investment Portfolio to be successful in our industry we have to focus on how we can add value and become more efficient one of the major Pro problems facing our Effectiveness was this interrelated web of spreadsheets most of our spreadsheets get their data from other spreadsheets and they get their data from other spreadsheets and so on we also send these spread spreadsheets out were they're used as inputs in other people's processes as a standalone process this would be fine but now imagine one of these spreadsheets gets in air and you didn't realize so three spreads later now you have to go back fix that sheet and all the other sheets that relied on it and hopefully you didn't send it out internally or even worse externally and I hope you didn't miss any of your very tight deadlines so I got involved as an architect at venerable um when we started seeing some of the inefficiencies that Alexander is talking about and um obviously from being a very um compliance heavy industry there's a lot of concerns about the last point that she made about the spreadsheets being extended externally um and just looking at the problem we started understanding a little bit more about our partners and what we realized was that they're changing um it's really no longer you know just a classic business person that is sitting at a desk and and and working in um what they are becoming are what we're calling citizen developers their quantitative analysis their data scientists their folks that really are developers in the business and and that took a lot of effort on our part to kind of change our mindset and look at how do we help these folks um do better job at automating and doing their job and we started looking at and doing some analysis realize that there's there's these basic buckets of needs they want to automate right they're looking to um reduce errors increase the proficiency of the processes they need to do analysis right that's their main job is to find and and adjust risk based on what they're learning from the data they need autonomy right the reason why they're kind of Citizen developers because they've developed their own skill sets kind of working around classic it organizations um everything's about data I mean we all know that it's all about the data it's all about how to find it making sure it's the right data making sure it's version correctly they trust the data sources um it's also about collaboration Alexander Works in a very small team and they work very closely together on their problems they work on the analysis together they're constantly working back and forth and so we they needed a solution that allows them to do that and honestly Excel is not a good tool for that um and although they may not admit it they need some guard rails we are compliance and governance heavy company um because we have to be there's definitely regulations around um variable annuities that we have to abide by and so we want to make sure that they're doing work in a very safe way um and we want to make sure there's safeguards and we also want to help them out too we want to make sure that they're not losing code because of mistakes um you know they delete something and by mistake so these are the needs that have emerged um and then so we did a look at what would what what problems they're facing what what are the characteristics of the group that we're working on and then we started looking at um what are the the Continuum of tools that they have to work with right so on the left of the screen you see basically self-service right and and that really is comprised of excel access databases and way off to the right are the formal IT projects because as a good organization we are doing it projects for them but those are really formal projects with um the full Gambit of sdlc that a good development team has to have um and we've built things like custom plugins for Excel we've built complete custom applications to help in certain areas but what they're looking for is the left side right they're looking for the to move from a manual processes that are partially automated using some tooling some scripts and stuff like that to closer to the right but without the need for formal IT projects and I think what fills that space after looking at all the options in the world was Jupiter notebooks um and and and honestly I'm an architect I'm not a data scientist and Alexandria has been critical are helping us understand what a notebook is and so I'm gonna let her talk to to this particular tool yeah chuper notebooks is this place that's a collection of code documentation data and visualizations on one environment place where we can have the ability to explore code and make visualizations with our data we can test and explain code in these individual blocks just great to refer back and and what also allows us to store our variables in the environment so you don't have to rerun your entire script every time you just want to look at one um of your data variables but we also wanted something more like Steve was saying we wanted to be able to collaborate with our team in real time we wanted the ability to be send reports people to explore the data but don't have to code to do that and we wanted version back control so let's let's look at the the needs again okay just as a summarization right so based on what we've learned from our discussions and looking at the work that we're doing in place we did determine that a Jupiter notebook is obviously a very important piece like it's going to help solve this problem but it needed to be cloud-based right because what we did not want to do is have to put something on everyone's desktop and suddenly they're asking for new laptops that have more and more RAM more and more um capacity they need to really kind of push the envelope and they just can't do it on the laptop so we need something in the cloud um we wanted to make sure they had an implementation of notebooks that would work and does everything that Alexandria needs um we needed support for versioning and Source control um we wanted to make sure that they could use all the math math related and data related libraries that are out there in the world the mat libes the pandas all these different things um and we wanted to make sure they also had the ability to write in libraries or languages excuse me that they were used to python are um you know WR native SQL for some of them and we also need to be reasonable in terms of cost right we we can't break the bank in order to put bring Jupiter notebooks to the company and so although this is sponsored by by jet brains um and I think you probably could tell we obviously went with data lore we did look at some other options we looked at sagemaker we looked at juper Labs themselves um and we looked at a couple other products that I I honestly don't remember there's a whole number of juper notebook implementations out there and we ended up choosing data lore obviously um and and we chose that because it's cloud-based it's very user friendly right so there is as much as I talk about citizen developers and writing code we still have a number of folks that are just learning that world and and really not as comfortable with it so having a tool that makes it a little easier for them to do the job um was very important to us and Juke um data LW really fit that bill has low code data Vis visualizations it has automated data quality metrics um it works well with SQL you just write a SQL statement and it'll pop the data into another cell and give you a lot of information um under the scenes it's really a great product when it comes to all this um but I think probably the thing that we really liked was how collaborative it was um two or more people can actually get into the product and work together and you'll see them interact with each other sell byell and we thought that was the kind of really really important thing for us to work on go ahead so obviously old company insurance company we're very formal we had to go through a nice little PC and um we had to you know do a formal thing we had do a architecture revieww and so we put we got together we came up with a use case uh came up with a pilot Alexandri and her team were one of our first folks to actually get a chance to play with the tool and work with it um and they came up with a very very interesting use case that actually knocked our socks off and I'm gonna let Alexandria talk to the use case because it really is amazing thank you Steve one of our processes required us to review our cash flow data over a 30-year period and our old process was messy to stay the least it relied on this web of spreadsheet and each time we ran it it would create hundreds of plots for an entity each entity run would take about an hour we had eight entities so we were doing this eight times monthly and 16 times quarterly to compare our quarter end cash flows and our monthly cash flows this is more plots than anyone could review and way more manual work than anyone needs to do uh so we wanted a new solution with data lower I was able to use their sfab to make sure all of the data was loaded correctly and find any errors before I got too deep into my coding processes I was able to code it in Python using packages like pandas and Matt plot lib and now I could pull this data straight from our database too with and use SQL as well in com combination with either uh python or R which was great I was also able to document my code and make a read me explaining my inputs outputs how to run and how to update my code I also always include what can be improved for the next person to come in we also went ahead and reduce those plots hundreds of plots there's too much for one person to look at so we focus on four metrics that we found the most value but we didn't want to lose the capability to look into these other metrics in case we did find something a little concerning and wanted to dig in deeper luckily with data lore we were able to make this interactive report that allowed me to have all the same capabilities as our last Model but way more easier and now we could just look at one pot as at a time as we needed it the impact was a bit crazy I was able to reduce our runtime from 8 hours to just a couple minutes I stopped stopped this spreadsheet of spider we spreadsheets I was able to have back documentation and now was able to reduce the load of manual work needed to run it and maintain it this is just one of my timec consuming processes now imagine if I did this for all my processes imagine if every Analyst at my company did this we can improve our data quality and save masses amounts of time so we can spend more time doing those things we got the degree for all right so obviously the PC went really well a lot of people were impressed with this this this great Improvement of efficiency and automation these guys came up with on just one of the use cases that they have to face every day um and that's one small team within the organization um there's probably a 100 plus folks that could take advantage of this over time but we know how embedded Excel is in the life of financial companies and frankly just about everywhere right it's a very uous tool everyone's using it and to be honest with you we're not attempting to replace excel in in its totality we're just looking to use the right tool for the right job and make sure there's a good tool in the tool chain that Alexandra and folks like her have the ability to use to solve the problem in a in a much better way um so we're really not trying to move everyone away from it we know that friction is very normal in this scenario we know that we're not going to try to force everyone to do it and and so we had to come up with some some strategies that we could use to help the folks adopt across the company um we wanted to make sure that we were using proxies we wanted to hear Alexandra's Solutions spread through the company almost organically we didn't want to have it coming down and pushing it down their throats because everybody uses Excel and a lot of people love Excel and we're not saying it's not a great tool we're just saying that for certain situations another tool might be better so what we did was we looked at what we're calling spiral adoption so we wanted to try to minimize the in the the disruption to our company we wanted to do it in a small way and slowly grow it as I said organically so we're going to start with some small teams like um Alexandri we're going to let them experience the tool frankly we're going to let them kick the tires and make sure they find the problems first right so that we can keep it contained to a small group and then we're going to move to the next group and spread it and then the next group and the next group and then over time we're going to build a community of folks that are using this tool um within their tool chain um a couple other things that we we kind of focusing in on um the data itself is very important though they have a trustworthy source so we at venerable have created a Enterprise data catalog um that they could use to find the trusted data that they know is the right version coming from the right Source from the right from the right department and when they're done and when they have that last visualization they have that last data set they need to pass on to a different team they can use it in a in a place that they know is going to be the right source and vice versa um while we want it to grow organically we also recognize that if we don't put some structure around it that it's going to become a chaos so what we've decided to do is to do some internal branding and come back to it on a regular basis to remind folks why we this tool exists why it's in the tool chain and and to help it you know kind of grow the community we also understand that new product that you drop into a company you got to give it the right level of of of love and care from the it group when a when a a when a a business um partner gets into a new tool starts using it and they have some issue if there's no one there for them to go to to say hey I'm having a problem please help me it's going to fail right they're going to get a bad impression and it's not going to work so we want to make sure that we've set in place a high touch it support um plan so we have like a teams and um chat room they can reach to we have dedicated resources that are willing to help and you know obviously we're here to help as well to try to promote it make sure that they don't have a bad experience um and then last but not least is that which does not change dieses and continuous Improvement is really important we want to make sure this gets better and better as Alexander her team are learning tricks and tips they're publishing that same with other teams are using it we're getting support from um data la and Jet brains in terms of new features new functionality that makes it better and better um and we're passing that on to the business so we can grow the solution and make sure that everyone has a great experience with this tool go ahead so thank you uh we definitely appreciate the the chance to talk to everybody um I hope that this is helpful um data lore is an amazing tool we've obviously figured it out we found out that it's a great great product um and uh I'm going to open up for questions perfect thank you very much step and Alexandria for sharing your story like I'm hearing the story I don't know for like for the fifth time but still it's really inspires me how big of an impact you can have with this like manual process when you automate it so we do have a few questions already in the chats and everyone like this is the time for your questions you get a chance to get your question answered live by stepen Alexander Andrea so the first question is are you going to demo datal lore and I guess unfortunately this question is for me uh because the guys are not going to do a live demonstration of datalore today but you're more than welcome to check our like quick overview video of dat lore on YouTube and try it out yourself you can even try it out without having to it on your infrastructure online on the jetbrains data lore website so yeah I guess I've answered this question and let's move on to the next one um I guess this question is for you Alexandria do you plan to keep certain analytics in Excel not for the long-term but we do love it for quick features there's also some things that um cell just seems to do a bit better than um I mean python has a good B but we were trying to like automate a lot of our stuff so I've been using SQL SQL isn't the great with something like um Market weighted averages has been a way more complicated query versus if I pop this in Excel I can have an answer within two seconds um so I we really want it for a short term and be able if we need something on the fly but as a long-term solution we're really trying to get out of excel makes sense absolutely none of the tools is just a one siiz fits-all solution and Excel I guess like the whole financial industry relies on Excel as Steve has mentioned and uh it would have been a very quick fix for um a tool that has been there for decades I guess the next question is for you Steve so what resources did you need to start the PC I guess the question is mostly about like the Buy in from the management and also if you could cover some of the like engineering resources you had to throw into the project that would be awesome okay so um from the business side we needed to find uh um folks that were interested and that were willing to make a change right so a lot of times you get the the folks are dragging their feet they're not very interested um so we reached out we found some Partners um within the business that were experiencing a lot of pain like so part of our job is to try to squash the pain so finding the pain is part of the job we found this piece of pain that we knew was out there we went and talked to the business partners and they were like oh wow you could think of a way to make this better well we're open to it let's let's try it and so getting them on board um was kind of the first step from a technical perspective um we looked at it from a number of different angles right so we tried it out um at the time the SAS implementation wasn't wasn't there when we started working with it and so my boss and I sat down in an afternoon Dro pulled down the the Enterprise um Docker image onto an ec2 and had it up and running in like two hours and so we were like oh my God this is so easy um let's then we basically transferred that over to our infrastructure team um that does a lot of our devops work and does a lot of the setups and then they took it kind of from there they gave us a more robust environment they worked on moving it over to kubernetes um to to give it a wider footprint and make it more robust um but from a technical perspective um you know there wasn't a lot of like we didn't have to like set up the the image on in um in the notebook like we didn't have to worry about what libraries that were necessary for alexand to work a lot of that is already built in there's a whole set of like kind of stock libraries that are already invol already in the image um and adding additional images is sorry additional libraries is no different than if you're in um pie charm or visual Visual Studio or vs code you just simply add it to your environment now you can start using it um the difference is is that it's not on your desktop it's up in the cloud it makes it easier you know and and repeatable um so yeah I hope that answered your question yes thanks Steve and we have the next question from Dan uh can you discuss the challenges faced by non-developers using Excel as they used tools like data lore and utilize best practices from sdlc moving towards the right side of the earlier chart I think this question is for Alexandria yeah we were actually in a weird environment though we're not developers a lot of us did know code we either knew r or python we just didn't have any other tools than a vs code to use it and at that point we hadn't had our database set up yet so it was just a standalone to that like if we wanted to use we had to uplate our Excel sheet so that didn't really get us away from it um but I will say datalore is very user friendly like they come up with demos for you there's also sometimes these pre-codes so like you can go to their zap section when you load a data frame in and you can view the data there you can even have pre-made plots that are really interactive and they have drop down so you can do different metrics and if you have a plot you like you can export the code so it is very much a you very user friendly if you didn't know the coding background yet if you don't mind can I can I add a little bit because the end of that question was about thec so one of the things that was I think probably the most difficult for us to kind of accept was that they aren't developers like they don't need to follow our formal sdlc that we as application developers have historically done um we want them to embrace some of the concept Version Control making sure they do proper testing um these kinds of things but we didn't have any intent on having these C developers actually develop an stlc and their their entire SCC environments their test or Dev and their production is actually our production so it's up to them to decide how they want to organize their work and and and build their Solutions in a in a very successful way now we of course have had you know 20 30 years of experience developing application codes so we're hoping we could help influence some of the work they're doing but we're not trying to implement an sdlc a formal sdlc into the world um they haven't been doing it with Excel for you know the same amount of time and they're not going to we just recognize you're not going to do it in the future um so I hope that it helps um answer the question yeah and I guess the next question Steve is also for you so can dat be ported to a client's VPS I guess could you talk a little bit more about your specific setup and then I can extend yeah yeah so so there's a couple different options with data lure um we're there's a community version um where somebody you could just get a license for one of your business persons and they're doing it at jet brains on their Cloud um another option that's available um is an Enterprise version which is what we did so we have it inside of our AWS Cloud word AWS shop um we've got it hosted up on our coperet um cluster um in the cloud um you you know we are responsible for the care and maintenance of that there's a new patch there's a new hot fix new version we have to it run it make sure test it you know kind of deploy it um it's actually pretty easy um it's very different than the other jet brain products that we've used like data grip and uh pie charm where it's not installed on on the desktop we don't have to create image that we put up into our company portal so it's a little bit different from that perspective um I understand now there's a there's a like an Enterprise SAS oper option um that we're actually really interested in looking at so I don't know alen you want to talk more about I can add as well that theore could work even like in completely air gapped environments so you can spin it up like you can spin up the daylor server wherever you have kubernetes or Docker so um I guess the answer to your question is yes and there are various setups available out there okay and moving on to the next question we have did you need to do upskilling of excel analysts to allow them to start using python Alexandria I guess this question uh for your as well um luckily we did not I it's harder I think for other teams like to adapt it and I have one uh my manager actually is very reluctant to do python luckily he he knows how to code an r and I just been using r instead and we can both see his code and um understand r as well um and I had another cooworker who had previous coding experience but had not coded in Python in a while but he was also able to make it back up no problem other people are still a little reluctant to move to excel from other teams just because they haven't had that introduction to python quite yet but I very much hope that a to like data low could be again this good bridge for them and also uh yes to go ahead please you mind have I this one here I like to point to too because you know when we looked at the tool chain that were available we recognized that on one side of the coin you've got the basically the the folks that just run the engine they're running the Excel spreadsheets they're not actually developing new analytics with Excel or they're doing very little of that and then we have the folks like alexandia that are kind of on on the further side on the right side that are doing a lot more custom math we're doing a lot more analysis and so this tool kind of fits them more than the lad and so we're not trying to make it so somebody that's basically running the engine would suddenly have to switch over write python that's that's not reasonable um but we want to make it so that the folks that do and are have a tool that's better than trying to struggle with Excel to do something that fits better in in a j I would also have to add something here because I think that the potential of generative AI I'm sorry to use this buzz word but I do believe that like in terms of democratizing data science for those known developers generative AI would be fundamental and in D team uh we have recently released the AI assistant which works also like in our online versions and in on premises versions too and basically when a non-developer comes to a notebook they can have all of the data preon configured like the data connections preconfigured by their team members then they can use AI to write SQL they can basically prompt AI to create a certain visualization with Pyon and what's more we have also recently added the so-called data lore autopilot feature where you can just specify the goal of your research and they I would suggest use the steps to complete your analysis and I think that that like this addition is instrumental into helping those people across a company who do not have software engineering background or who know very little python get started with data science or data analysis so that's something very important to add yep um and I think like to be respectful of our viewers and speakers time this will be our last question and we will take all the remaining questions offline and for those who are watching this webinar in the recording you more than welcome to leave your comments under the video we'll make sure to reply to all of them and I just wanted to say thank you to Steve and Alexandria for joining us and thank you everyone for watching this webinar and stay tuned for more events with Jed brains