Challenge #386: Shed Light on a Bank’s Risky Business

Hi Maveryx,

A solution to last week’s challenge can be found here.

This challenge comes to us from @hannah_malek . Thank you for your contribution, Hannah!

Every month, the Risk Team at XYZ Bank compiles a risk report in which each key risk indicator (KRI) is assigned a red, amber, green (RAG) status according to specific thresholds. These thresholds are unique for each KRI and may be modified periodically.

The dataset comprises a collection of KRIs, dates, relevant KRI details, as well as the corresponding thresholds for each KRI on a given date. These include threshold limits for the red category and upper and lower limits for the amber and green categories, along with their respective operators.

Your challenge this week is to calculate the RAG status for each KRI based on the RAG thresholds for that particular date.

Bank_Data_Updated.xlsx

challenge_386_start_file_Updated.yxzp

challenge_386_solution.yxzp

Macros

Advanced

Intermediate

Data Preparation

Accepted answers

All comments

mmontgomery

C386

Spoiler

Green High: IIF(IsEmpty([Green High]),9999999,[Green High])
Green Low: IIF(StartsWith([Threshold Green], '>'),tonumber(REGEX_Replace([Threshold Green], '.(.*)', '$1')),[Green Low])
Rag: IF [Value]>=[Green Low] and [Value]<=[Green High] THEN 'Green'
ELSEIF [Value]>=[Amber Low] and [Value]<=[Amber High] THEN 'Amber'
ELSE 'Red'
ENDIF

c_386.yxzp

patrick_digan

Spoiler

challenge_386_start_file.yxmd

balajilolla2

Spoiler

Solution Attached

challenge_386_start_file.yxmd

alexnajm

Decent challenge!

Spoiler

challenge_386_start_file_Alex Abi-Najm.yxzp

Kenda

Spoiler

challenge_386_start_file.yxmd

AkimasaKajitani

My solution.

I made 2 solutions.

Spoiler

challenge_386_start_file_AK.yxzp

ggruccio

I feel like there is an error in the data where one of the values says green if > 10000, but the value for "Green Low" is 8000. Otherwise here's my solution:

Spoiler

challenge_386_gg_finish.yxzp

Luke_C

Spoiler

Having issues uploading image

challenge_386_start_file.yxmd

RolandSchubert

Spoiler

challenge_386_solution_rsc.yxmd

braveraj

Spoiler

challenge_386_risk_color.yxmd

Watermark

@ggruccio yep, data issues. KRI4634 & KRI9101

Spoiler

challenge_386_MT_Solution.yxmd

ARussell34

Great way to start off the week. I learned about the "between" function and it has been really useful, especially in this problem.

AR_challenge386_sol.yxmd

M__K__A

My solution is attached. I think I did this correctly, however I have a few results that differ from the provided output. I believe there may be some discrepancies unless I am misinterpreting the records...

challenge_386 - MKA Solution.yxmd

olga_strubbe

Thank you, @hannah_malek! This was a nice challenge. It helped me practice my batch macros.

Solution I: standard workflow

Solution II: Batch macro

Challenge 386_Shed light on bank risky business.yxmd

Challenge 386_Shed light on bank risky business + macro.yxmd

Challenge 386_macro.yxmc

rmassambane

386!

challenge_386.yxmd

Sebastian_Chaieb2

Initially I was building my solution based on [Red_Sign] field, however it looks like there is an error with the data as it dose not match specification . So I decide to use [Green_High] when it is NULL.

My Solution below:

Spoiler

challenge_386_Solution_SC.yxzp

DaisukeTsuchiya

Spoiler

A few record didn't match with Output.

Spoiler

challenge_386_start_file_DT.yxzp

Flo_P

Spoiler

A strange challenge with unclear and overlapping data. If there was a lesson here, I didn't learn it.

challenge_386_solved_FPER.yxzp

ed_hayter

Spoiler

Interesting Conceptual Challenge - think if the input data was more varied it would have been more fun. In the end it was varied but not enough to irk a long-winded formula.

challenge_386_start_file.yxzp

gawa

Some record have thresholds overlapped each other, but was it also a part of challenge??

Anyway, result matches

Spoiler

challenge_386_start_file_gawa.yxmd

JereJussila

Spoiler

Fun challenge !

challenge_386_solution_JJ.yxzp

TDouheret

Spoiler

My solution:
Weekly Challenge #386 solution.PNG

challenge_386_start_file TD.yxwz

Pheline

C386

challenge_386_ps.yxzp

alacoume

A solution:

challenge_386_start_file.yxmd

jacobcarman

Spoiler

Decided to recreate lows and highs due to data issue.

challenge_386_JSC.yxzp

rachel_lynch

Great use for the dynamic replace tool in a batch macro! There is one record that I believe may need to be double checked in the source data - the parameters are duplicated (i.e. amber is >= .03 and green is <=0.03 - it can't be both, one should drop the "=").

Spoiler

Shed Light on a Bank’s Risky Business.png

Shed Light on a Bank's Risky Business.yxzp

TonyAndriani

This was a fun one. Went the macro route and worked with the Threshold columns to make it a little more interesting. Giving us the parsed limits pretty much turned this into a beginner-level exercise and didn't seem particularly realistic.

Spoiler

The overlapping thresholds was a nice touch. In the real world, when someone asks us to do an analysis the requestor usually doesn't give us a complete set of requirements (often they don't completely understand the requirements themselves). We have to inspect sample outputs and ask a lot of questions. With these weekly challenges, we can't ask questions but we can look at the output. In this case, we see that we have to evaluate the green threshold first, then amber, then red. (I evaluated red just in case there was a situation where a number fell outside the range of all three thresholds so I could flag it as an out of range error.)