Project 2 - Create a report using PowerQuery, Macro VBA, List Functions and Data Validation functions for Data Reporting of Supply Chain Management companies

Below are the instructions and VBA codes to fulfill the tasks for analyzing Loan Applicant data and creating a dashboard in MS Excel for IJS Bank Limited.

Features of the Dashboard

Gender Ratio of Applicants

1. Create a Pivot Table:
   • Select the data range.
   • Insert a Pivot Table and place it in a new worksheet.
   • Drag "Gender" to the Rows area.
   • Drag "Gender" to the Values area and set it to count.
   • Insert a Pivot Chart based on this Pivot Table (e.g., Pie Chart).

Car Owner/Non-Owner Ratio

2. Create a Pivot Table:
   • Insert a Pivot Table.
   • Drag "Car Owner" to the Rows area.
   • Drag "Car Owner" to the Values area and set it to count.
   • Insert a Pivot Chart (e.g., Pie Chart).

House Owner/Non-Owner Ratio

3. Create a Pivot Table:
   • Insert a Pivot Table.
   • Drag "House Owner" to the Rows area.
   • Drag "House Owner" to the Values area and set it to count.
   • Insert a Pivot Chart (e.g., Pie Chart).

Client Income Type

4. Create a Pivot Table:
   • Insert a Pivot Table.
   • Drag "Income Type" to the Rows area.
   • Drag "Income Type" to the Values area and set it to count.
   • Insert a Pivot Chart (e.g., Bar Chart).

Client’s Level of Education

5. Create a Pivot Table:
   • Insert a Pivot Table.
   • Drag "Level of Education" to the Rows area.
   • Drag "Level of Education" to the Values area and set it to count.
   • Insert a Pivot Chart (e.g., Bar Chart).

Client’s Housing Type

6. Create a Pivot Table:
   • Insert a Pivot Table.
   • Drag "Housing Type" to the Rows area.
   • Drag "Housing Type" to the Values area and set it to count.
   • Insert a Pivot Chart (e.g., Bar Chart).

Client’s Family Status

7. Create a Pivot Table:
   • Insert a Pivot Table.
   • Drag "Family Status" to the Rows area.
   • Drag "Family Status" to the Values area and set it to count.
   • Insert a Pivot Chart (e.g., Bar Chart).

Client’s Occupation Type

8. Create a Pivot Table:
   • Insert a Pivot Table.
   • Drag "Occupation Type" to the Rows area.
   • Drag "Occupation Type" to the Values area and set it to count.
   • Insert a Pivot Chart (e.g., Bar Chart).

Separate Worksheet Required

Data Summary of All Female Clients for Income and Loan Amount

Use UNIQUE, SORT, and FILTER functions:

=UNIQUE(FILTER(DataRange, GenderRange="Female"))

Create a summary table using , , and other aggregate functions.

• • Create a summary table using SUM, AVERAGE, and other aggregate functions.

Data Summary of All Male Clients for Income and Loan Amount

• Use DSUM and DAVERAGE
• =DSUM(DataRange, "Income", CriteriaRange)
• =DAVERAGE(DataRange, "Loan Amount", CriteriaRange)
• Data Summary for all Flag Columns
• Use DCOUNT:
• =DCOUNT(DataRange, "FLAG_DOCUMENT_2", CriteriaRange)
• =DCOUNT(DataRange, "FLAG_DOCUMENT_3", CriteriaRange)

• Other Deliverables

  Macro for Conditional Formatting & Freezing Panes

1. Macro for Conditional Formatting:

• Sub ApplyConditionalFormatting()
•     Dim ws As Worksheet
•     Set ws = ThisWorkbook.Sheets("DataSheet")
•     
•     ' Apply conditional formatting to a range
•     With ws.Range("A2:A100")
•         .FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, Formula1:="=1000"
•         .FormatConditions(.FormatConditions.Count).SetFirstPriority
•         With .FormatConditions(1).Interior
•             .PatternColorIndex = xlAutomatic
•             .Color = 65535 ' Yellow color
•         End With
•     End With
• End Sub
• Macro for Freezing Panes:
  
• Sub FreezePanes() Dim ws As Worksheet Set ws = ThisWorkbook.Sheets("DataSheet") ' Freeze the top row ws.Rows("2:2").Select ActiveWindow.FreezePanes = True End Sub

• VBA Code to Convert Selected Data into PDF

• Sub ExportToPDF()

      Dim ws As Worksheet

      Set ws = ThisWorkbook.Sheets("DataSheet")

      ' Define the range to be exported

      Dim ExportRange As Range

      Set ExportRange = ws.Range("A1:G20")

      ' Export the range to PDF

      ExportRange.ExportAsFixedFormat Type:=xlTypePDF, Filename:="C:\Path\To\ExportedFile.pdf", Quality:=xlQualityStandard

  End Sub

• Check Spelling Mistakes using VBA

• Sub CheckSpelling()

      Dim ws As Worksheet

      Set ws = ThisWorkbook.Sheets("DataSheet")

      ' Check spelling for each cell in the worksheet

      For Each cell In ws.UsedRange

          If Not Application.CheckSpelling(cell.Value) Then

              cell.Interior.Color = RGB(255, 0, 0) ' Highlight cell in red

          End If

      Next cell

  End Sub

• These codes and steps will help you create a comprehensive dashboard, perform data analysis, and automate some of the tasks using VBA in Excel.