Collateral Report Madness

Get help. Get answers. Let others lend you a hand.

Moderator: Mr_Noodle

Collateral Report Madness Thu Jun 27, 2019 4:27 pm • by DoogieWall

Each day I must create a collateral report. These are stored in the following folder structure: Bank Name\Year\Month\Day\Files… An example would be: Citizens\2019\06 – Jun\2019-06-26\Files…

My process goes something like this:

Step 1: Run an access program that creates the appropriate daily subfolder and fills it with almost all the necessary files.

Step 2: I go into the previously created subfolder (generally the prior working day) and copy a spreadsheet manually into the new subfolder for today.

Step 3: I rename the copied spreadsheet using the following structure: BBC_1234_YYYYMMDD.xls. Where 1234 = a sequential borrowing base report number. An example would be: BBC_1869_20190626.xls.

I would love to automate this with hazel, but I have spent quite a bit of time with not much success. So I’m writing now in hopes that someone can embarrass me by laying out how simply this can be done.
Posts: 1
Joined: Thu Jun 27, 2019 3:28 pm

Re: Collateral Report Madness Fri Jun 28, 2019 11:27 am • by Mr_Noodle
To be clear, step 1 is already done by a program you are already using and don't need Hazel to do that part?

For step 2, you will need to have Hazel go into subfolders. Check the manual as there is a whole chapter on this. Now, in your case, you only want it to go into yesterdays folder so it's going to be tricky. You can use a bunch of conditions for each folder level. I also assume the spreadsheet is different for each bank and that Hazel is monitoring the folder containing each bank folder. As a result, you want a set of conditions like:
Code: Select all
    If (all) are met
        If Kind is Folder
        If (all) are met
            Subfolder level is 1
        If (all) are met
            Subfolder level is 2
            Name matches (◦ year)
            (◦ year) is Today
        If (all) are met
           Subfolder level is 2
           Name matches (◦ month)
           (◦ month) is Today
        If (all) are met
           Subfolder level is 3
           Name matches (◦ date)
           (◦ date) is before Today
           (◦ date) is in the last 24 hours
        Run rules on folder contents

In this case, it will go specifically into yesterday's set of folders. Once there, you can create a rule to match the file:
Code: Select all
    If (all) are met
        Name matches (• file name)_(◦ file date)
        Extension is xls
        Move to Monitored Folder
        Sort into subfolder (current date <<format set to year>>) ▸ (current date <<format set to month>>) ▸ (current date)
        Rename (• file name)_(current date)

There's a bunch to digest so I suggest reading through the relevant parts of the manual. You will need to tweak the above and should probably do this in a test set of folders/files before using it on the real thing.
Site Admin
Posts: 8697
Joined: Sun Sep 03, 2006 1:30 am
Location: New York City

Return to Support