Analytics Tools — Excel, SQL and Business Intelligence

Analytics Tools — Excel, SQL and Business Intelligence

Syllabus tag: KASNEB CPA | Advanced Level | CA34S1 Business Data Analytics

1. Microsoft Excel for data analytics

Excel remains the most widely used analytics tool in business, particularly in finance and accounting.

PivotTables: the most powerful Excel feature for data analysis — summarise, sort, and aggregate large datasets without writing formulas. Drag-and-drop interface; can produce frequency distributions, cross-tabulations, and summary reports.

Key functions for analytics: VLOOKUP/XLOOKUP (look up data across tables); SUMIFS/COUNTIFS (conditional aggregation); INDEX/MATCH (flexible lookup); FORECAST.LINEAR/FORECAST.ETS (time series forecasting); STDEV.P/STDEV.S (standard deviations); PERCENTILE, QUARTILE, MEDIAN (distribution analysis); CORREL (correlation coefficient).

Power Query: ETL within Excel — connects to multiple data sources, cleans and transforms data, and loads it into Excel or the Power BI Data Model. Automates repetitive data preparation tasks.

Power Pivot: extends Excel's data model to handle millions of rows and enables relationships between multiple tables (like a mini data warehouse within Excel).

2. SQL for data analysis

SQL (Structured Query Language) is the standard language for querying relational databases. Every data analyst must be proficient in SQL.

Core SQL for analytics:

SELECT column1, column2, aggregate_function
FROM table_name
WHERE condition
GROUP BY column1
HAVING aggregate_condition
ORDER BY column2 DESC
LIMIT n;

Key aggregate functions: SUM(), COUNT(), AVG(), MAX(), MIN().

Joining tables: INNER JOIN (only matching rows); LEFT JOIN (all rows from left table + matches from right); FULL OUTER JOIN (all rows from both tables). Efficient joining is critical for performance on large datasets.

Window functions: RANK(), ROW_NUMBER(), LAG(), LEAD(), SUM() OVER (PARTITION BY...) — perform calculations across a set of related rows without collapsing them into groups. Essential for ranking, running totals, and period comparisons.

3. Python for data analytics

Python has become the dominant language for data science: Pandas (data manipulation — DataFrames); NumPy (numerical computing — arrays and mathematical functions); Matplotlib/Seaborn (data visualisation); Scikit-learn (machine learning — regression, classification, clustering); Statsmodels (statistical analysis and econometrics).

4. R for statistical analysis

R is particularly strong for statistical modelling: ggplot2 (elegant data visualisation); dplyr (data manipulation); tidyr (data tidying); caret/tidymodels (machine learning workflows). Often preferred in academic research and actuarial science.

5. Business Intelligence platforms

Power BI (Microsoft): connects to hundreds of data sources; creates interactive dashboards; integrates with Azure and Microsoft 365; DAX language for calculated measures. Tableau: industry-leading visualisation; drag-and-drop; Tableau Server/Cloud for sharing. Looker (Google Cloud): uses LookML modelling layer; central semantic layer ensures consistent definitions across reports. Qlik: associative data model — explores unexpected relationships between data elements.

6. The modern data stack

Modern organisations use a "modern data stack": cloud data warehouse (Snowflake, BigQuery) → data transformation layer (dbt) → BI tool (Tableau, Looker). dbt (data build tool) enables analysts to write SQL-based transformations and models with version control, testing, and documentation — bringing software engineering practices to analytics.