HONG LEONG BANK BERHAD
Users had provided 2 input files which is Invest_Sales.xls and Invest_Redem.xls in the Input folder. The
files are password protected. Users would like to combine both report into 1 summary report and know
how well each asset is performing. Refer to the below task on the steps to combine the report.
You are also been provided 2 more files
1. Map_Date.xls – mapping file for the date calculation for each assets
2. HLBB_UiPath_Output_Template.xlsx – Output template where users are expected to receive
You are required to use UiPath studio and Orchestrator(if required) with any official packages to create
the workflow. You are advised to use the development best practice to develop your workflow.
Once you’ve completed the process, please zip your whole workflow including all relevant files and send
it back to HLBB RPA Team for review.
Task 1(Sales)
1. Open “Invest Sales” file
2. Password is “S@le5”
3. Sort “Confirmation Date”
4. Delete empty “Confirmation Date”
5. Sort “Asset Code”
6. Add column “Desc”, “Date”, “DR” and “CR”
7. Duplicate the whole data table to 3 copies
8. Update the “Desc”
a. 1st copy with “Pyt fr Cust”
b. 2nd copy with “Pyt to FH”
c. 3rd copy with “Comm”
9. Update value for “CR” or “DR”
a. Pyt fr Cust – Copy “Original Settlement” to “CR”
b. Pyt to FH – Copy “Net Investment Amount” to “DR”
c. Comm – Copy “Conf. Rebate Amount” to “DR”
10. Save the file
Task 2(Redemption)
1. Open “Invest Redem” file
2. Password is “Red3nn”
3. Sort “Confirmation Date”
4. Delete empty “Confirmation Date”
5. Soft “Asset Code”
6. Add column “Desc”, “Date”, “DR” and “CR”
7. Duplicate the whole data table to 2 copies
8. Update the “Desc”
a. 1st copy with “Pyt fr FH”
b. 2nd copy with “Pyt to Cust”
Page 1 of 2
HONG LEONG BANK BERHAD
9. Update Value for “CR” or “DR”
a. Pyt fr FH – Copy “Gross Sett. Amount” to “CR”
b. Pyt to Cust – Copy “Gross Sett. Amount” to “DR”
10. Save the file
Task 3(Combine & Reporting)
1. Combine both Sales and Redemption Report
2. Calculate the “Date” by referring to the Map_Date.xls
a. Lookup the Asset Code and get the T+ value based on the “Desc” column
b. Use “Transaction Date” from the original report and add the date based on the T value
i. Example :
Transaction Date T T+1 T+2 …
01/01/2024 01/01/2024 02/01/2024 03/01/2024
c. Date calculation for Comm will exclude Sunday
d. Date calculation for other Desc will exclude weekends(Sat & Sun)
e. If “Asset Code” is not found, please send an exception report to user.
3. Save the output to “Result” sheet in the Output Excel
4. Provide the summary report from the Result to provide the below
a. Total CR and DR for each “Date” and save in “Report” sheet in the Output Excel
b. Total CR and DR by “Asset Code” by each currency and save in “Report + Currency” sheet
in the Output Excel
i. To get the currency it is defined in the Mapping file “Currency” column
5. Send the Output file as attachment to the recipients with the below sample
a. Email Title : RPA Process done at {today}
b. Email Body :
c. Email Attachment : HLBB_UiPath_Output_{today date}.xlsx
Page 2 of 2