Case Studies

ENERGY TRADING (ETRM)

• Automated Tedious Daily Reporting

Issue
Routine, Daily reporting is tedious, manual and very time intensive. Client desires to automate daily reporting processes and report building. 

Solution 
› Design and develop Excel/VBA/SQL application to ingest csv reports emailed from broker and auto compare data to queried Allegro (ETRM System) data and report line item differences by product type. 
› Features Include: Dashboard Control Approach, Formula Driven Cross-Check Tie Outs, Issue Reporting on data import procedures, Date Validation Check and Selection, Auto Zip File Extraction, Auto CSV conversion to Excel File Format 51, xlsx, Message prompts to prevent overwriting published files, DATE TIME STAMPS recording when procedures were performed, Formula Drive Date Calculations, User Defined 'Publish To' Locations, Dynamic File Name Creation, Dynamic Local Folder Creation, Auto Hide/Unhide Worksheets, Dynamic Deal Type assignment, Reconciling Items Listing, Timing Differences Re porting, Automatic 'Out Of Balance' warning messages, Dynamic SQL Statement, Failover Connection String, Compiled SQL Statement Capture, Data Import Run Time Reporting, Auto Summary Data Difference Reporting, In-Memory Datasets, Macro Driven Processes, Auto Month End Roll and Worksheet Preparation, Date Validation Checking For Month End Processing, Previous Month Data Retention, Single Excel File Solution for Daily and Month End Reporting, Auto Worksheet Protection, Auto Account Summary Activity Posting and Reporting, Auto Collateral Usage Posting and Reporting, Auto Letter of Credit Utilization Posting and Reporting , Undo/Redo Import Data Functionality, Allegro SQL inaccessible/hidden from user manipulation, VBA project lock down/ inaccessible without password, Next Valid Date Logic to Prevent Recon Date Skipping. 

› Design and develop an Excel/VBA/SQL application with dashboard controlled approach to report Nat Gas Contract Positions from Allegro data 
› Features Include: Dashboard Control Approach, Orderly, intuitive, automated report generation , uto 'New Market' notification, Auto 'File Validation' process, Auto 'Tick and Tie' process between individual reports and raw data , Auto 'Roll Data' procedures, Auto 'Data Archive' procedures, Auto 'Data and Issue' flagging and notification, Template SQL Statement storage, Dynamic Report Date provisioning for the Template SQL Statement, Compiled SQL Statement capture, Deprecated Excel equations, Lookups and other Excel derived data conditions, Utilization of customizations in Allegro, User Friendly Excel Dashboard with controlled, step by step macro processing, User Defined folders to publish PDF report files to, User Defined folders to publish Excel report files to, Excel ADO connection to Allegro Analysis database on SQL Server, FailOver ADO Connection, Auto Formatting of Allegro Data returned to Excel, New Market identification process, Valuation Date Validation Control, Missing Allegro Data Notification for MarketArea, Region, Main Price Index, Marcos utilize VBA7, a standard for Office 2010 through 2016, include Office 365, and future Microsoft Office releases, Speed: This Excel Automation file for Reporting Allegro Nat Gas Positions in completed within one minute, including: Data Retrieval ,Data Dissemination, Error Checking, File Validation, Report Publishing, Automated Trader Recon Positions file creation. 

Benefits 
› Task time for each report dropped from multiple hours per day to minutes per day 
› Data integrity was greatly enhanced through auto-detection of missing Allegro (ETRM System) data 
› This automattion of a previous tedious, time intensive task was a great boost to morale and productiivity. 

Return To Case Studies