Just lately, I collaborated with two companies, each searching for the same, easy answer:
To automate their month-to-month and quarterly reporting processes and current the information on visually interesting dashboards for his or her shoppers.
Each companies had been grappling with related knowledge challenges, which led me to suppose these points are doubtless frequent throughout many companies. This prompted me to jot down this weblog, aiming to share helpful insights and provide sensible options.
1. The companies had been spending far too lengthy compiling studies into Excel
Reporting typically took days to finish. One company needed to collect studies from a number of world markets, whereas the opposite relied on a number of employees members throughout the enterprise to replace and ship the information from their techniques. If somebody was on annual depart, that knowledge level was merely marked as ‘TBC’ within the studies.
2. Making an attempt any deeper-level evaluation proved to be difficult
Points with Excel’s stability & scalability:
Each companies had been dealing with massive volumes of information, and, as many people know all too effectively, Excel tends to battle and crash beneath these workloads. This frequent freezing and crashing in Excel, notably throughout pivot creation, made deeper evaluation very cumbersome. The groups typically needed to drive Excel to restart, typically risking the lack of their work.
The constraints of Excel’s visualisations
Excel affords a comparatively restricted vary of visualisations, making it tougher to current knowledge in various, insightful methods. Whereas easy visuals are sometimes the only option for ultimate displays, the exploratory section calls for extra superior visuals to analyse knowledge from a number of views and uncover deeper insights.
Excel affords restricted interactivity between visualisations in comparison with extra superior instruments, which offer a extra seamless and dynamic expertise for knowledge exploration. As an example, in a instrument like Energy BI, you’ll be able to click on on a area inside one visible, and all associated visuals (comparable to gross sales tendencies, buyer demographics, or product classes) instantly replace to show solely the related knowledge for that choice. This degree of interactivity is invaluable for uncovering deeper insights and understanding the components behind modifications within the knowledge.
The significance of Deeper evaluation
Deeper evaluation is essential for making essentially the most impactful selections every month. It’s what separates an ordinary report that merely exhibits whether or not numbers are up or down month-over-month from a really distinctive one, the place you’ll be able to suggest proactive options, craft revolutionary methods, and uncover untapped alternatives. By investing time on this degree of study, you not solely handle rapid issues but in addition place your self as a key accomplice in your consumer’s long-term development.
3. Extreme time and sources spent on creating visually interesting graphs outdoors of Excel
As a result of Excel’s visualisations are inclined to look a bit, effectively, clunky, one of many companies outsourced the creation of polished, branded visuals to their designer every month. As with most design initiatives, this concerned plenty of back-and-forth discussions about how these new visuals ought to look.
4. Knowledge expertise gaps led to inaccurate reporting
The reporting was managed by somebody with out the mandatory expertise to completely perceive Excel’s quirks , and understandably so, because it wasn’t a part of their core function. Because of this, each companies unknowingly reported incorrect numbers. For instance, regardless that the Income column was set to ‘Foreign money,’ entries like ‘USD123’ and ‘ 123’ (with an area) had been excluded from the overall as a result of Excel didn’t acknowledge them as legitimate forex values. Whereas Excel does provide a Knowledge Validation function to limit entries to decimals or entire numbers, it have to be utilized manually, and lots of customers aren’t conscious of it. For my part, Excel ought to flag these discrepancies by default.
This instance is from simply one of many shoppers, as their case was extra complete:
Dropbox / Excel:
The company’s main Excel file, containing a number of tabs, was saved in Dropbox to permit world entry for group members.
2. Python in Deepnote:
That is the place I spent nearly all of my time, utilizing Python in a Deepnote pocket book to totally clear the information after which automate this course of each month. Under is a snapshot of a Deepnote Python pocket book. I’ve outlined within the cells the steps I took to tug, clear and push the information:
3. BigQuery
For each companies, I ensured that the cleaned knowledge was saved in a database whereas additionally pushing it again to an Excel file in Dropbox for many who wish to entry the information in Excel format. Storing the information in a database offers a number of key benefits, together with:
a. Safety: Superior options like user-based permissions, encryption, and audit trails guarantee delicate knowledge is protected and entry is tightly managed. Since Energy BI doesn’t permit for hiding delicate columns from sure customers, I created related views inside BigQuery to handle privateness, controlling which knowledge is uncovered on the dashboard degree.
b. Pace: Queries run shortly, even with a number of customers accessing the information concurrently by way of the dashboard.
c. Scalability: As the information grows, the database will deal with it seamlessly, avoiding the aforementioned points each companies skilled with Excel.
Large time financial savings
Their month-to-month and quarterly studies now refresh mechanically in minutes, eliminating the effort and time as soon as spent manually compiling knowledge. Even when somebody is on annual depart, the method runs easily with out disruption. The groups are now not depending on my enter, making all the system totally self-sufficient🎉.
Very glad shoppers
Each companies are thrilled with the outcomes, utilizing phrases like ‘superb’ and ‘I’m obsessed’ to explain their shoppers’ new dashboards (sorry to toot my very own horn, however typically you’ve simply received to). Whereas I can’t share the precise dashboards, right here’s a mock-up that carefully resembles one among them:
Customers have been empowered to carry out deeper-level evaluation
The dashboards provide superior, related visualisations that allow deeper evaluation. Absolutely shareable throughout the group, they permit for extra detailed, sector- and team-specific insights, empowering everybody to make extra knowledgeable selections.
Knowledge is correct
Crucially, the numbers are actually correct, free from the quirks and limitations typically related to Excel.
No must outsource a designer or depend on third-party instruments
Beautiful, branded visualisations can now be created straight in PowerBI and simply embedded into PowerPoint, eliminating the necessity for designers or exterior visualisation instruments.
The companies are actually extra savvy about what’s potential with knowledge
As with all my shoppers, I took the time to coach them on the complete potential of Excel, Energy BI, and Python. By co-piloting with their groups, I helped shut the information expertise hole, highlighting Excel’s quirks whereas introducing the ability of Python and notebooks to unlock even higher insights.
In conclusion, Excel is a implausible instrument up to a degree. Like a dependable automobile, it will get you the place you want to go more often than not. However when the highway will get more difficult, typically you want a extra highly effective automobile to maintain transferring ahead.
As of August 2023, though Excel now integrates Python, it does include some limitations, which you’ll examine right here. For my part, working with Excel by way of a Python pocket book is way extra environment friendly for evaluation and knowledge wrangling.
Keen on studying how your corporation can profit from related automations and dashboarding? Be happy to achieve out:
https://www.datagatorsolutions.com/
