Auto CMO - Excel Tool Replacement Design Challenge

Welcome to Auto CMO - Excel Tool Replacement Design Challenge!

The purpose of this tool is to take the bond probe file (an input file containing security information) and display it in a customer web application so that we can identify securities that are not currently included in the bond probe process and monitor them to determine if they should be converted into the bond probe process. This is determined based on the market value of the securities and if they are priced below $xx.xx (a configurable amount). After securities are tracked to be pricing below $xx.xx for 30 days. If the security should be converted into the bond probe process (that process takes place outside of this tool). We use the output of the tool to track the responses and the required dates for quarterly follow ups.

Previously we ran a wireframe challenge to build the screens needed for this tool and you will be provided with the wireframes we built.

Note: Please do not submit a colored version of wireframes as they are just meant to guide you, outline necessary screens, and further explain the application basics.   You are highly encouraged to enhance the wireframes with your own design style / ideas / solutions during this design challenge.

Round 1

Submit your design for a Checkpoint feedback:
1. Login.
2. Home (Analyst Role).
- If you have time - please provide us with a click map for your design.
- Readme.jpg : Provide notes about your submission.
- Make sure all pages have correct flow! Use correct file numbering. (00, 01, 02, 03).

Round 2

Final design plus Checkpoint feedback:
1. Login.
2. Home (Analyst Role).
3. Home (Admin Role).
4. Home (Read Only Role).
- If you have time - please provide us with a click map for your design.
- Readme.jpg: Provide notes about your submission.
- Make sure all pages have correct flow! Use correct file numbering. (00, 01, 02, 03).

Challenge Description:
The goal of this challenge is to take the existing excel tool and convert it into a web application.

We are looking for the [topcoder] design Community to help us with planning our new client  "user experience" (UX) when interacting with Topdocer projects and challenges.

Design Goals:
- Your design submissions need to be able to drill down for all page flow.
- Show your design solution for Desktop view (width 1280px and height as required).
- Feel free to suggest any tools/functions that can bring good User Experience for this tool.

Supporting Documents:
- Wireframes (Wireframes.zip).
- Screen shots of the process (screenshots.zip).
- Branding Guideline (forec-light.css); we will provide a better branding guideline with checkpoint feedback.

User Roles:
- The application is required to have an Analyst role and a Read-Only role.
- Optionally, an Admin role can be added.

Design Consideration:
- The daily input file is large (around 20 K records). Your tool will need to have tools for viewing the large data sets, but also filtering down with minimal effort to the critical records.

Current Tool Description:
- An ASCII file in delimited format is delivered to the LAN by an internal application. The users open the Excel tool and hit the browse button. They will then navigate to the LAN folder, which is mapped as a drive on their system. They select the file to be processed (it will be for the prior business day as it is an overnight file). Once the file is selected, they hit the “Pull Data” button. This will parse the file and load the data in to the “Staging” tab and the “Import” tab. All headers etc. are created dynamically. The data in the “Import” tab is not used by the operations team. They only work on the “Staging” tab. The steps they go through are:
1. Select row #2 and enable Excel filters (“Data -> Filter”). Please refer Screenshot1.png.
2. Navigate to Column C (“99-20 Category”) and from the filter drop down select Securities that are marked as “NEW 99-20” and “NOT 99-20” and hit the “OK” button. Please refer Screenshot2.png.
3. Navigate to Column O (“Market Value Price”) and select all prices that are below 80 dollars. Please refer Screenshot3.png.
4. Manually highlight each unique Security with a different color (specific choice of color is not important, just that they be unique for easy identification) Please refer Screenshot4.png.
5. Comments are then manually added for each of the securities in the same color as the Security for easy association. Note that if a Security has shown up in the same list for multiple days, then the same color is applied for easy association and the number is manually incremented by 1. For ex: If the prior day comment for a given Security was “Pricing below $xx.xx day 4 of 30 – monitoring this”, if it was below $xx.xx the following day too, the Analyst comment would be “Pricing below $xx.xx day 5 of 30 – monitoring this”.
6. The Analyst will follow up with the Treasurer’s office for all Securities that hit the 30 day threshold on whether they should be “converted” (a separate process). The Treasurer’s office may ask to continue tracking and follow up by a certain date. The Analyst would track that in the comments section. As each day a new instance of the Excel Spreadsheet is used, any comments are manually carried forward (by opening the prior day’s instance of the spreadsheet and copying / pasting.
7. Once the manual follow ups are completed, the Spreadsheet is saved to the LAN folder. This is a manual step.

Things to fix in the wireframe:
- When the security is expanded and we show the detail rows with all the funds that hold the security, we should include the market value price of the security since that is a key field to which the analyst pays attention.
- We should swap the position of the Field / Value pane and the Comment History pane. In other words the Comment History pane should be on the top and the Field / Value pane should be below it. The analyst will almost exclusively look at only the Comment History pane with the additional fields being looked at in the event of some additional research.
- We need the filter tab to expand out the data tab like the calendar.

Required Functionality:
1. Login:
- Please refer to login.html.
- Each user needs to sign in by filling:
-- Username.
-- Password.
-- Forgot password link.
-- Remember me checkbox.
-- Login button.
- Single Sign on should be supported.
- Please show error styling.
- Please show forgot password recovery. (forgotpassword.html)

2. Home (Analyst Role):
- Please refer to home.html.
- Please capture welcome {username}, notifications and logout at the top of the page.
- User will be able to import file. If this is clicked user will be redirected to add data file page (addfile.html).
-- User will be able to select data file date using a calendar and browse his machine to select the data file.
- The two taps we have here will show same data.
- User will be able to filter the data files and print them.
- User will be able to edit the comments on each row in the table when user clicks the edit icon and save his edits.
- User will be able to expand data file information.
- If you click on CUSIP link you will see the related data to it on the right side.

3. Home (Admin Role):
- Please refer to home-admin.html.
- Same as before but Admin user will be able to control the limit value and add status options by clicking "+" icon.

4. Home (Read Only Role):
- Please refer to home-readonly.html.
- Same as before but user can only visualize data, not edit, print, import...

Important:
- Keep things consistent. This means all graphic styles should work together.
- All of the graphics should have a similar feel and general aesthetic appearance

Target audience:
- Analyst employees.

Judging Criteria:
- Does the design convey the function and use unambiguously and intuitively
- Does it clearly convey what the app is supposed to let you achieve / what problem it is designed to solve.
- Does the design perform the function it was designed?
- Is the design easy to use and understand?

Submission & Source Files:
Preview Image
Please create your preview image as one (1) 1024x1024px JPG or PNG file in RGB color mode at 72dpi and place a screenshot of your submission within it.

Submission File
Submit JPG/PNG for your submission files.

Source Files
All original source files of the submitted design. Files should be created in Adobe Photoshop and saved as layered PSD file, or Adobe Illustrator as a layered AI file.

Final Fixes
As part of the final fixes phase you may be asked to modify your graphics (sizes or colors) or modify overall colors. We may ask you to update your design or graphics based on checkpoint feedback. See more information about Final Fixes.