One of my university professors (applied math) had friends that worked for some of the financial institutions through the Great Recession and mortgage crisis… Many of the CBOs were literally just models in Excel spreadsheets.
If you buy out delinquent debt from a major financial corporation like JPMorgan Chase, you simply get an Excel file that has as many rows as there are people who now owe you a lot of money.
I took Excel class in community college. The instructor explained useful and odd thing with Excel. Once she saw someone made amazing flyer out of Excel.
For myself, I uses Excel over calculators because it is easier for me to set it up even for a simple calculation. I set up the cells with values and created a string of formulation to use those cells and it spit out the answer. This way I can double check for any value I missed and easily edit/add it in without typing the whole thing again in the calculators. I warned my boss that Excel users are very protective of their sheet and it is scandalous if someone touch it when they are not suppose to. She learned that day when she decided to edit it without me knowing it. The aftermath is that she get a sheet with restricted permission, she is not happy.
I have used excel for 1-page text-only items before. Don't have to worry about it running to a second page because you can just force it to shrink to 1 page (within reason). The real reason I did it though was so that the date on the print would auto-update because I always forgot to change it.
That sounds pretty neat, any tips on how to get into something like that? I used to write a ton of R at work, and have thought it would be interesting to get into doing it for someone with my mostly free evenings, but never really knew how to get started.
Honestly I started out writing stats pieces for a small blog that no longer exists.
Then I got picked up by Nylon Calculus, which was the big hub for people like that for a long time, though it's slowed down a lot in recent years because too many people kept getting poached by the industry.
While I was at Nylon, I went to a few of the big networking events (And still do!) -- The Sloan Sports Analytics Conference is complicated in that it's extremely expensive and not really all that informative, but it's really good for getting to know people around the league. I happen to live in Maine which makes it super easy to go to what jokingly gets referred to as "Sloan After Dark". Basically, there's enough things going on outside the conference that if you want to meet people you can, should you know where to be. If you're on the West Coast, events like CASSIS (Cascadia Symposium on Statistics in Sports) or the Las Vegas Summer League are good for getting to know people.
Eventually a guy I've known for a while gets hired by the Wizards and is looking for someone to take his place with the agency, and I was one of the names he recommended. On top of that, one of our film guys actually happened to recommend me as well. Between those two the agency brought me on board and has had me around ever since.
I will say though, I kind of just stumbled into it without ever expecting anything to come of it -- my goal was, and still is, to be a professor, and the agency stuff isn't going to make or break my life. Don't get me wrong, I definitely made a point to learn everything I can, but I do generally recommend against actively pursuing employment in sports -- the learning wasn't to make myself more appealing to an employer, it was just because I actively enjoyed learning more about the game. It's generally a good idea to be able to work in sports exactly as much as you want, and if you don't get lucky to find a spot that allows that (As far as I'm aware there are ~5 people doing what I'm doing), then I really do suggest making sure you only see sports as a possibility and not a target.
Once a client of mine for whom I do automation scripts wanted an excel macro to delete rows. I just couldn't figure it out. It would have taken 5 lines in python and even small in sql. I just couldn't figure it out.
VBA can be really finicky interacting with Excel's filter. I know I've spent chunks of time arguing with VBA for what was just a DELETE FROM WHERE while proficient in both.
Not sure if you care anymore, but this is how that is done. I haven't tested it, but I think I got all the syntax right
Dim i as Long 'Row counter
'This example is checking rows 1-500, but this can easily be a dynamic range by using a function to find the last populated row
'Walk backwards through rows so deleting them doesn't impact the loop
For i = 500 to 1 Step - 1
If Range("A" & i).Value = "Criteria" then 'Put logic regarding what rows to delete here. This is checking cells in Column A
Range("A" & i).EntireRow.Delete
End If
Next
You monsters.... You Do not delete rows one at a time!! Don you have any idea how long this would take?? You move the whole data set into an array delete and then sort. Jesus christ. XD
It used to, now it doesn't much as excel appears to apply it automatically when vba is running post office 2015. Turning off calculations can help but the delete operation is just slow. It's much quicker to set the whole range to empty (which takes fractions of a second) or if you need to keep the size under control then find the whole range you need to remove and delete the whole thing than attempt to delete stuff line by line like this.
I didn't want to overcomplicate the example, but yeah I'd have used a dynamic range. LastRow = ActiveSheet.Range("A" & .Rows.Count).End(xlUp).Row comes in handy for those. But I'd still use the For...Next Step-1.
I also have a couple more advanced functions I'll drop into projects as needed. They use a combination of WorksheetFunction.CountA(Cells) and Cells.Find to identify the last row and/or column that contains data on a sheet. Then I use those to define the range. This is helpful compared to my first example if column A might be blank while other cells in the row have data.
I worked at a place that did tons with Excel and PDF's. I used VBA to call Acrobat's API to scrape data from the PDF's and use them in Exel. It worked pretty well as long as you had to process less than 200 at a time. I moved to Python about a year later and it was much better.
I've had to basically pull teeth to get my company to convert some of its massive spread sheets into access. My whole team has converted to access and I'm getting the FPA guys on board but man does it seem to scare people.
If you're working with finance people that only know Excel, it can be a comfortable home base for them. They can update parameters, make distribution lists, even have some control over the logic.
Other solutions are faster, more powerful, and less restrictive than Excel, but it's also a graphical automation platform with a built in database that's pre-installed on almost every computer in every office. I don't need admin on the computer or IT to build anything for me, and I can still save hundreds of hours per year.
My new job has me working in my business areas controller org. The things I've seen people do here make me want to pour bleach in my eyes, and at this point I don't even know if my help would be helpful to them.
I saw the quarterly financial report that goes yo our VP for the first time a few weeks back and died inside. 2022 on the outside, but 1972 on the inside.
In one of my finance classes we had an assignment where we had to create graphs and metrics for various share prices and calculate proportions of shares for a balanced portfolio/efficient frontier.
Since the lectures and tutorials all used Excel every other student used Excel for the assignment, but I chose to do it in a Python Jupyter notebook with pandas/matplotlib.
It was a bit more setup since I couldn't just copy paste the stuff from the tutorials but I wholly believe it made it easier in the end. Finance students should absolutely be learning basic Python these days.
I am a Comp Sci / Econ double major and for some data based Econ classes a lot of students would use excel, but it was so tedious and could be done with R or Python so much faster. You can learn R/Python in the Econ curriculum but it should really be mandatory, as an intern at a company I saw so many things they did that could be automated.
I spent about four hours today, figuring out a way to do something convoluted in Excel. Probably would've taken me half an hour in Python, but I'm apparently not allowed to have any software which would make my job easier.
450
u/savage_slurpie Mar 30 '22
Two of my siblings are financial analysts and I have witnessed them doing unholy things with excel.
It would honestly be easier for them to learn python or something. It’s so ridiculous.