Datascience using vba programming and a sql database
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
- 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.
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.
Teaching and sharing knowledge as well as making life easier through automation of processes is her passion. Alida loves working with data and used VBA, SQL, Python and Microsoft power BI extensively. Throughout her life she has written numerous applications using SQL and VBA. She has worked at numerous financial institutions as a data scientist, fund manager, risk analyst and researcher. She has also been privileged to teach at the University of Pretoria in South Africa. She obtained her PhD in statistics in 1994 on modelling non-linear growth. She won 2 governors and numerous team awards at the South African Reserve Bank (SARB) through automation of processes in the Financial Markets Department (FMD). She loves life and is passionate about everything she does. Her motto is to maximize and go with the flow..