If you've been living under a stone for the past 2 decades, you may have no idea what I'm talking about... This post is about the all powerful grid! Not like the Tron "Grid", or Connect 4, or even the Battleship. We're talking about that 17.2 billion empty cell blank-canvas per-sheet known as EXCEL.Let me set the stage:
I have 245,000+ lines of strings containing a name, address, voter number, date of birth, and did I mention blanks? I need to also mention that each line contains a different piece of that information, so this needs to be parsed to condense all necessary info to 1 string per person, rather than 5 or 7 or 8 or 9 lines per person. You may be asking yourself did he pull the state voter registration information off the internet? Yes I pulled the voter registration information of the interwebs for an advertising campaign where we hope to target 65+ year olds who live in our 3 surrounding zip codes. More on this later...
So what do you do when you have nearly a 1/4 mil lines of data that is valuable in a processed form? Fire up Excel. Now that you mention it, Excel has been inching its way into my work everyday for the past few years as metrics, calculations, and DATA management requirements. I've used it to store, convert, and organize lists of all kinds. The program has permitted me to create simulations based on variables. I'm even using the program to develop a Simpleology clone. Excel has pretty much pushed me to my limits of understanding how to manipulate data within the grid space.Now on the surface, I have reached a ceiling within my abilities within Excel, and it has me feeling anxious. This feeling comes primarily from Visual Basic programming! Now I have learned several languages (programming languages), yet I have not seemed to devote time to this powerful flavor.
SO, what about the story, you ask? The end result: 9853 individuals were identified as 65+ years old, living within our 3 surrounding zip codes, and their last "active" address on file registered with the state! Cost savings: $16,575 in consulting charges. GOLD!
Excel finished the job without Visual Basic programming, but I did dip into Python for the parsing of the data. Could Excel have done the whole task alone without 3rd party tools... yes, at least I believe it can. But due to my time constraints, I called on the experience I had... Excel is a gold mine, and Python is my rail and cart system!What about you? Have you found limitations within Excel that require 3rd party support? Do you have a tool that has saved you when working on automation/data mining?
haha - I use Excel and its bastard second-cousin Google spreadsheets all the time!
ReplyDeleteI find that I create lots of workflow and tracking in excel - similar to how you mentioned a simpleology clone - but more Kanban styled. The only time I can remember needing Visual Basic was when I needed a way to quickly migrate data from my accounting system to my tax accountant's preferred PDF worksheets.
Using some Adobe Acrobat magic (I literally had to buy the software) and a quick $5 fiverr gig I was able to get a template Excel doc that I could copy and paste a month's worth of financial data push a button and automatically fill out my accountant's PDFs. Its pretty awesome.
I've found that I've spent a lot of time finding ways to automate excel spreadsheets, which in the moment has always seemed like a waste of time - but in the long run has served me well in understanding the full capability when I need to do something abnormal with my data.
You truly haven't 'lived' (in the nerdy sense of the word) until you've had a souped up excel worksheet almost take down your machine (mind you - it wasn't the most powerful piece of hardware). Haha frustrating, yet totally radical. Take particular worksheet(s?) was for tracking options trading - and probably way overkill at that.
Excel and its descendants are definitely some of a digital entrepreneur's best friends.
Stay thoughtful.
DeleteHey Eric, I haven't used it. I read a few articles on it and a few videos, it looks pretty solid. Good find!
ReplyDelete