• Mark Monfort

A look inside the toolkit of a data analyst

Updated: Feb 7, 2021

Having worked in the data analytics field over a decade I'm certainly not new to the game. However, the work done to turn raw data into significant insights and use technology to do so has been around for much longer. Data Analytics or Business Intelligence (BI) tools have been around for a long time and are a key part of the arsenal of assets that consultants deploy. In this article, we take a look at the typical set of tools that one might use as a consultant in the data analytics/business intelligence world.

Analytics - Power BI

There are many data analytics and visualisation tools out there but Power BI is arguably at the top of the list. It's not just because of its price but it's all-round usefulness as a Swiss Army knife kind of tool for any budding analyst out there.

It has a lot of things going for it including:

  • it's part of the Office 365 suite of tools so is easy to get access to (given internal IT permissions).

  • It is free for those just wishing to do analysis on their own data but as soon as insights need to be shared with colleagues, a PRO license may be needed at $13AUD per month per user (and higher costs may be incurred depending on the business requirements). A few other providers allow you to get started for free too.

  • It allows users to use script or menu prompts to transform data into a format they need for further analysis

  • For those familiar with Excel, the transition to learning how to use Power BI is a short gap.

  • Microsoft update the tool almost monthly (taking a break in January) which is more often than other BI tools.

That said, there are limits to the tool and there are times where either the basic desktop version of the tool isn't enough to do the number crunching. Sometimes you need to add some more analytics grunt in the form of more computing power that can be delivered by a cloud server like AWS or Azure. However, in most circumstances, Power BI can help you level up pretty quickly.

Analytics - Excel

Excel is considered by many to be the erstwhile tool of choice for data analysts. It's still used by many to do any number of number crunching or spreadsheet formatting tasks. It is limited by its ability to compute large datasets (it's spreadsheets can have row limits) but for some quick ad-hoc tasks it can prove even more powerful than other tools.

For example, when I worked in financial markets, we had built some pretty cool dashboards that calculated all sorts of financial metrics and visualised them. We did this with sophisticated tools like Qlik Sense and had them sitting in web applications our clients could access. The amount of data driving those apps was large and it meant any updates to get new data there would take time. In less time than that, I could use the Bloomberg API built into Excel and update a TCA (transaction cost analytics) model with the latest numbers within seconds.

Excel is not only fast for doing some types of pre-built analysis but testing new theories can also be far quicker in Excel than in other sophisticated tools. The typical workflow for an analyst would look to do some data modelling on a small scale with some data in Excel and then to test that on a larger scale with a more advanced tool. If the process needs to be repeated (over more data or on a frequent basis) then the move to a data analytics/business intelligence tool is warranted.

All said, Excel might have it's competitors but it's still the king in many ways.

Advanced Analytics - Python/R

For more advanced analysis, some analysts might need to deploy a tool like Python or R. These programming languages are typically best used within an IDE (integrated development environment) like the Jupyter notebooks available from Anaconda. These notebooks allow for more advanced statistical methods to be written to test data.

For example, you might have time series data across a number of factors. You could run correlation analysis to test how well each factor impacts another variable you're testing. Say we have the share price of Apple and other macroeconomic factors across the last 10 years. Rather than create some correlations between each pair and manually assess them, you could run a PCA (principle component analysis) in Python and get your answers sooner. Other forms of advanced statistical analysis are available too and there are tonnes of insights online available for free or at a low cost for those looking to get started.

It's not necessarily used in all analysis we do, but when needed and deployed well, it can be the tool that gives any analysis a greater edge than its competitors

API tools - e.g. CoFlows

The future for companies is digitisation of their businesses and it's hard to do this without good access to APIs. This can be for bringing in data into your business or sending data insights to clients or both. APIs also give you access to new types of services. In the st you may have only pulled in raw data via SFTP or even off USB files to then begin a job process and clean that information. With APIs, that cleansing can be done for you by intermediaries and sent to you in the final format you need to do your analysis. The world is changing and you need API tools to do this.

In our case we use CoFlows, an solution built using an open-source technology stack and flexible enough to meet client needs.

Presentations - PowerPoint

PowerPoint is typically used to put a few charts, tables and words to a couple of pages and to make a slide show out of them. This is the tools main usage but it has far more capability than that. A few things include:

  • A screen recorder - you don't need Adobe or other image capture software when you can simply use the Screen Recording function in Power Point. This records a video of whatever you decide to use on your screen and places it in your presentation. You could save that video somewhere else if you need and this makes for a better way of explaining instructions than just using static images.

  • Interactive Presentations - using action buttons, you can add prompts in your presentation so that when you go into full screen mode you can make it seem that you've built an app that users can navigate around. It's a simple trick but when coupled with the ability to turn a normal PPT extension file into a PowerPoint Show (PPSX) it means that your presentation can look much more like an app than a PowerPoint file.

  • Portrait Mode - this is for those struggling to play around with Word formatting for creating reports. Turning PowerPoint into portrait mode in the page layout and designing your document there gives you far more ease with the control of the chart and wording layout.

These hacks can help in a lot of circumstances but they don't do better than the design and presentation tools that are fit for purpose for these tasks. However, where the circumstance suits, these can and should be part of the analyst arsenal. It's in ours.

Media equipment

Depending on the type of analysis you're doing you may wish to use other hardware enhancements - especially if jobs are being done remotely

Even if not trying to be a media superstar, you owe it to clients to have a good desktop camera (the kind that put laptop cameras to shame) and microphone setup.

In my toolkit I use the following:

  • Razor Kiyo RZ19 webcam

  • Blue Yeti USB Microphone

These are easily portable and added to either my iMac workstation setup or attached to my laptop if I'm on the go. They offer much better experience in terms of clarity and quality of audio/video for clients and especially if videos are used and being recorded.

Definitely worth the investment.


You might not be an analyst to get something out of what we shared here. You might be an analyst have other tools in your repertoire. Whichever it is, the tools we use to more easily work with and present data are out there. If you're looking for better techniques than what you have it would be worth your while to do some of your own experimenting and testing.

Good luck!

13 views0 comments

Recent Posts

See All