How to Learn Financial Analysis in Python 10x Faster

Key Takeaways

hello all my name is krishak and welcome to my YouTube channel so guys in this specific video we are going to see that how you can learn and perform financial analysis using python with the help of Mito library now this Mito library is super cool Library which can actually help you to work with Tabet data and do some amazing analysis Jacob from the Mito team has actually created this specific video so please make sure that you watch this video till the end and try to the power of moo sheet in your use cases and in your problem statements moo is a spreadsheet that generates python code for every edit that you make today let's pretend that we're a financial analyst at Vanguard we're going to build a report that looks at fun performance for the year 2022 to do so I'm going to import some data first we're going to look at the fund info data I'm importing just a CSV file to get started with this has the fund name the portfolio manager and some additional data about each fund notice that Mido has started to generate the equivalent code for every edit that we make inside of the spreadsheet that means as a Excel first analyst I can take those skills and turn them into python code just using M the next thing I'm going to do is import some additional data this time I want my fun performance data this data lives in a database and instead of having to write the SQL query to do it myself I'm going to rely on Leo to do that for me I'm just going to sign in with my username and password and tell Mido which year I want to look at data for when I click the import data button Mido is going to execute that SQL query for me and return the data into my spreadsheet there's a bunch of different ways of getting data into Mido so far we've looked at cssv files and importing data from a database but if you look at the Mito documentation you can also import things like Excel files as well as reading data from remote file systems importing data frames and importing data from other other dat data sources that we haven't yet talked about now that I have this data I can see that it has the date the fund and the month over month of returns we're interested in building a report that looks at for each fund and each fund manager what is the month over month return earned for each month so to do so I'm going to combine these two data sets I'm interested in pulling over this portfolio manager column I'm going to add a new column just like I would in Excel and call it fund manager and then I'm going to use a v lookup for formula again this is exactly how I would do this in Excel I'm going to look at these two columns and perform that V lookup again Mido is generating the equivalent python code for every edit so I can just worry about building my analysis like I would in Excel and let moido handle all of the pyth thinking for me one of the things that I notice is that some of these funds for example this one here has multiple fund managers for this report we want to see each individual fun manager on a new row so to do that I'm going to use a custom edit I've built this custom function called separate row and all I have to do is to tell my where how I want to separate this row we want to separate the fun manager and then I want to separate on the comma when I click separate row on eliminer it's going to break each one of those fields into a new row this is an example of functionality that doesn't natively exist inside of excel but it can exist inside of Mido python is a really powerful and flexible programming language that allows you to express complex logic pretty simply and Mido is an interface on top of that python code that that makes it even easier to work with it the next thing that we're going to do is create a pivot table to do so I'm going to click this pivot button and what we want to look at is for each fund and for each fund manager we want to see the month over month return earns but we don't want to just see the sum of the yearly month over month returns instead we want to look look at the month over Monon returns for each year inside of the month so what we see now is for January 2022 the month over Monon returns for February over 2022 the month over month returns all the way for the full year this is exactly the report that my manager wanted me to show them so the next thing I want to do is clean up this report a little bit to make it easy to show my manager to do so I'm going to do a couple things first I'm going to apply some conditional formatting I'm going to add a conditional formatting rule to all of the month over Monon return columns and let's say if the return is greater than zero then we want to highlight this in a nice green color and and similarly if the return is less than zero but that's highlighted it in a red to make a good stand out that's a lot better I feel like if my manager is going to be able to understand that but my manager likes things likes to see things in Excel so showing them this view is probably not going to cut it to do so I'm going to export this as a Excel file let's call this Vanguard performance I want it as an Excel file and I want to export it with all of this conditional form Ting now when I click the generate export code button it's going to add a new line of code to the bottom of my script that takes this ex this file that we've created and generates an Excel file when I run that code it's going to create a new file called the Vanguard performance xlsx file let me open that and show you what that looks like download it I have the exact same view that I had in my M sheet in Excel this is ready to send to my manager but before I send this to my manager I think they'll also going to want to see some of this data represented as a graph I'm going to flip back to my my DOA sheet and create a graph to send to my manager along with the Excel file the graph that I want to show my manager is the average month of over month performance for each fund to create that information I'm going to scroll all the way to the right of my data set click the add column button and create the average month over month I'm going to use the average formula and just like I would in Excel I'm going to select all of the columns that I want to include in my average this has calculated the average for the entire data frame all 98 rows so so far we've looked at using a v lookup an average formula but moo supports a huge variety of formulas if you look at the moo documentation and look at our formula reference you can see all about a 100 formulas that M supports it's all of the most common data cleaning uh string parsing arithmetic and date operating formulas now that we've created the average month- over month column the last step that we need to do before building the graph is actually D duplicate this data because we split the fund managers into multiple rows if if if fund had multiple fund managers for example this one here we're going to see two entries for them and that's going to throw off our graph to D duplicate the data I'm going to click the data button I'm going to click remove dup duplicates and I'm going to select how do I want to duplicate my data in this case any time that the fund exists multiple times we're just going to drop the first one D duplicating the data as easy as that we've removed 12 duplicate rows from our data frame now that I've prepped my data the last step is to actually build the graph to do so I'm going to click on the fund and the average month over month return column and select graph M's going to start to build the graph for me the last step that I want to do before calling this done is actually clean up the graph a little bit let's close that since we already have our data and let's maybe rename this let's call this average month over month return since there's a lot going on in this graph let's also maybe clean it up a little bit I don't think we you need to display the graph um grid lines instead of calling this fun let's call this Vanguard fund perfect so this graph looks really good to me I think my manager is going to be happy with it because plotly graphs like the one here are default interactive I want to preserve this interactivity when I share it with my manager to do so I'm going to export this not as just a PNG file but as an HTML file when I copy the code and I scroll down to paste it here I see that the output is creating an HTML file so let me run the code and look at the HTML file perfect so this is what the graph looks like it's an HTML file that I can embed it in an email and my manager can scroll over the different data points and see the average month over month return I'm going to save this I'm going to put it in an email and send it it to my manager let's do that now hey Nate just finished up the month over month report let me know if you want to see any changes and then I'm just going to attach my files want the graph Z and the Vanguard from performs this is what the resulting email looks like it has the full Vanguard Fund performance Excel file and it has the graph that we generated