Datascience Using Vba Programming And A Sql Database

Practical applications of programming and SQL using a case study with data obtained from the Worldbank

What you'll learn
Know the concepts, principles, and terminology of programming languages and how to develop program solving skills
Datascience, its components, importing of case study into EXCEL
Learn and practice to write an application in a Microsoft programming language (VBA) along with the use of the ActiveX buttons
Install SQL, create a database and tables. Load Case Study into tables using VBA.
Learn and practice the SQL language using Case Study data. Write a View/Query merging and linking tables in SQL.
Link to SQL from VBA and visualize data using ActiveX dropdown and command buttons
Data analyses and visualization including correlation and regression.
Write a stored procedure in VBA comparing and visualizing GDP per capita for different countries
Learn about LAG and LEAD functions in SQL
Learn how to do nested SQL queries
Create temporary and permanent tables in SQL
Do top down and bottom up analyses for any chosen year
Use listboxes, comboboxes, optionboxes and command boxes in a VBA applicaiont
Change chart titles, X an Y series in VBA application
Requirements
No programming experience is needed. Some basic knowledge of Excel is an advantage. You will learn everything and do practical exercises.
You need to have Microsoft office on your computer and enough space to load Microsoft SQL.
Description
This is the course that will help you walk right into a workplace as a data scientist with sufficient knowledge of programming and working with databases. This course has a very practical angle with the theoretical knowledge necessary to become a data scientist hitting the ground running. The programming language VBA (Visual Basic for Applications) of Microsoft is taught with real data obtained from the World bank. You will also learn how to populate, retrieve, clean and manipulate data using a relational database tool called SQL (Structured Query Language), Including in the course is comparative analyses of GDP (Gross Domestic Product) growth and GDP per capita for the BRICS countries (Brazil, Russia, India, China and South Africa). There are 266 countries that can be used for any comparative analyses with years ranging from 1960 to 2020. The countries are classified into 4 income groups and 7 regions. Top down analyses, regression, histograms and many more are included in this course.The The course is divided into 6 chapters with sub sections.Chapter 1:  DATA SCIENCE AND PROGRAMMING LANGUAGESThe components of Data Science, processes and tools are discussed. The approaches in data analyses are covered along with the challenges a data scientist face. Concepts, principles, and terminology of programming languages are covered. How to develop program solving skills is discussed. The programming language of Microsoft Excel called Visual Basic for Applications (VBA) along with its benefits is also covered.Chapter 2:   VBA WITH PRACTICAL APPLICATIONSThe basics of VBA is discussed including declaring variables, message and input boxes, iterative loops, popular statements, ActiveX Control boxes and arrays. An example is given to retrieve data from a webpage that will then be used throughout the course to analyse and present the data. Practical applications programming with buttons, Combo boxes and List boxes and graphs are covered in detail.Chapter 3:  SQL WITH PRACTICAL APPLICATIONSTypes of databases are discussed as well as its components. The basics of SQL (Structured Query Language), a powerful programming language which is used for communicating with and extracting data from databases are covered in detail. The learner will develop a database and tables, work with Views and import data into SQL using VBA code. Extract data using VBA code from a SQL table. Write queries, join tables and learn to manipulate data. Writing procedures in SQL and executing them are also included.Chapter 4: STATISTICAL ANALYSES OF DATAData cleansing and statistical analyses are covered focusing on descriptive analyses using economic Gross Domestic Product (GDP) and exchange rate data.Chapter 5:  VISUALIZATION OF DATAWhat and why visualizations along with practical examples of data visualisation are illustrated.Chapter 6: COMPARATIVE ANALYSESWriting stored procedures using more than one parameter and executing them using VBA are explained in detail. Comparative analyses of South Africa as part of the BRICS (Brazil Russia India China and South Africa) countries are done on GDP growth and GDP per capita growth is demonstrated.

Overview

Section 1: Introduction

Lecture 1 Introduction

Lecture 2 Introduction to chapters presented in this course

Lecture 3 Chapter 1: Data Science and Programming

Lecture 4 Chapter 2 Section 1 - Visual Basic for Application (VBA)

Lecture 5 Chapter 2 Section 2 - Visual Basic for Application (VBA)

Lecture 6 Chapter 3 Section 1 - Structured Query Language(SQL)

Lecture 7 Chapter 3 Section 2 Structured Query Language (SQL)

Lecture 8 Chapter 3 Section 3 Structured Query Language (SQL)

Lecture 9 Chapter 4 Section 1 Statistical Analyses

Lecture 10 Chapter 4 Section 2 Statistical Analyses

Lecture 11 Chapter 4 Section 3 Statistical Analyses

Lecture 12 Chapter 4 Section 4 Statistical Analyses

Lecture 13 Chapter 4 Section 5 Statistical Analyses

Lecture 14 Chapter 5 Section 1 Visualization

Lecture 15 Chapter 5 Section 2 Visualization

Lecture 16 Chapter 6 Section 1 Comparative Analyses

Lecture 17 Chapter 6 Section 2 Comparative Analyses

Lecture 18 Chapter 6 Section 3 Comparative Analyses

Lecture 19 Chapter 6 Section 4 Comparative Analyses

Lecture 20 Chapter 6 Section 5 Comparative Analyses

The main focus of the learning is to teach the learner to become a data scientist, using a programming language (VBA) and a SQL database to find unseen patterns, extract data, and convert information to actionable insights that can be meaningful to any company.