CAREER & SKILLS SYNOPSIS:

Expert capabilities: VBA Excel 365 (and prior) HTML 4.01 XHTML CSS 2.1 XML (incl. DTDs & Schemas) JavaScript RDFa & Semantic Web markup SharePoint/MOSS (end - user) VBScript

Advanced capabilities: C# .NET 4.52 (and prior) Visual Studio 2017 (and prior) WPF (incl. XAML) SQL Server 2012 (and prior) T SQL ODBC Expression Blend VSTO Entity Framework 6 Windows Workflow 4 Prism 5.0 Windows Forms Access XPATH & XSLT Word Visio

Intermediate capabilities: Python VB.NET PL/SQL UML HTML Help Workshop Sandcastle

EMPLOYMENT:

Confidential, Kearny, New Jersey

Head of Supply Chain (Consultant/Contract)

Responsibilities:

• Significantly reworked existing Google Sheets model for scheduling planting of approximately a dozen crops, factoring in each crop’s germination, transplanting, and harvesting cycle, while tracking and accounting for multiple capacity constraints.
• Ported one version of the Google Sheets model to Excel, directly linking to company database tables on Amazon Redshift, thereby eliminating existing manual updates of identical information in multiple worksheets.
• Created a macro- and pivot table-driven Excel workbook which allows for much more granular analysis of daily adjustments to planting schedules.
• Worked with data and sales teams in revising planting scheduling. Participated in daily operations stand-up meetings. Assisted sales team in refining Google Sheets forecasting model.
• Researched use of Sheets API for automation with Python.

Confidential, New Jersey

Developer (Consultant/Contract)

Responsibilities:

• Led a small team charged with finding efficiencies within Confidential Team procedures. Created a VBA/Excel tool (Template Mechanic) which provides a streamlined architecture for updating more than two dozen Excel templates maintained by entire Confidential Team on behalf of several hundred analysts in C&W’s Valuation & Advisory business.
• Devised a highly automated method for processing change requests, enhancements, and bug fixes in the templates, allowing the Confidential Team to begin reducing a backlog of some 900 such TFS items. Implemented Strategy Pattern via two custom interfaces, with one interface itself invoking the second.
• Constructed a generic class, with extensive comments and stubbed-out code, allowing Confidential Team developers to efficiently create error-correcting classes, which can then be readily swapped in and out of the Template Mechanic VBA project. Implemented the secondary Strategy Pattern interface in several helper classes.
• Designed main module with an outer loop to open to-be-fixed workbooks, an inner loop to invoke each error-correcting class, and an extremely thorough error handling and event-logging architecture.
• Created a 30-plus slide Power Point demonstration to explain implementation details to the group. Directed and oversaw creation of a parallel Template Mechanic in Word. Wrote a .NET program (C#) for creating and maintaining requisite folders on developers’ local machines.
• Created an Excel VBA add-in that communicates with VSTO add-in of analysts’ valuation workbooks, handling any errors returned (via a *.csv file) by Finalize, a functionality which pushes data from workbooks to two centralized databases. Participated in designing solution.
• Created two interfaces for implementing Strategy Pattern, which resulted in more modular, maintainable code.
• Created a variety of modules and classes.
• Coordinated development with off shore .NET developer on a daily basis for code development, testing, and debugging.
• Participated in weekly team meetings, and various ad hoc meetings, with approximately half-dozen managers and stakeholders.
• Created proof-of-concept (POC) for substantially reconfiguring, and speeding up, the existing, extremely slow, Associate & Send VSTO procedure. Wrote first pass of POC in VBA, demonstrated general feasibility of connecting analysts’ highly complicated Excel workbooks (typically 5+ MBs) with similarly complicated (typically 200+ page) appraisals, replacing inside the Word document embedded picture objects of 100+ Excel charts with via Confidential links, and some 10,000+ Word docvars with Confidential links to named cells in Excel.
• Subsequently converted VBA code to independent VSTO (C#) add-ins for both Word and Excel.
• Created various small-scale POCs involving Azure, MSSS, SharePoint, and .NET.

Confidential, Gladstone, New Jersey

Developer (Consultant/Contract)

Responsibilities:

• Rebuilt two failing Excel 2016 workbooks utilized by Confidential to generate quarterly 200-300 slide PowerPoint client presentation.
• Refactored virtually all VBA code, introducing global- and module-level variables, numerous error traps, and self-commenting code.
• Eliminated multiple instances of redundant, non-functioning, and zombie code, and replaced magic numbers, strings, and range references with named constants, variables, and references.
• Refactored and repaired an Excel 2016 workbook which generates exhibits for quarterly PowerPoint documents.
• Operated under agile methodology, logging all issues, projects. and activity in Pivotal Tracker.

Confidential, Secaucus, New Jersey

Developer (Consultant/Contract)

Responsibilities:

• Implemented major fixes to the company’s Price Management Tool (PMT), “a” macro-enabled Excel workbook utilized by internal merchants, price execution team, planning department, and others. There are approximately 3 dozen copies/versions of the tool active at any one time - all are binary-format (*.xslb) files, between 5MB and 15MB each.
• Built a macro-enabled Excel workbook (dubbed the Cognos Automation Tool) which opens each PMT over the weekend and fires each PMT’s slow-running Cognos data-download macro.
• The tool performs 4 data-integrity checks on external Cognos/SAP files themselves, and an additional 4 checks on each PMT, before running the PMT’s data-download macro and/or saving the updated PMT.
• The Automation Tool is highly configurable and user-friendly, with virtually all worksheets containing editable cells for all top-level parameters the business might want to modify.
• Originally developed a version of the Automation Tool entirely in VBScript. However, just before implementation IT management decided that it wanted any Cognos Automation Tool to run under Windows Scheduler, rather than under UC4 Scheduler, thus forcing redevelopment from VBScript to the Excel dictator workbook architecture.
• Wrote a 15-page user guide (including multiple annotated screen shots) for the Cognos Automation Tool.
• Altered major VBA module in PMT(s) to hide 2 dozen columns (business was unwilling simply to delete columns), update the module’s process of recopying formulas throughout target worksheet, and introduce previously non-existent error traps for users who elected to update from an external PMT.
• Refactored remainder of module, significantly increasing execution speed.
• Built Conflict Resolution capability for PMTs, consisting of a new user form, RibbonX buttons, and several modules and classes. Conflict Resolution automates the previous manual (and thus laborious and error-prone) process of identifying nine separate conflicts (e.g., that a putative mark-down price is higher than original ticket price) which must be resolved before PE team uploads pricing data from PMT to SAP.
• Constructed a macro-enabled Excel file which allows users to add, remove, or replace VBA classes, modules, and forms for all Excel files in a target directory.
• The tool was developed to facilitate and automate the process of updating VBA code across the 3 dozen PMTs, though it can readily be modified for more generic use.

Confidential

C#/T-SQL Developer

Responsibilities:

• Each record in the database represents a single data point from a group of MS Project files (populated by an existing .NET app), and the produced Excel files are user-designed spreadsheets summarizing status of various projects.
• Created several MSSS stored procedures to be invoked by the .NET app, including one which takes 2 parameters and includes over 50 inner joins. Built 2 datasets, each with multiple datatables.
• Much of the code involves constructing the datatables (which ultimately became the individual Excel worksheets), including extensive datatype conversions (all raw data in the MSSS db were stored as varchars).
• Created abstract ‘WorksheetCreator’ class, allowing efficient program looping and compact code.
• Extensively refactored inherited code.
• Utilized TortoiseSVN (Apache Subversion) for version control. Worked on-site at Confidential, interacting daily with project manager.
• On an internal, Confidential project created an Excel VBA project which utilizes Selenium to automate the process of downloading quarterly bank holding company data from FFIEC and Chicago Federal Reserve websites.
• Wrote a second VBA project which, after importing the downloaded text files into Excel, “transposes” each data set from its 500 row by 4,000 column format into a “flat file” - i.e., created worksheets where each cell in the raw data file becomes a single Excel row, suitable for importing into a database.

Confidential, Princeton, NJ

Developer (Consultant)

Responsibilities:

• Utilized code-first approach (C#, EF 6), with business model employing 3 abstract classes, then concrete classes deriving from 1 of the abstract classes and implementing 1 of 3 custom interfaces.
• Business model also includes custom enums, 3 classes that will serve as complex data types within MSSS (with concrete classes holding ICollection properties of these types), one-to-many relationships, and many-to-many relationships.
• Employed a mix of data annotations and the Fluent API to create MSSS tables.
• Worked in Visual Studio 2013 and Team Explorer.
• Prior to building MSSS database built full solution for DB’s report-generation strictly using VBA and Excel.
• Employed RibbonX customization in report-generating workbook, with VBA code-behind employing custom enumerations and types, and multiple modules, subs, and functions. Wrote user manual.
• Participated in manual remediation of hundreds of DB Excel reports as part of DB’s conversion from Confidential River data to Black Rock data.

Confidential

Developer in Tax Technology department (Consultant)

Responsibilities:

• Created POC of .NET capabilities as alternative to department’s reliance on Appian for MSSS front end, including the creation of a multi-table database with foreign key references, views, and stored procedures. For front-end development created a WPF desktop app, utilizing Visual Studio (VS) 2013, with majority of WPF design done as XAML mark up (vs. Design pane). Wrote code-behind in C#, incorporating Entity Framework (EF) 6, and Prism 5.0. Completed VS solution incorporates multiple modules/projects, MVVM design pattern, dependency injection principles, unit tests (NUnit), and Git for version control.
• Built VSTO POC project in VS to display ADO.NET capabilities for CRUD operations against a MSSS database from within Excel. Also utilized WPF to create customized, UserControl buttons, which were hosted on a document-level Action Pane. C# code uses connected-layer of ADO.NET. Employed ClickOnce deployment of POC to shared/network drive.
• Developed “relational staging layer” (BRD) Excel tool for transforming feeds from Clarity (IBM) templates into a *.csv-file format suitable for loading into CART (Consolidated Analysis & Reporting Tool), an Oracle Essbase-based application. Reformatting entailed creating a single, flat-file record for each cell of large source tables. Each such record contains the amount found in each source-table cell, plus twelve ‘dimension’ fields, populated from a column heading, row label, or worksheet-wide value. Placed controls for user interaction in RibbonX menu. Wrote code so that RibbonX controls are enabled in sequence as user completes requisite data-processing steps. Business logic behind tool is highly complex, with final VBA exceeding 250 pages. Completed the project in less than four months.
• Handled Excel-to-Oracle (TOAD) data-load process within large-scale data conversion project. Initiated and created macro-enabled Excel template that was run as the preliminary data integrity layer for the project's data team. Further initiated and wrote numerous PL/SQL stored procedures, and one custom function, in Oracle as secondary data integrity layer. Utilized inner and outer joins, views, temporary tables, and unions in constructing stored procedures. Macros and procedures eliminated virtually all data issues experienced in the project. Edited and maintained other stored procedures used in the data-load process, as well as numerous SQL*Loader control files.
• Developed both Excel front-end and MSSS back-end components for an application that centralizes tracking of IRS requests for documents, replacing ‘data silo’ system of ad hoc Excel workbooks. Designed back-end on Access 2007 before migrating database to SQL Server 2008. On MSSS built a number of views and parameterized queries for DML and ETL requirements. Built one Excel 2007 workbook template for front-end data entry, plus a second, read-only workbook for managerial reporting, hosting both workbooks on SharePoint 2007. Included RibbonX customization on managerial reporting workbook, using Visual Studio 2008 to compose and edit underlying XML.
• Substantially rewrote VBA code in a SharePoint-hosted Excel template/file being used by roughly 50 CPAs in Confidential ’s tax department. Revised code automatically syncs 10 SharePoint properties with values in the file itself. Existing version had required duplicate data entry (Excel file and its SharePoint properties). VBA code also updates and recalculates certain CorpTax formulas embedded in the Excel template file. Wrote accompanying user guide.
• Loaded trial balance data into CorpTax. Reformatted user-submitted Excel financials then loaded data sequentially into Access, Oracle (TOAD), and CorpTax tables. Wrote ad hoc Access queries and SQL procedures (in TOAD/Oracle) to handle user resubmissions and data errors.