Project 2 - Create a report using PowerQuery, Macro VBA, List Functions and Data Validation functions for Data Reporting of Supply Chain Management companies
Project Steps:
-
Data Import: Import the loan applicant data into an Excel worksheet.
-
Data Cleaning: Ensure the data is clean and without errors.
-
Dashboard Creation:
a. Gender Ratio of Applicants:
- Create a pivot chart showing the gender ratio of loan applicants.
b. Car Owner/Non-Owner Ratio:
- Create a pivot chart showing the car owner/non-owner ratio.
c. House Owner/Non-Owner Ratio:
- Create a pivot chart showing the house owner/non-owner ratio.
d. Client Income Type:
- Create a pivot chart showing the distribution of income types.
e. Client's Level of Education:
- Create a pivot chart showing the distribution of education levels.
f. Client's Housing Type:
- Create a pivot chart showing the distribution of housing types.
g. Client's Family Status:
- Create a pivot chart showing the distribution of family statuses.
h. Client's Occupation Type:
- Create a pivot chart showing the distribution of occupation types.
i. Data Summary for Flag Columns:
- Create pivot charts and tables for all flag columns using the DCOUNT function.
-
Separate Worksheet for Data Summary:
- Create separate worksheets for data summaries of female and male clients' income and loan amounts.
a. For Female Clients:
- Use unique, sort, and filter functions to summarize income and loan amount data.
b. For Male Clients:
- Use DSUM and DAVERAGE functions to summarize income and loan amount data.
-
Other Deliverables:
a. Create a Macro for Conditional Formatting & Freezing Panes:
- Write VBA code to create a macro that applies conditional formatting and freezes panes as needed.
b. Create VBA Code to Convert Data to PDF:
- Write VBA code to select specific data and convert it into a PDF document from the applicant data.
c. Check Spelling Mistakes Using VBA Code:
- Write VBA code to check for spelling mistakes in the workbook and provide explanations on how the code works.
VBA Code for Conditional Formatting & Freezing Panes:
Here's a simplified example of VBA code for conditional formatting and freezing panes:
Sub ApplyConditionalFormattingAndFreezePanes()
' Conditional Formatting
Range("A1").Select ' Select the range to apply formatting
Selection.FormatConditions.AddColorScale ColorScaleType:=3
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
Selection.FormatConditions(1).ColorScaleCriteria(1).Type = xlConditionValueLowestValue
Selection.FormatConditions(1).ColorScaleCriteria(1).FormatColor.Color = RGB(255, 0, 0)
' Add more conditional formatting rules as needed
' Freezing Panes
ActiveWindow.FreezePanes = True
End Sub
VBA Code to Convert Data to PDF:
Here's an example of VBA code to convert selected data to a PDF:
Sub ConvertDataToPDF()
' Select the data range to convert to PDF
Sheets("Sheet1").Select ' Change the sheet name and range as needed
Range("A1:F100").Select
' Export the selected range as a PDF
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Path\To\Output.pdf", Quality:=xlQualityStandard, OpenAfterPublish:=False
End Sub