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

You are working as Business Analyst in IJS Bank Limited. Currently your organization is working on Loan Applicant Clients for the current quarter. The Manager of the Branch asked you to analyze Loan Applicant’s data and to Present appropriate report on the shared data. The report should be demonstrated using Dashboard into MS Excel.

Features of Dashboard

• Gender Ratio of Applicants
• Car Owner/ Non-Owner Ratio
• House Owner/ Non-Owner Ratio
• Client Income Type
• Client’s Level of Education
• Client’s Housing Type
• Client’s Family Status
• Client’s Occupation Type

Project Link: Project 2 Loan Applicant's Data

Separate Worksheet Required for

• Data Summary of All Female Client’s for Income and Loan Amount using New Functions
• Data Summary of All Male Client’s for Income and Loan Amount using List Function

Excel Sheet link : Separate Worksheet for answer's for above questions

• Data Summary for all Flag Columns like (FLAG_DOCUMENT_2, FLAG_DOCUMENT_3 etc.) using all variants of DCOUNT function.

Excel Sheet link : Separate Worksheet for answer's for above questions

Other Deliverables

• Create a Macro for Data Formatting (Conditional Formatting, Freezing Panes) of workbook as same will be applied to the data of next quarter
• Sub DataFormatting()
  '
  ' DataFormatting Macro
  ' Macro for Data Formatting (Conditional Formatting, Freezing Panes)
  '
  ' Keyboard Shortcut: Ctrl+k
  '
  Range("Applicant_Data[[#All],[AMT_INCOME_TOTAL]]").Select
  Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
  Formula1:="=540000"
  Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  With Selection.FormatConditions(1).Font
  .Color = -16754788
  .TintAndShade = 0
  End With
  With Selection.FormatConditions(1).Interior
  .PatternColorIndex = xlAutomatic
  .Color = 10284031
  .TintAndShade = 0
  End With
  Selection.FormatConditions(1).StopIfTrue = False
  Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
  Formula1:="=220000"
  Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
  With Selection.FormatConditions(1).Font
  .Color = -16752384
  .TintAndShade = 0
  End With
  With Selection.FormatConditions(1).Interior
  .PatternColorIndex = xlAutomatic
  .Color = 13561798
  .TintAndShade = 0
  End With
  Selection.FormatConditions(1).StopIfTrue = False
  ActiveWindow.SmallScroll Down:=-115
  ActiveWindow.ScrollRow = 307484
  ActiveWindow.ScrollRow = 307033
  ActiveWindow.ScrollRow = 304782
  ActiveWindow.ScrollRow = 300731
  ActiveWindow.ScrollRow = 251659
  ActiveWindow.ScrollRow = 143613
  ActiveWindow.ScrollRow = 118852
  ActiveWindow.ScrollRow = 90490
  ActiveWindow.ScrollRow = 1
  Range("E3").Select
  ActiveWindow.FreezePanes = True
  Range("G9").Select
  End Sub
• Create VBA Code to convert selected data into pdf from applicant data
• Sub PrintSelectionToPDF()
  Dim ThisRng As Range
  Dim strfile As String
  Dim myfile As Variant
  If Selection.Count = 1 Then
  Set ThisRng = Application.InputBox("Select a range", "Get Range", Type:=8)
  Else
  Set ThisRng = Selection
  End If
  'Prompt for save location
  strfile = "Selection" & "_" _
  & Format(Now(), "yyyymmdd_hhmmss") _
  & ".pdf"
  strfile = ThisWorkbook.Path & "\" & strfile
  myfile = Application.GetSaveAsFilename _
  (InitialFileName:=strfile, _
  FileFilter:="PDF Files (*.pdf), *.pdf", _
  Title:="Select Folder and File Name to Save as PDF")
  If myfile <> "False" Then 'save as PDF
  ThisRng.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
  myfile, Quality:=xlQualityStandard, IncludeDocProperties:=True, _
  IgnorePrintAreas:=False, OpenAfterPublish:=True
  Else
  MsgBox "No File Selected. PDF will not be saved", vbOKOnly, "No File Selected"
  End If
  End Sub

• Check Spelling mistakes in the workbook using VBA Code

• Public Sub HighlightSpellingMistakes()

  Dim wd As Range

  For Each wd In ActiveSheet.UsedRange

  If Not Application.CheckSpelling(word:=wd.Text) Then

  wd.Font.Color = vbRed

  wd.Interior.Color = vbYellow

  End If

  Next wd

  End Sub