Project 2 - Create a report using PowerQuery, Macro VBA, List Functions and Data Validation functions for Data Reporting of Supply Chain Management companies
Google Drive Link: https://drive.google.com/drive/u/1/folders/136IRuQNgk322K0V7n-iPsSlLtCKpp7TW
Q1. Features of Dashboard ( Dashboard is in EXCEL)
- Gender Ratio of Applicants:
COUNT GenderRatio (Pie Chart in Excel)
| 105059 | M | 34.16431% |
| 202448 | F | 65.83439% |
| 4 | XNA | 0.00130% |
| 307511 |
- Car Owner/ Non-Owner Ratio:
| Car Owner/Non Car Owner Ratio | ||
| Count | Answer | Ratio |
| 104587 | Y | 34.010816% |
| 202924 | N | 65.989184% |
| 307511 | ||
- House Owner/ Non-Owner Ratio:
| House Owner/Non House Owner Ratio | ||
| Count | Answer | Ratio |
| 213312 | Y | 69.36727% |
| 94199 | N | 30.63273% |
| 307511 | ||
- Client Income Type:
Row Labels Count of NAME_INCOME_TYPE Grand Total307511
| Businessman | 10 |
| Commercial associate | 71617 |
| Maternity leave | 5 |
| Pensioner | 55362 |
| State servant | 21703 |
| Student | 18 |
| Unemployed | 22 |
| Working | 158774 |
- Client’s Level of Education:
Clients Education Type Count Grand Total307511
| Academic degree | 164 |
| Higher education | 74863 |
| Incomplete higher | 10277 |
| Lower secondary | 3816 |
| Secondary / secondary special | 218391 |
- Client’s Housing Type
Housing Type Count of NAME_HOUSING_TYPE Grand Total307511
| Co-op apartment | 1122 |
| House / apartment | 272868 |
| Municipal apartment | 11183 |
| Office apartment | 2617 |
| Rented apartment | 4881 |
| With parents | 14840 |
- Client’s Family Status:
Row Labels Count of NAME_FAMILY_STATUS Grand Total307511
| Civil marriage | 29775 |
| Married | 196432 |
| Separated | 19770 |
| Single / not married | 45444 |
| Unknown | 2 |
| Widow | 16088 |
- Client’s Occupation Type:
Row Labels Count of OCCUPATION_TYPE Grand Total211120
| Accountants | 9813 |
| Cleaning staff | 4653 |
| Cooking staff | 5946 |
| Core staff | 27570 |
| Drivers | 18603 |
| High skill tech staff | 11380 |
| HR staff | 563 |
| IT staff | 526 |
| Laborers | 55186 |
| Low-skill Laborers | 2093 |
| Managers | 21371 |
| Medicine staff | 8537 |
| Private service staff | 2652 |
| Realty agents | 751 |
| Sales staff | 32102 |
| Secretaries | 1305 |
| Security staff | 6721 |
| Waiters/barmen staff | 1348 |
| Blanks | 96931 |
Q2.
- Data Summary of All Female Client’s for Income and Loan Amount using New Functions
| DATA SUMMARY FOR MALES | |
| Income | Amt |
| Total Income | 20318041019 |
| Total Count of males | 105059 |
| Average Income of males | 193396.4822 |
| Maximum Income | 18000090 |
| Minimum Income | 27000 |
| Standrad Deviation (Income) | 134597.1695 |
| STDEV Population (Income) | 134596.529 |
| Variance(Income) | 18116398046 |
| VAR Population(Income) | 18116225605 |
| Loan | Amt |
| Total Loan Amount | 64201050311 |
| Maximum Loan | 4050000 |
| Minimum Loan | 45000 |
| Avreage Loan | 611095.1971 |
| Standrad Deviation (Loan) | 411524.1856 |
| STDEV Population (Loan) | 411522.2271 |
| Variance(Loan) | 1.69352E+11 |
| VAR Population(Loan) | 1.69351E+11 |
- Data Summary of All Male Client’s for Income and Loan Amount using List Functions
| DATA SUMMARY FOR FEMALES | |
| Income | Amt |
| Total Income | 31588428942 |
| Total Count of females | 202448 |
| Average Income of females | 156032.3092 |
| Maximum Income | 117000000 |
| Minimum Income | 25650 |
| Standrad Deviation (Income) | 274825.5926 |
| STDEV Population (Income) | 274824.9138 |
| Variance(Income) | 75529106332 |
| VAR Population(Income) | 75528733253 |
| Loan | Amt |
| Total Loan Amount | 1.20004E+11 |
| Maximum Loan | 4050000 |
| Minimum Loan | 45000 |
| Avreage Loan | 592766.7173 |
| Standrad Deviation (Loan) | 397581.6183 |
| STDEV Population (Loan) | 397580.6364 |
| Variance(Loan) | 1.58071E+11 |
| VAR Population(Loan) | 1.5807E+11 |
- Data Summary for all Flag Columns like (FLAG_DOCUMENT_2, FLAG_DOCUMENT_3 etc.) using all variants of DCOUNT function.
| FLAG Documents | Count of 0 | Count of 1 |
| FLAG_DOCUMENT_2 | 307498 | 13 |
| FLAG_DOCUMENT_3 | 89171 | 218340 |
| FLAG_DOCUMENT_4 | 307486 | 25 |
| FLAG_DOCUMENT_5 | 302863 | 4648 |
| FLAG_DOCUMENT_6 | 280433 | 27078 |
| FLAG_DOCUMENT_7 | 307452 | 59 |
| FLAG_DOCUMENT_8 | 282487 | 25024 |
| FLAG_DOCUMENT_9 | 306313 | 1198 |
| FLAG_DOCUMENT_10 | 307504 | 7 |
| FLAG_DOCUMENT_11 | 306308 | 1203 |
| FLAG_DOCUMENT_12 | 307509 | 2 |
| FLAG_DOCUMENT_13 | 306427 | 1084 |
| FLAG_DOCUMENT_14 | 306608 | 903 |
| FLAG_DOCUMENT_15 | 307139 | 372 |
| FLAG_DOCUMENT_16 | 304458 | 3053 |
| FLAG_DOCUMENT_17 | 307429 | 82 |
| FLAG_DOCUMENT_18 | 305011 | 2500 |
| FLAG_DOCUMENT_19 | 307328 | 183 |
| FLAG_DOCUMENT_20 | 307355 | 156 |
| FLAG_DOCUMENT_21 | 307408 |
103 |
Q3.
- Create a Macro for Data Formatting (Conditional Formatting, Freezing Panes) of workbook as same will be applied to the data of next quarter
To view the macro
View>Macros>View Macros (Names as FormattingMACRO)
- Create VBA Code to convert selected data into pdf from applicant data:
Sub ConvertSelectionToPDF()
Dim selectedRange As Range
Dim saveFile As String
Dim chosenFile As Variant
If Selection.Count = 1 Then
Set selectedRange = Application.InputBox("Select a range to convert and save as PDF", "Select Data to convert into PDF", Type:=8)
Else
Set selectedRange = Selection
End If
'Prompt for save location
saveFile = "Selected_Data_" _
& Format(Now(), "yyyymmdd_hhmmss") _
& ".pdf"
saveFile = ThisWorkbook.Path & "" & saveFile
chosenFile = Application.GetSaveAsFilename _
(InitialFileName:=saveFile, _
FileFilter:="PDF Files (*.pdf), *.pdf", _
Title:="Select Folder and File Name to Save as PDF")
If chosenFile <> "False" Then 'save as PDF
selectedRange.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
chosenFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, _
IgnorePrintAreas:=False, OpenAfterPublish:=True
Else
MsgBox "No File is Selected. Therefore, PDF will not be saved", vbOKOnly, "No File Selected"
End If
End Sub
- Check Spelling mistakes in the workbook using VBA Code:
Sub HighlightMisspellings()
Dim cell As Range
For Each cell In ActiveSheet.UsedRange
'Check the spelling and if it's incorrect, then change the cell color to red
If Not Application.CheckSpelling(word:=cell.Text) Then
cell.Interior.Color = RGB(255, 0, 0)
End If
Next cell
End Sub