Udemy
    •  
    •  
    •  
    •  
    •  
    •  
    •  
    •  
Subscribe
Turn what you know into an opportunity and reach millions around the world.
Learn More
Your cart is empty.
Keep shopping
Datascience using vba programming and a sql database
Rating: 4.7 out of 5(17 ratings)
110 students

Datascience using vba programming and a sql database

Practical applications of programming and SQL using a case study with data obtained from the Worldbank
Created byAlida Herbst
Last updated 7/2022
English

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

Course content

1 section • 19 lectures • 9h 4m total length

  • Introduction2:55

    An introduction to the presenter Dr Alida Herbst and the company PECUNUA

  • Introduction to chapters presented in this course10:07

    This video gives a brief description of the contents that will be covered for each of the six chapters

  • Chapter 1: Data Science and Programming13:43

    •What is data science?

    • What are the components of data science?

    • How the data science process works.

    •What is a computer program?

    •Elements of a programming language.

    •What is a syntax and an expression?

    •What the basic constructs of a programming language entails.

    •What are the steps to develop problem solving skills in programming?

    •What are the benefits of VBA?

    •Developer tag in VBA

  • Chapter 2 Section 1 - Visual Basic for Application (VBA)23:49

    Be able to record a Macro

    Declare variables

    Understand and program iterative loops

    Know how to work with message and input boxes

    Know how to work columns and rows in arrays

    Know popular VBA statements

    Understand ActiveX boxes and what each one does

  • Chapter 2 Section 2 - Visual Basic for Application (VBA)50:17

    •Have a good grasp on the case study.

    •Be able to import data from a website.

    •Write an application using

           Declaring variables

           Using ActiveX buttons

           Using Iterative loops

           Using message boxes

  • Chapter 3 Section 1 - Structured Query Language(SQL)13:19

    •Know what a database is and its types, components.

    •What is DBMS

    •Know the benefits of SQL

    •Load SQL, be able to create a database and tables

    •Main SQL commands

  • Chapter 3 Section 2 Structured Query Language (SQL)20:51

    •What is an ADO and how to add it into VBA

    •How to connect to an SQL database using VBA

    •Record set properties

    •Record set methods

    Write VBA application to connect to SQL

  • VBA & SQL
  • Chapter 4 Section 1 Statistical Analyses16:11

    •What is data analyses

    •Types of analytics

    •Measures of Central Tendencies

    •Measures of Dispersion

    •Shapes Skewness and Kurtosis

    •Data Analysis Add-In

    •Descriptive Statistics

  • Chapter 4 Section 2 Statistical Analyses26:50

    •Populate ComboBox from SQL

    •Retrieve data from SQL

    •Populate Spreadsheet with data

  • Chapter 4 Section 3 Statistical Analyses11:04

    •Record a macro using descriptive statistics

    •Add descriptive statistics to application on DESCRIPTIVE sheet

    •Analyse descriptive statistics

  • Chapter 4 Section 4 Statistical Analyses14:01

    •Correlation

    •Correlation coefficient

    •Record Macro for correlation

    •Application for correlation

    •Add Scatter plot

  • Chapter 4 Section 5 Statistical Analyses11:00

    •Regression

    •Record Macro for regression

    •Application for regression

    •Regression output

  • Chapter 5 Section 1 Visualization12:09

    •What and Why Visualization

    •Types of visualization

    •Line graph of GDP per Capita

    •Adding titles to graph using VBA

  • Chapter 5 Section 2 Visualization56:56

    •Create table Countries in SQL

    •Write VBA macro and Populate Countries in SQL

    •Create new view with a group by statement

    •Create sheet Visuals with command button and combobox

    •Create bar chart for income group

    •Create a pie chart for income group

  • Datascience, statistics and visualizations
  • Chapter 6 Section 1 Comparative Analyses15:14

    •Lead and Lag function

    •Calculating a Growth variable

    •Stored procedure with Income group, Region, GDP per Capita, population and GDP growth for any country

  • Chapter 6 Section 2 Comparative Analyses44:26

    •List box application

    •Stored procedure

    •Boxplots comparing BRICS (Brazil Russia India China South Africa) countries on GDP growth

  • Chapter 6 Section 3 Comparative Analyses31:58

    •Create a nested view

    •Stored procedure with nested view

    •List box application with line graphs comparing BRICS countries on GDP growth or GDP per capita with an option button

  • Chapter 6 Section 4 Comparative Analyses1:58:25

    •Application with Years list boxes, income group and region

    •Delete records in COUNTRIES where region or income group is null.

    • Create new view with all parameters

    •Create stored procedure for 5 year (SDATE) parameters determining average growth per INCOMEGROUP per year

    •Histograms comparing years for region and income group

  • Chapter 6 Section 5 Comparative Analyses51:36

    •Top and Bottom analyses per year

    •Stored procedure

    • 4 nested select statements

    • Temporary table

    •Permanent table and drop statement

    •Select Case Statement

    •Summarize and recap some important aspects

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 LANGUAGES

The 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 APPLICATIONS

The 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 APPLICATIONS

Types 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 DATA

Data cleansing and statistical analyses are covered focusing on descriptive analyses using economic Gross Domestic Product (GDP) and exchange rate data.

Chapter 5:  VISUALIZATION OF DATA

What and why visualizations along with practical examples of data visualisation are illustrated.

Chapter 6: COMPARATIVE ANALYSES

Writing 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.

Who this course is for:

  • 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.

Report abuse