About Course:

Data Analysis Master Program covered Advance Excel, VBA/Macros, MS Access, SQL, Power BI & Python Practical Training which is designed by industry expert. Data Analysis training course consist of study material, corporate assignment with live data of banking & Finance, workforce operations, HR reports etc. Inside the Data Analytics course has deep understanding of concepts & logic, Data Analysis, Automation Reporting, create dashboard, user forms in Access, SQL Queries to Figure meaningful data, Visualization in Tableau/Power BI, Python and basic of Business Intelligent etc.  Sage Academy, Data Analysis Course is to unlock the great career in good companies.

Data Analysis professional course are currently available at our Delhi on weekdays or weekends.

Who can learn this course:

Sage Academy is a rapidly developing corporate practical training and development company based in Delhi that helps people to learn new skills and become job-worthy. Enrolling in our Data analytics program that is helpful to career enhancement of experienced professionals or Freshers. People who have taken a sabbatical from their careers like Graduates can also learn new skills and become employable with the help of our practical training and certifications courses. We promise to meet all your training and development needs and make you an expert in a few weeks. Trust us once and you’ll be happy you did.

Data Analytics Master Certification Training Program

Module 1 - Advanced MS Excel and Back-End Operations: Introduction to Advanced MS Excel:-

Circular References error, Rectify Circular Reference, Formula Auditing, Structured Reference Solve The Real World Excel Problems with Functions:

Mathematical Functions:

• Abs, Sum, Sumif, Sumifs,Count, Counta, Countif, Countifs,Countblank, Average, Averagea, Averageif, Averageifs.
• Subtotal, Aggregate, Rand, Randbetween, Roundup, Rounddown, Round, Sumproduct

If Formulas:

• If, If with OR, If with AND, If with AND &OR, If with Countif, If With Sumif.
• If with Trim, If with Concatenation, If with Left, Mid, Right.
• If with Other formulas, Complex formulas writing in If.
• Nested If (For Multiple Conditions), If condition used more than one time in the same formula.

More If Formulas:

• Nested if with Multiple Text Functions, TAX Calculation, Other Critical Lookup Formulas
• Nested if with VLook-up And Hlookup.
• Introduction to Name Manager: Name Ranges and Apply the Name Ranges on the combination of Cells.

Date & Time Function:-

• Date, Day, Month, Year, Edate, Eomonth, Networkdays, Workday, Weeknum, Weekday, Hour, Minute, Second, Now, Today, Time

Statistical Function & Other Functions :-

• Isna, Isblank, Iserr, Iseven, Isodd, Islogical, Isytext, Max, Min, Mid, Maxa, Maxifs, Median, Minifs, Mina, Var, Vara, Correl, Geomen, Rank, Percentile, Frequency.
• Information Function: IsOdd, IsEven, IsError, IsNumber, IsText, IsBlank, IsRef, ISNA Some Other Data Base Functions :Dsum,DCount, DAverage, DMax, Dmin

Lookup & Reference Functions:-

• Discussion on Lookup Functions, Use of Lookup, Vlookup, Hlookup, Index, Indirect Match, Offset, Choose, Rows, Columns, Transpose.
• Vlookup, Hlookup with Name Range And Match .
• Find Data In Opposition by Index Match.

Array With Multiple Formulas:                                                                           

• Array in Multiple formulas, Array with Lookup functions.
• Array With Sumifs, Countifs, Sumproduct , Large Functions.

Text Functions & Data Validation :-

• Char, Clean, Code, Concatenate, Find, Search, Substitute, Replace, Len, Right, Left, Mid, Lower, Upper, Proper, Text, Trim, Value, Large, Small, Filters (Basic, Advanced, Conditional), Sort (Ascending, Descending, Cell/ Font Color), Conditional Formatting, Data Validation, Group & Ungroup, Data split.

Pivot Table and Pivot Charts with Slicer:

• Do the Multiple Field Setting in Pivot Table.
• Pivot form Multiple Source of Data, Data Ranges, Name Range.
• Group Pivot Table Items, Multi-level Pivot Table, Calculated Field/Item
• Perform the % calculation on the basis of multiple fields, Using Slicer.

Advanced Chart Technique:-

• How To Make Dynamic Charts, Bar Charts, Pie Charts, Scatter Chart, Line Chart, Column Chart, Speedometer Chart, Gantt Chart, Pareto Charts

Advanced Dashboard:-

Report Development

Module 2 - VBA - Macros & Dashboard Programming

What is VBA & How VBA Works?

• Quick Review of Macros
• Introducing the Visual Basic Editor
• Uses of record Macros
• Understanding and creating modules

Programming Concepts

• VBA Sub and Function Procedures
• How to create a message box
• Write a Program to update and retrieve information using Input Box
• Understanding and using Select Case statement
• How do I define a variables and Rules for defining a Variables Name and Type
• Creating And using Variables
• Working with range Objects
• How to save and Protect Modules

Decision Makers

• If……Then……Else
• If……Then……ElseIf……If

Other Kinds of Loops

• Working with Do While u. Loop Procedure
• Do…… Until Loop and Do…… Loop Until
• Do……While loop and Do…… Loop While
• For each…… Next
• For……Next

Workbook Objects Create or Add Single and Multiple Workbooks

• Workbook Save and Save AS
• Open Single and Multiple Workbooks
• Close Specify and Multiple Workbooks
• Activate From one workbook to another Workbook
• Open Workbook from Specific Path
• Get Workbook Name and Paths
• Hide and Unhide for Single and Multiple Workbooks
• Protect and Unprotect Worksheets

Worksheet Objects insert a single and Multiple Worksheets

• Delete Specific and multiple worksheets
• Get Count of Worksheets
• Select a Specific and all Worksheets
• Get All Worksheets Name
• Hide and Unhide For Single and Multiple Worksheets
• Rename for Single and Multiple Worksheets
• Protect and unprotect worksheets
• Sort and Move worksheets
• Calculate entire worksheet
• Using VBA and worksheet Function

Cell objects insert Single and multiple Row , Column and Cells

• Delete Single and Multiple row, Column and Cells
• Get Range or Address of Cell and Selection
• Navigate from one Cell to another Cell
• Select specific Range, Cell, Rows and Column
• Types of Selection and Offset method
• Insert Function In cell

Reading and Writing Arrays

• Defining Arrays

Form Controls and User Forms (Create and Design an user Form)

• Working With User Forms & User Forms Events Like List Box, Combo Box, Option Buttons, Check Box, Text Box, Labels, Command Button, Toggle Button.
• How To Create Dynamic Dashboard On User Form With Different Controls
• How To Link Various User Form With Each Other To Create A Complete Interface Between User And System

VBA Programming Functions

• Create a Sum Functions
• Create Multiply Function
• Create Count Function
• Extract Text & Number
• Proper Function
• Vlookup Function
• Public or Private function

Excel VBA Power Programming for VBA Macros

• Working with Dynamic Ranges. Protecting worksheets, Cells and Ranges. Working with Multiple Files. Opening &saving Files
• How to Analyze Data On multi Worksheets And Build Summary sheets
• How to Access the Windows File and Folder System to Open and Close Workbooks
• How to protect your code Against Errors
• How to create Your own custom Business Worksheet Function in VBA
• How to create Basic Report Generation Tools Using Excel VbA, Microsoft Word and PowerPoint
• How to use the Excel Visual Basic Macro record Excel Tasks in VBA And then Interpret the code

Connection between Excel VBA & other platforms

• How To Establish Connection Between Excel Vba And Outlooks Through Vba
•  Effective Error Handling

Automation Development Reports & Live Projects

Module 3 - MS Access & SQL

MS Access is a Relational Database Management System (RDBMS) Topics Covered as follows:

MS - Access:-

Understanding Databases

•  Starting and Opening an Existing Database
• Moving Around in Access
• Understanding Datasheet View & Design View
• Using the Mouse Pointer to Navigate
• Using the Keyboard to Navigate

Creating Tables

• Creating a Database
• Creating a Table Using the Wizard
• Creating and Modifying a Table
• Adding Fields to Tables
• Adding and Editing Records
• Printing Tables
• Moving and Deleting Fields
• Deleting Records

Working with Tables

• Formatting a Table
• Modifying Field Properties
• Sorting Records in a Table
• Finding Records in a Table
• Using Filters with a Table
• Establishing Relationships Between Tables
• Creating Subdatasheets
• Importing Records From an External Source

Designing a Form

• Creating a Form Using Auto Form
• Creating a Form Using the Form Wizard
• Adding Controls to a Form
• Modifying Control Properties
• Resizing and Moving Controls
• Entering Records into a Form

Designing a Report

• Creating a Report Using Auto Report
• Creating a Report Using Report Wizard
• Adding a Control to a Report
• Formatting a Report
• Resizing and Moving Controls
• Creating Calculated Controls

Creating and Using Queries

• Creating and Running a Query
• Specifying Criteria in a Query
• Using Comparison Operators
• Creating a Calculated Field
• Creating a Multiple-Table Query

Automating Tasks

• Using Controls to Run a Macro
• Assigning a Macro to an Event
• Assigning a Macro to a Condition
• Testing and Debugging a Macro

SQL:-

SQL Overview

• Relational database concepts, specific products
• SQL syntax rules
• Data definition, data manipulation, and data control statements
• Getting acquainted with the course database and editor

SQL SELECT statements

• Clauses
• The SELECT clause: columns and aliases, where expressions, order by expressions how null Values behave

SQL Functions and Expressions

• Eliminating duplicates with DISTINCT arithmetic expressions
• Replacing null values
• Numeric operations, including rounding
• Date and time functions
• Nested table expressions
• Case logic
• Other expressions in specific DBMS Products

SQL Updating

• The INSERT, UPDATE and DELETE statements
• Column constraints and defaults
• Referential integrity constraints

SQL Joins

• Inner joins with original and SQL 92 syntax
• Table aliases
• Left, right and full outer joins, Inner joins
• Self-joins

SQL Sub Queries and Unions

• Intersection with IN, and, Between
• Sub queries
• Difference with IS NULL and IS NOT NULL sub queries
• The purpose and usage of UNION and UNIONALL

SQL Summarization

• The column functions MIN, MAX, AVG, SUM and COUNT, UPPER, LENGTH, LOWER
• The GROUP BY and HAVING clauses Grouping in a combination with joining

Module 4 -Microsoft POWER BI Report Design and Dash boarding

Power BI Desktop, Custom Visuals

Introduction to Power BI:- 

Power BI Component, Types of Reports in Real-time Usage, Power BI Tools and Implementation Plan Power BI Licensing and Excel Analytics.

Power BI Desktop Tool:

• Report Visuals, Fields, Pages and Filters
• Data and Relationship Option, PBI Canvas
• Get Data from DAT Files, Excel Files, Access Files
• PBIX and PBIT Files And Re-Using Reports
• Data Import Options Designing Simple / Basics Reports in PBI
• Visual Interactions in Power BI - Options Spotlight Options with Visuals, Real-time Use
• Slicer Visual in Power BI and Data Filters

Hierarchies & Filters:

• Grouping and Binning with Fields, Bin Size and Biz Limits (Max, Min)
• Creating Hierarchies. Drilldown, Drill Up Reports
• Filters : Types and Usage in Real-time, Conditional Filters, Visual Filters, Page Filters, Report Filters
• Drill-thru Filters with Hierarchy Levels TOP N Filters – Usage Filtering at Category Level, Import and Direct Query with Power BI.

Power Bi Visuals:

• Fields, Formats and Analytics Options
• Table Visuals & Properties, Data Bar and Data Scaling Options
• Divergent Colors and Data Labeling Matrix : Sub Totals, Grand Totals
• Row Groups and Column Groups in Matrix
• Slicer Visual - Properties, Alignment Single Select and All Options
• Chart Reports - Common Properties Axis, Legend Types- Stacked Bar, Column, Line charts Clustered Bar, Column, Line Chart

Power Bi Visuals with Different Types:

• Tree Map, Funnel and Gauge Reports, Map Reports
• Single Row Card and Multi Row Cards
• Callout Values in KPI Reports and Use, Indicator, Trend and Target Goals in KPIs
• Using Buttons, Images in Power BI Canvas
• Bookmarks in Power BI Desktop – Usage Using Bookmarks for Visual Filters

Module 4.2 - Data Modeling with Power Query

Power Query Basic Operations:

• Power Query Usage & Operation Types, QUERY Concept, Properties, Validations
• Power Query - Data Mash Up Operations
• Basic Data Types, Literals and Values, Expressions
• Primitives in M Language, Structured Data Values in Power Query
• LIST, RECORD, TABLE, Connection Format Settings let, source, in statements in M Lang Functions, Parameters in Power Query
• INVOKE Functions & Execution Results
• Power BI Canvas: Edits, Applied Steps, Frowns, Query Header Row Formatting

Power Query Usage:

• Power Query Transformations Categories
• Query Combine & Merge Transformations, Join Options In Merge Transformation
• Truncate, Replace, Split, Reduce Rows, Manage Columns, Hide / Show Columns Grouping, Aggregations, Column Formats
• Transpose, Reverse Rows Transformations
• Power Query - Row Count And Replace, Data Type Detection - Scenarios, Use
• Data Type Conversions And Value Replace Fill Up And Fill Down,Pivot And Unpivot Transformations
• Move, Filter And Converttolist() Split, Format, Merge, Extract, Parse, Date,Time

Module 4.3 - Data Modeling with DAX

DAX Functions:

• DAX as library of Functions, Types, Variables, Operators Dax Formula With Excel, Limitations
• DAX Architecture and Entity Sets
• Rules OF DAX, Working Options, Syntax, Functions
• ROW Context and Filter Context, DAX Structures and Syntax Options
• Creating and Measuring with DAX Creating and Using Columns with DAX

Advance DAX Functions:

• Data Modeling Options in DAX, Detecting & Adding Relations for DAX
• Power BI DAX Functions - Types, Usage, Cheat Sheet
• Power BI Reports - DAX Functionalities Calculated Columns, Aggregated Measures
• Quick Measures in DAX - Auto validations, DAX Performance Date and Time & Text Functions, Logical & Mathematical Functions
• Data Modeling with DAX. Creating Roles
• SELECTEDVALUE, FORMAT Functions RELATED, COUNTROWS CALCULATE, SUM, ALL

Report Development

Module 5 - Python Training Program with Data Analysis:

Module 1 - Basic Python

• Using Arithmetic Operators in Python
• The Double Equality Sign
• How to Reassign Values
• Understanding Line Continuation
• Indexing Elements
• Structuring with Indentation
• Operators
• Create Functions with Parameters

Module 2 - Sequences, Iterations, Date and Time

• Lists, List Slicing, Tuples, Dictionaries
• For Loops, Conditions
• Lists with the Range () Function
• How to Use Date & Date Time Class
• Calendar in Python
• Python Text Calendar

Module 3 – Data Cleansing with Python

• Sub Setting / Filtering / Slicing Data
• Functions, Indexing or referring with column names/rows
• Mutation of table (Adding/deleting columns or Rows)
• Binning data (Binning numerical variables in to categorical variables)
• Sorting (by data/values, index) - By one column or multiple columns - Ascending or Descending
• Type conversions, Setting index
• Handling duplicates /missing/Outliers
• Creating dummies from categorical data (using get_dummies())
• Applying functions to all the variables in a data frame (broadcasting)
• Data manipulation tools (Operators, Functions, Packages, control structures, Loops, arrays etc.)

Module 4 - File Handling and Web Scrapping

• File Objects, File Different Modes and Object Attributes
• How to create a Text Fil and Append Data to a File and Read a File
• Closing a file, Read, read line, read lines, write, write lines…!!
• Renaming and Deleting Files
• Directories in Python
• Working with CSV files and CSV Module
• Web Scraping with Beautiful Soup, Urllib2, Data frames

Module 5 – Data Handling and Manipulation

• Introduction to NumPy and SciPy
• Introduction To Type of Data Variables
• Data Summarization Techniques
• Building A Data Dictionary
• Outlier Treatment
• Missing Value Treatment.
• Data manipulation using Pandas
• Import and export
• Database access with SQL.

Module 6- Data Analytics and Visualization

• Introduction to Seaborn and Matplotlib
• Plotting with Matplotlib
• Types of Charts & Graphs (Line, Bar, Histogram, Pie Chart, Scatter Plot)

Module 7 - Descriptive statistics And Inferential statistics:

• Measures of central tendency
• Measures of dispersion
• Bivariate, Univariate Analysis
• Range, Skewness
• Interpretation of histograms
• Research methodologies
• Basics of probability theory. Bayes Theorem
• Probability distribution functions – Uniform, Bernoulli, Binomial, Normal, Log Normal, T
• Continuous probability distributions
• Hypothesis testing – 1 Sided test, 2-Sided tests. F test. T test. Chi Sq Test. ANOVA
• Statistics and visualization using Python.

Module 8 - Time series forecasting and Clustering:

• Learn basic concepts of time series modeling
• Basic techniques for forecasting
• Smoothing techniques
• Decomposition, Understanding the fundamentals of ARIMA
• ARIMA modeling, model estimation & interpretation
• Forecasting with regression and time series data
• ARIMAX or dynamic regression models to build forecasting models with multiple regressors
• Case Study = Case study on predicting sales for a large European retailer using real data.
• Types of clustering & their uses
• K-Means clustering
• Hierarchical clustering
• Case Study = Case study on retail customer segmentation using K Means clustering techniques on real data