Developer in Excel & Access VBA

RAYMOND HSU, J. D., MOS*Candidate

*** *. ********* *****, *******, New Jersey 07652

adbd19@r.postjobfree.com d:201-***-****

Linkedin Profile: http://www.linkedin.com/pub/raymond-hsu/45/249/242/

Microsoft Excel Power BI / Database Solution Developer

MS Excel 2013/16 VBA (Expert), MS Access 2013/16 (Expert), Power BI, Power Pivot,MS T-SQL, SSRS, SSAS, SSIS

IT professional developer with 15 plus years of success devising innovative and tailored automation and database solutions using Microsoft technologies to meet the ever-changing business requirements in the pharmaceutical, insurance and finance industries. Candidate has extensive business training in accounting and finance and is a holder of a J.D. degree.

Professional Experience

Global SmartExcel Application Developer (ongoing project enhancement) Sep 2020 to present

PepsiCo – Valhalla, New York (off-site, part-time)

Direct vender f r PepsiCo to support, maintain and enhance their TSG Innovation and Productivity checklists. Please refer to the PepsiCo projects listed below

Microsoft Access VBA Developer

Mutual of America – New York, New York July 2019 to August 2019

A short-term (5 to 6 weeks) project to enhance the reporting platform of the client’s front-end database in the Information Security division. The Microsoft Access database is linked to Mutual Of America’s (“Client”) IBM DB2 enterprise database, which contains, among other things, client’s corporate customers’ pension plans for their employees.

The report enhancement project focused on creating additional reports based on various business perimeters with the resulting datasets be converted to csv format. The text files would then be sent to the IBM DB2 for processing/updating. A parallel processing in the client’s active directory also has to be performed via vbscript. All these processes were written in VBA code within the Access environment.

The report automation enhancements were created to replace manual updating performed by the staff members that the information security division has to be conducted at a set interval. The estimated savings in labor hours has been put at 100 to 150 hours a month and the error rate cut to 0.05% per hundred.

Created C# applications to automate the process of transferring weekly data from clients in Excel spreadsheets to backend databases.

Excel VBA Developer

Cushman Wakefield – E. Rutherford New Jersey Aug 2018 to June 2019

Hired as a member of a large VBA developer team (about eight of us) providing support to Global Template Tool (“GTT”) users numbered in 300 plus. These users are licensed appraisers and financial professionals in one of largest global Real Property full service company. The end users/clients of Cushman Wakefield are some of largest banks, financial institutions, private equity funds. Revenue from the appraisal operation of Cushman Wakefield exceeds 448 million (5.5% of its 8 billion plus report revenue in 2018).

Duties included providing technical support in the development of Excel and Word templates. These development tasks

comprised of adding enhancements to the existing Excel and Word templates, developing new templates for new markets. Enhancements entails the developing of complex financial formulas, concatenation of texts and paragraphs in grammatically correct fashion and coding in VBA to achieve and bring about the objectives of business requirements in the report functionally, fulfillment of governmental and industrial rules and protocols.

Day to day responsibilities also involved the maintenance of the existing codes, bug-fixing and addition of new functionalities and business requirements via the use Team Foundation Server. Collaborating with off-shore Quality Assurance teams and others to insure the development work was thorough tested through a full battery of regression testing, sanity testing, etc. before pushing to the production phase.

Skillsets used on the job, besides extensive coding in VBA in Excel and Word also included C# and VSTO for creation of COM add-ins and additional enhancements such as the Microsoft ribbons, right-hand panes in Excel and Word, styles in Word, content place holders, bookmarks in Word, creation of Excel add-ins and customary VBA coding to send Excel tables, integrating and transferring named ranges to “docvariables”, copious deployment of Word Content Controls to fix the placements and to set placement perimeters of fragments (small Word documents in size within a contain Word document) and some use Power BI and Power Query and Microsoft Access 2016.

Used C# extensively to automate functions of transferring data, graphs, charts and others as a COM object and to perform retroactive update of Excel and Word documents. Also developed various business rules embedded in COM module created in C# to be called and used by all company-wide Excel and Word documents.

Sole Developer of Pharma Science & Technology Financial Analysis Access database app

Celgene – Summit New Jersey Mar2017 to Apr 2018

Sole contributor and developer of a MS Access-based database with split configuration for the finance department to be used by 90 plus scientists and analysts monitoring and forecasting expenses incurred for clinical trials at the compound level for research expenditures and invoices billed by third-party vendors and for generating ad hoc and canned reports comparing the expenses with the forecasts and budgets and display variances for each compound in clinical trials.

The reporting platform utilizes Microsoft Power Pivot and Power BI technologies to compile diverse data sources via the use of pivot tables into datasets that drive that reporting mechanism. Also created stand-alone windows based applications in C# to call the reporting functions in Microsoft Access to reduce network traffic.

Developer of Technical Star Gate Productivity Checklist Mar2016 to Feb 2017

PepsiCo – Purchase, New York

Recommissioned by PepsiCo to continue the development and enhancement of their global Technical Star Gate (“TSG”) Checklist Access/Excel tool for the Productivity component of the company wide implementation of the application.

The Productivity Checklist Tool is to track and monitor that new products comply with the company policies and departmental protocols in each stage of their development from conception to launching into the markets. This Access/Excel management tool is to be used globally as a de facto blueprint for all PepsiCo products so that a uniformed and consistent product launching framework may be achieved and the process can be monitored, analyzed and studied to achieve greater efficiency and minimize duplication of efforts among different PepsiCo users, stakeholders and company groups.

The Checklist application serves as a self-contained management tool to store the progress information of each new product during the different stages of its development. It allows the management and different development teams to quickly share information, to create timelines of the launching of the new products, and to prioritize resources based on the launch-readiness of the product lines. The information collected for each product through the Checklist tool will also be used to speed up the process of bringing other similar products into the markets.

Access VBA Promotion Planning Tool Developer with Microsoft Access database

as the backend (2nd Project) Oct 2015 to Feb 2016

Pernod Ricard USA- New York, New York

Recruited again to continue the development of a global promotion planning tool (“PPT”) (using Excel as the frontend interface and Microsoft Access as the database) that features rich user interface to retrieve, edit and save data to Access and then permanently to the company’s Oracle enterprise database. I was the sole code contributor and developer of PPT VBA application used as a frontend with rich user interface that enables 250 plus users to enter, edit and store promotion campaign spending information and activation tactics, etc. at the sub-brand level for the upcoming fiscal year. The application also has multi-faceted reporting function that gives the users various report options from top level strategic planning to week by week operation at the marketing and sales level. These reporting functions include extracting data to compare the target spending versus the actual spending, measuring variances and key performance indicators on a quarter by quarter and prior year basis.

Besides writing VBA code to run the Access and Excel applications, I also developed query code in the Oracle environment. I was instrumental in writing queries and codes for generating reports, matrix for comparison for the management teams and division heads. From concept to production rollout, I worked closely and daily with Pernod Ricard IT project manager and members, its Oracle database administrator team and company subject matter experts in the planning, testing, QA processing, UAT and production stages. The production version went live before the scheduled dates and the performance has met and exceeded many of the preset goals and objectives of the project. The PPT application has and continue to receive many positive feedback from the users across the marketing, financial and sales disciplines as well as the upper management.

Global SmartExcel Access Application Developer (2nd Assignment) Jun 2015 to Oct 2015

PepsiCo – Valhalla, New York

Sole contributor and developer of an Access/Excel VBA application to track and monitor that new products comply with the company policies and departmental protocols in each stage of their development from conception to launching into the markets. This management tool is to be used globally as a de facto blueprint for all PepsiCo products so that a uniformed and consistent product launching framework may be achieved and the process can be monitored, analyzed and studied to achieve greater efficiency and minimize duplication of efforts among different stakeholders and company groups.

The Access/Excel VBA application serves as a self-contained management tool to store the progress information of each new product during the different stages of its development. It allows the management and different development teams to quickly share information, to create timelines of the launching of the new products, and to prioritize resources based on the launch-readiness of the product lines. The information collected for each product through the tool will also be used to speed up the process of bringing other similar products into the markets.

Access/Excel Promotion Planning Tool Developer Dec 2014 to Jun 2015

Pernod Ricard USA- Purchase, New York

Sole code contributor and developer of an Excel VBA application used as a frontend with rich user interface that enables 200 plus users to enter, edit and store promotion campaign spending information and activation tactics, etc. at the sub-brand level for the upcoming fiscal year. The application also has multi-faceted reporting function that gives the users various report options from top level strategic planning to week by week operation at the marketing and sales level. These reporting functions include extracting data to compare the target spending versus the actual spending, measuring variances and key performance indicators on a quarter by quarter and prior year basis.

The promotion planning tool (“PPT”) uses Access as its database storage platform and data is further rolled up and store in the company’s Oracle enterprise database. I was instrumental in writing queries and codes to store and extract data between the applications (Access and Excel). From concept to production rollout, I worked closely and on a daily basis with Pernod Ricard IT project manager and members, its database administrator team and company subject matter experts in the planning, testing, QA processing, UAT and production stages. The production version went live before the scheduled dates and the performance has met and exceeded many of the preset goals and objectives of the project. The Excel application has and continue to receive many positive feedback from the users across the marketing, financial and sales disciplines as well as the upper management.

Excel Global Usability Application Developer Jul 2014 to Dec 2014

PepsiCo – Valhalla, New York

Sole contributor and developer of an Excel VBA application to track and monitor that new products comply with the company policies and departmental protocols in each stage of their development from conception to launching into the markets. This Excel management tool is to be used globally as a de facto blueprint for all PepsiCo products so that a uniformed and consistent product launching framework may be achieved and the process can be monitored, analyzed and studied to achieve greater efficiency and minimize duplication of efforts among different stakeholders and company groups.

Lead Database & Program Developer (a Pfizer project) Jun 2013 to Jul 2014

Pharmalink Consulting – Short Hills, New Jersey

Lead role for the design and development of a database solution with rich graphic user interface to support a team of 25 users in the SharePoint environment to record, track and monitor the various processes and facets of a pharmaceutical project. The core data reside in 245 Excel workbooks and, through the VBA automation function, the workbooks are consolidated into one large Excel file. The automation process of the Excel application also standardized the different number of columns among the 245 workbooks and drove the data into the dashboard and metrics displaying key performance indicators. It also has a timer function to pre-set time to generate and send via email weekly reports to designated group of users.A`QQ

Engaged and contributed in the project’s modeling and workflow sessions. Successfully deploying an architectural solution for the Microsoft database in a SharePoint network platform that optimizes the database efficiency and speed and facilitates the simultaneous access by multiple users. Sole contributor to the rapid development and implementation of the database solutions to track and monitor the team’s work load and performance via the creation of Excel dashboard and the use of key performance indicators (“KPIs”) and various reporting functions to meet the requests of the clients, stakeholders and the team leaders and supervisors.

Developed and continue to develop automation solutions using Microsoft Excel and Access applications and to meet the reporting and analytics requirements of the team leaders and supervisors in the day to day business operations. Successful in implementing business rules to govern the workflow and to streamline the work-in-progress among various project teams premised on Visio flow charts and business requirements provided by Pfizer’s conformance officers. Achieving outstanding database response time over the network environment and SharePoint / Citrix platform by optimizing the data load via a backend/frontend database configuration.

Contract Developer on MS Access Database Application with Excel Oct 2012 to Apr 2013

User Interface.

Pfizer Animal Health –Madison, New Jersey

Sole Contributor and developer of a pricing model application (“PMA”) using Microsoft Excel 2007 in the implementation of a global pricing scheme. The stand-alone Excel pricing and reporting application enables users to retrieve and download past historical sales data in units, gross and net sales volume, gross and net prices and cost-of-goods-sold values. Through the user interface in Excel forms, users may select and filter desired business categories and download datasets to Excel spreadsheets. The application then can calculate the optimum pricing increases to achieve a user-defined projected sales volumes, break-even number of units, and many other statistical features to assist management setting price movements at the SKU level of each product for the upcoming sales cycle.

I was also working on the proposed development of a data warehouse architecture and system workflow featuring MS SQL Server 2008R2. The pricing scheme acts as the launch pad of a large data warehousing project tracking and archiving fact transactions and entity contexts so that precise purchase behaviors and seasonality of sales may become available to the various marketing staff to achieve optimum profitability. A migration feasibility study from MS Access to SQL Server 2008/2012 was drafted using the MS Upsize Wizard. Once the upsizing/migration has completed, the proposed database warehouse model will use MS SQL Server 2008R2 and SSAS technology to develop cubes for data mining and establishing KPIs displayed in MS Excel.

Independent Consultant on Database Development Mar 2011 to Oct 2012

eBizSolutions.com – Paramus, New Jersey

Consulting and participating in the creating, developing of a litigation support application for law firm uses. The application stores evidentiary documents in various formats, contains calendar feature to schedule, track and project different phases of the case progress, maintains a comprehensive client billing system, reports real-time costs and expenses for user-input time period, and many other cutting-edge features, such as case budgeting, cost project, analyses of venue selection and jury profiling. (Details of the application are confidential and proprietary).

Report Analyst/ Supervisor Jan 2002 to Feb 2011

Sompo Japan Insurance Company of America – New York, New York

Supervise, develop and maintain company-wide business intelligence reporting functions for various departmental, external entities and upper management use. Create Pivot Table reports and analyses using MS Excel 2007 & 2010. Responsible for overseeing the migrating of legacy Excel files with add-ins, macros, modules & DNS components to the new Excel 2007 and Excel 2010 versions. Design and implement company-wide graphic user interface BI applications for extracting data results from company back-end databases and made available to end users via front end programs using Visual Basic, ASP, Microsoft office applications and others. Use ODBC technology and MS Office Query to extract and distribute real time business intelligence and company data from the back end data sources to the front-end company users.

Key Achievements:

In 2011, created, implemented and tested the data-retrieval end of a dynamic business intelligence web application using MS SQL 2008 as the backend database and ASP codes for programming the application logics and database retrieval. I played the key role in the “ETL” process and in the normalization of the relational for optimum performance over the network and in query execution. Working with the webmaster using Adobe Dreamweaver CS4 as the interface for the generation of web pages. The application enables our insured account Honda Motorcycle of America to view and retrieve loss data and number of claims and their dollars amount among their hundreds of motorcycle dealers/warranty repair services in the U.S.

The application can also perform drill-down analysis on a real time basis at the web and to download and produce business intelligence reports in Excel 2007 & 2010 format with many GUI (“graphic user interface”), e.g. Microsoft form controls to enable the end-used to further perform quantitative analysis in charts, graphs and financial summaries.

oI was the project leader of a team of three, consisting Sompo’s database administrator, a webmaster and a consultant. I was instrumental in the conceptualization of the application and the lead contact person with the client’s IT members.

oAside performing the database logic programming, I also conducted the “ETL”, the cleaning and scrubbing and mapping of the client’s data in conformity with our back-end IBM AS/400.

In 2010, assisted the IT department staff in developing, implementing and verifying various data-retrieving algorithms and ad hocqueries, dash-boarding when the company migrated to Oracle’s business intelligence (“BI”) application in 2010 and collaborate with IT team leaders using MS SSRS to test and develop reporting applications.

oI was the testing programmer to insure the SQL queries and resulting dataset of migrated applications conform and yield consistency to our requirements and the IBM AS/400 archival databases.

Education & Credentials

MCAS (“Microsoft Certified Application Specialist”) October 2014

Candidate for certification

Completed the training and need to take the Exam#77-600

SetFocus IT School – Parsippany, New Jersey Aug 2012 to Oct 2012

Completion of Business Intelligence Master’s program

Program consisted of the study of T-SQL 2008R2, SSIS, SSAS, SSRS,

MDX & Data Warehousing and more

*(“MCITP”) certification candidate (Exams 433 & 451)

Dover Business College – Paramus, New Jersey Jun2000 to Oct 2001

Certificate of Computer Programming & Database Development

Graduate School of Management at Rutgers - Newark, New Jersey Feb 1999 to May 2000

Master of Business Adm. in Finance& Information Technology

MBA Degree candidate

New York Law School – New York, New York Sep 1985 to Feb 1989 J. D. Degree

Rutgers University at Newark- Newark, New Jersey Sep 1983 to May 1985

Bachelor of Arts, Major in Accounting

Contact this candidate