Problem Customer credit decisions are partially based on credit report data. The bank performs "soft data pulls" from the credit reporting agency 1-2 times each year. The bank found it difficult to sift through the current and previous report files to understand how customer data had (or had not) changed. Solution The fix for this common problem is to concentrate all of the soft pull data reports into a single database and to query the database to answer the bank's questions. When a new report is pulled, the file is placed in the same location as the others and the database is refreshed with a single click. Some questions addressed by this solution included: How has customer X's credit trended Which of my customer's credit has dropped more than X points recently Which of my customers currently have tax liens or changed their address Which of my customers have a new credit freeze? Tools Used Power Query in Microsoft Excel

Problem Over many years, dozens (hundreds) of weekly "loan status" report files were created and saved. Each file is a snapshot of the credit department's active loans. At the time, it was not possible to find how a loan had changed over time or to simply see an overview of oustanding loans, by type, by credit score, etc. Additionally, because of the occasional format change, there existed three different report file formats. Solution We were able to concentrate the loan data from all report files (those already archived and new ones as they are generated), while being flexible in order to handle varying number of loans in each file. All loan data - from each report file format type - was appended into a single database that could be queired, analyzed and reported on. New ability to create trends and summarize month-over-month and year-over-year changes was added. Tools Used Microsoft Excel, Power Query, Pivot Tables

Problem A document tracker coordinator was tasked with sending project document-tagged emails to team members depending on their role in the project. Each one of the several hundred documents for the current project could have different recipients. Further complicating, each email is either seeking Review or Approval of the referenced document but may also be for Information only. If Approval is needed, a deadline must be included. Solution DREW (the "Document Review Email Writer") to the rescue! Emails are generated directly from Excel, based on a project role distribution table. There are adaptations for including a calculated (or custom) due date, a custom subject line and a custom body message. A second user form manages the Excel workbook settings for transparency and simple updating to the next project. Tools Used Microsoft Excel, Microsoft Outlook, VBA, Excel Tables

Problem Using Excel's built-in solver on one problem is simple. Changing the target and input ranges to solve a second problem on the same worksheet is a hassle. Changing these settings for 7 problems slows research to halt. Updating the solution to an eight (8) component, physical property model of solid-liquid equilibrium required just that. Solution We used VBA to programmatically change the inputs to (and then run) each mini problem/solver block. If a solution was not found, a second "Perturb" routine quickly modified the original guesses by a small amount so the solver could run again. The time to solve the 7 solver blocks was reduced by 95%, enabling additional, deeper research. Tools Used Microsoft Excel, VBA

Problem A cost curve is only as valuable as the underlying data is accurate and current. The owner of this data found the process to update the existing cost curve to be extremely difficult, suffering from multiple file versions, hard-coded numbers and no single version of the truth. Solution Created a flexible "Cost Curve Generator" where each type of input data had its own table. Cell dropdown options were used to manage user selections and a formating table was used to set which data to include in the chart as well as the fill color. Data is now simple to update (and only has a single source) and charts are easily ported for use in PowerPoint or other report. Tools Used Microsoft Excel, VBA, Excel Tables

Problem The workflow of this client required repeated use of newly generated text files. The step of processing the text files would break if it encountered a blank line. Before this tool, the blank lines had to be removed manually. Solution A simple Excel add-in was created to clean the text files (processing two files simultaneously, per the user's request) by removing all blank lines. Additional features of remembering the last file (because newly generated files often have the same name) and the ability to open in Excel or Notepad (easy confirmation the cleaning worked), makes this new workflow a breeze. Tools Used Microsoft Excel, VBA

An Excel add-in brings new functionality to Excel that isn't tied to any single file. Some of the add-ins previously created include: - A steam table calculator - A report cleaner - A chart creator - A data retriever - A CRM simplifier

These custom applications were created using Python: - Text file transformation for physical property data transfer, enabling mechanical equipment design - Policy file reader to create a calendar of stated tasks - Software license monitor to facilitate collaboration and sharing of limited network seats - OCR of old, monthly reports to unlock data from PDF files