Employed as an EXCEL/VBA/ACCESS technical resource in the Business Processes department. Worked autonomously, though the Business Processes manager, liaising directly with the user community, which included director level personnel. First project being the development and implementation of a new “Engineering Capacity Planning Tool” (CapPln.xls). EXCEL was a “given” to support the manipulation and presentation of the data and as volumes are low there was no storage issue to warrant using ACCESS.
The approach, over the first nine weeks, was a weekly design meeting involving interested parties, refining the business requirements and responding to the technical progress I presented on each occasion.
CapPln has four discrete inputs: i) forecast data (project/work-load), ii) historic data i.e. booked hours, iii) input-capacity and iv) competency-templates.
Where project work-load is the sum of the assigned competency templates and input-capacity may be analysed by competency.
The importing and manipulation of data is achieved using VBA and employing the many features of the EXCEL Application object. Extensive use was made of Ranges, VBA manipulation giving precise control over data output for charting purposes (rather than using pivot tables).
A version of the supporting manual for CapPln, which I was also tasked to produce, may be found here (http://jifs.co.uk/demo/EngCapPln%20Tool%20ANON.htm). It contains sections on business-requirements, design, “how-to” and technical.
Following the initial design and build phase came the set-up phase. Being based at Rugby I collated and loaded the data. The Indian and Polish data came in and was, in both cases, formatted differently. It was expedient to modify the load processes rather than insist that they adopt the Rugby standard.
The tool has been adopted and is used by the Engineering Director when reporting to the local senior management team and to the next level up (Europe).
Other significant project is BdgEst.xls – Budgetary Estimating. Instigated by the Tendering Director. To maintain product-cost-basket (pcb) data and report pricing trends on elements within each pcb. This entailed providing the user with a facility to choose a file to load (MS open-file dialog), then process that file – mapping input rows/columns to targets on BdgEst. As with CapPln a bespoke data selection method was employed to feed the MS chart object.
Ad-hoc work ACCESS database – another department were responsible for producing this piece of work. However, they did not know how to auto-fill a form (on load) with a set of pre-determined records, I achieved this using TSQL statements in vba. Other work here was selecting and formatting data to EXCEL for reporting.
Other ad-hoc work includes answering/solving EXCEL queries including issues with pivot tables and charts.

Hewitt Associates (Birmingham)
Summary:
On contract. Using VBA & EXCEL to amend business rules in line with Government legislative requirements. Pension administration system based on Profund. Designed and produced an EXCEL ‘test harness’ to support unit testing. Meeting tight deadlines for code change/fixing to fulfil user requirements; working with in-house technical team. Also using their bespoke development software which provides a partial bridge between business specification and coding. Providing technical support to business analysts and testers.
One-off mini-project:
About six weeks elapsed, four weeks utilisation, worked directly with a user (he provided the requirement) to amend an existing workbook.
Background: a workbook comprises:
• many data worksheets filled by the data-feed, arranged in a hierarchy of e.g. employee data, member benefit data, investment history etc.
• a run-time sheet, on which the user could set the required calculation type and any run-time assumptions could also be set e.g. date of leaving.
• a worksheet called “Calcs” contained all the data manipulations (some functionality was provided by macros e.g. post88gmp, which in turn called a bespoke {referenced} object). Extensive use of identifying cells by range name.
• a “Results” sheet summarised the “Calcs” data for human consumption.
Reverse-engineered the existing functionality by implementing code I included in the ‘BeforeRightClick’ event of each relevant worksheet, which would read the formula in a cell and expand the contents one item per column, along the corresponding row. Thus with rhm click on a target cell, it was easier to see how the values were calculated and explain this to him. He would then tell me how, in light of the new requirements, they should be derived.
He designed the test cases, which I applied to the Test Harness (see above) and ran. I would analyse the results: fix those issues attributed to my error and feed back to the user those that were unexpected along with my interpretation of why.
The final phase of this project was to implement letter production. As might be expected, the changes made to the various pension calculations had an impact on how the figures were presented to the scheme member. A unit wide project was underway to make these changes, however the user’s section wanted them implementing immediately, so I developed a letter-pack production process, which could be triggered from the workbook. (I did check within my reporting line that this was acceptable, it was sanctioned.)
The user provided me with the various letters and highlighted which fields from the workbook were to be inserted. I implemented the process by building Word dot templates for each letter and inserting a “field” for each required item, naming each using the corresponding workbook cell range name. Thus, on running the letter-pack macro a Word object would be instantiated using the appropriate dot template; then for each Field in the Fields object the data was fetched from the corresponding named range. The resulting document was saved as type doc using a name built from the members name and run-date and time. This process is “live”.

January (2007) was tasked with making emergency changes to one client’s workbook – there was a requirement to generate annual member benefit statements, but scheme rules had changed since the last run. It was an intense ten days work, ultimately successful. The HA business team management must have been pleased as they awarded all those involved with a bottle of wine and mine did make it up from Hemel to Birmingham.

Finally:
There were originally five of us taken on in December 2005; I am the last to leave at the end of the month. A point of note- raised my hourly rate by 89% (eighty nine) during the life of this contract.

Studio2OnLine Ltd
SQL Server 2000, PHP
Employed as the Development Manager accountable to the Director, responsible for three programmers and the delivery of web based solutions. My technical contribution: SQL Server 2000 database design with appropriate use of T-SQL, triggers and stored procedures; also preparing technical specifications. Designed, organised and implemented a rudimentary stock ordering system for POS Direct in four weeks. Designed, planned and oversaw the migration of an Access database to a SQL Server db; required designing ‘proper’ keys and cross reference tables to ensure optimum read/write performance.

Compass Software Ltd
Sharepoint, VBA /HTML / script, SQL Server 2000
Compass (part of Torex Retail plc) in Frolesworth (LEICS).
Employed as a Technical Writer. Accountable for maintaining a Sharepoint “library” of development documentation; integrating programmer comments into “.cs” files (to give a consistent ‘look and feel’) - using a VBA app I designed and wrote - to then generate a chm help file using NDoc. Further use of vba to manipulate data within EXCEL and with Word. Attend (code) design meetings and produce first draft documentation.

(2 extensions) contracted to UNISYS at C&G (UFSS mortgage system)
T Plan, VBA, EXCEL, WORD, ACCESS, LINC
A contract LINC Developer. Working from spec and with the author to code and unit-test changes to implement Offset Mortgage processing. Written MRD from user change request to document modification to their Columbus (arrears) data warehouse system; coded and unit tested same.
Transferred to the C&G team responsible for system testing version 9r0.nn migrated code (as UNISYS technical resource). My contribution:-on-site fixing of level 1 or 2 faults. While waiting for the system test cycle to begin provided the (C&G) managers with the utility, as explained below, which permits a tester to create a UNISYS incident form and a TPlan incident record from keying input into one source (Word template and macro). Using VBA created a Word 97 template with AutoNew macro, which enables a tester to raise an incident: simultaneously creating a UNISYS incident form and a raising a TPlan incident record (within an Access database) when selecting the template to create a new document. This is achieved by presenting the user with a sequence of three input forms, which are used to capture key data. Use of drop-down-list boxes is extensive for static data e.g. raised-by. The connection to the TPlan database is provided by selecting the appropriate ‘reference’ and following the information provided in their sdk. Furthermore, created an EXCEL macro to extract all TPlan incidents into a worksheet. This provides the raw data to report on; tabulating data (another worksheet) and then using the chart wizard to present the statistics.

SQL Server 2000, .NET ASP, ADO .NET, VB .NET, HTML, ACT!
A freelance assignment for Combined Knowledge produced an install set (script) which set up the SQL database and website on IIS. Also developed, from an initial simple request, the Week Planner website and database to simulate a whiteboard, as might be found on a planning office wall. In this particular implementation the objective is to provide a calendar view of the current week (columns) highlighting when an engineer (rows) has an assignment (intersection), identified by giving a brief description and location of the job. The whole Job Card may be viewed from here following a single mouse-click

(3 extensions) contracted to C&G, Gloucester
Migration project to version 8R3 UFSS
A contract ‘Systems Tester’ role at the C&G. The project an ‘in-place’ migration of their current 7RP UFSS system to UNISYS’ base 8R3 (LINCII 17). A team member (one of four – one team of several) proving that the migration process specified by UNISYS works! The 8R3 code fundamentally alters the way certain key data are held (although all ‘transparent’ to the user): e.g. account numbers are now held as a nine digit internal number (plus two digit sub reference) mapped to a company specific external format – all parameterised; also, product codes also are now keyed on an internal sequence number mapped to an external reference. Three areas I concentrated on were:
i) producing checking mechanisms to confirm changes were correct i.e. ERGO counts of records and
ii) using the CASL scripting engine on the Attachmate emulator to post the static data (populated in EXCEL (csv) files) required by the new system
iii) again using CASL, setting up a ‘reconciliation’ process (capturing pre and post migration data directly from screens and automatically running a comparison – notifying any differences).

(3 extensions) contracted to UNISYS at Scunthorpe, Leeds and Gloucester.
UFSS, LINCII 16.3, LDAIII
Working for UNISYS as a Contract LINC developer between January 2000 and June 2002. As a team member based at the client site in Gloucester, supporting the implementation of UFSS (7RP) – a complete mortgage processing system. Part of a multi-disciplined team investigating performance issues with the overnight batch {with over 1.5 million live customers on the database there are two nights in the month that push the batch towards the next days start of business}.
Working at their Leeds office (01/2001 - 05/2001 and 07/2001 - 08/2001): on Account Number Changes. Previously working on a new ‘Mortgage Quotation System’, if a quote is taken up, requires that the quotation data is fed into the normal UFSS pipeline. Working closely with the system test personnel – ensuring that the system provides the functionality they believe is required, rather than that suggested by the sometimes cryptic specification document. For the remainder of 2001 and the whole of 2000, on site at Lloyds TSB in Scunthorpe. The final ‘3 to 5’ conversion phase having successfully completed, then worked on PUFSS. Have produced SRD’s from BRD’s; coded as per SRD’s and unit tested. Fault fixing, which required liaison with system test personnel. ‘Live’ fault fixing – liaison with user community.

Westland Systems Ltd., Hinckley
VB6.0, SQL6.5, COM, ADO
Developer using Visual Studio 6.0, principally Visual Basic, with Visual SourceSafe enabled. Extensive use of T-SQL via a connection object to populate and read from SQL6.5 databases residing on a separate server. The databases set up via Enterprise Manager (for the installation-set this was achieved via script). Where the design required it, server-side SQL database triggers were written. Stored procedures utilised as appropriate, for example to keep the build of large cursors server side.

I worked in a team of two! Accountable to the other chap, who was project manager/ developer. I was responsible for the SQL server build and introduction of ‘stored procedures’, plus VB development work, examples follow.
The project: a bespoke “human resources” system for a large construction company. This gave the HR department the ability to maintain a database of employees with skills data, contractual hours, company cars etc. The dll I was principally responsible for was a “Profile Manager”.

It controlled the degree of access HR personnel had to key functions e.g. amending salary. (There was a ‘global pay increment’ function, which was, not surprisingly, restricted to the few.) This control was achieved by assigning ‘rights’ to the pertinent HR functions.
A significant aspect of my involvement was to confirm the design requirements with the users.

The logical model became: GROUP contained USERS; a GROUP was given ‘rights’ (of access i.e. view, add, amend and delete) to specified HR functions (six categories, containing about 33 individual items); a USER was defined and assigned (bound) to a GROUP, thus inheriting all the group rights. A USER could be disassociated from a GROUP (become unbound). Thus ‘unbound’ users could be listed and re-assigned. Also from a list of users an individual could be selected and their rights amended, but the individual could not be granted any HR functions not already part of the GROUP. ‘Filter Text’ was another attribute, which could contain any modifier to be merged into the SQL select statement.

‘Crystal Reports’ was used to satisfy some of the reporting requirements. Exposure to Windows NT administration.

LINCII 16.3, COBOL74, SQL6.5 Server/SQL, EXCEL, VBA macros
Working on the ALMS system (formerly UFSS) @ LINCII v16.3 & COBOL
Projects include:
- first phase year 2k compliance, ensuring all significant database dates are stored RDN
- reworked insurance renewal letter (improved run time by about 80%)- general insurance statistics
- insurance processing changes due to implementation of PC server based quotation engine (POLARIS): a bolted on insurance module to ALMS, which allows insurance to be sold independently of a mortgage. This will utilise all the direct-debit & customer processing in ALMS - although this is presented to the users as a separate system via VB front-ends.
Also designed (in liaison with insurance department staff) and built an insurance statistics SQL6.5 Server database, which was fed from an ALMS extract (using bcp to load data into temporary tables, from which the formal tables were built). The temporary table is read via a cursor to create a customer table entry, writing it’s identity as a foreign key reference to the owning 'retention' or 'new business' tables). Using VB (macro) within EXCEL to read and present data as well as the MS Query tool where appropriate. Using ftp to copy files from mainframe to server.