Category: Techniques
-
SQL for accounting and finance professionals
If you’re an accounting or finance professional wanting to learn SQL, I’ve got good news: most of the knowledge you have of Excel or Google Sheets can be translated into SQL. Whether you’re looking at a spreadsheet or a database…
-
Calculating medians and quartiles across groups in SQL
One of the best ways to understand data is through the use of descriptive statistics, figuring out the minimum and maximum values, the median value, and the quartiles. When you’re working with smaller datasets this is easy, but with larger…
-
ETL in MySQL – How to automatically load data into a table every day
I’m working on a dashboard to track COVID-19 cases per capita in Calgary, and while the government’s open data API provides daily case counts within the city it doesn’t have any history available. The easy solution to this is to…
-
Installing MySQL 8 on Ubuntu 20.04
If you need a database for a project, MySQL is one of the most popular choices. It’s free, open-source and is a core part of of the popular LAMP (Linux, Apache, MySQL, PHP) web application stack. If you want to…
-
Create Professional Reports in Excel using R and XLSX
While R is the real workhorse of data analysis and modeling, if you want to share the results of your work with other people your probably going to send it in a spreadsheet. Luckily the xlsx package for R makes…
-
Scheduling R scripts to run automatically in Windows
A key component in data acquisition or reporting is the ability to trigger your script to run at a set time each day. Whether you are attempting to download the latest stock prices or update corporate earnings reports, once you’ve…
-
Exploring public data with Power BI – abandoned mines in Ontario
Power BI is Microsoft’s data exploration and dashboarding tool. While it hasn’t risen to desktop prominence like Excel and Outlook have for the majority of knowledge workers, it is an incredibly capable tool which allows you to quickly visualize data…
-
Advanced email in R: embedding images and markdown
Previously we looked at how you can combine R and Markdown to create reports directly from your R scripts, and also how to send email from R using Microsoft Outlook. In this post, we’ll take these concepts a step further…
-
Get started with RPA today using free tools
Robotic process automation (or RPA) is transforming the way many businesses handle their repetitious, labour intensive tasks such as reporting, making basic decisions, and providing services. Using software these tasks can be automated; reducing the time to complete tasks while…
-
Quickly create polished, professional reports with Markdown and R
One underappreciated feature in R is the ability to easily create beautiful reports using Markdown. Markdown files contain a combination of code and text, allowing you to write your analysis alongside your code and publish both the analysis document and…