Company: UBS AG, CTO, SDLC Host based MS Access development
Technology: MS Access, MS Excel, VBA, SQL Server, T-SQL, Jira, SAP, Oracle, DWH, MS SQL Server, SSIS, Alterix.
Link: Not available
People in the project: Stefan Tröhler
Implementation period: Jul 2018 – Feb 2020
Cost: no details
Project SDLC Reporting
The project consists of several databases, which contain the tables needed to import the data, the queries needed to refine the data, the macros needed to execute the workflow, which in turn are started by the Power Shell scripts and in turn call the functions and procedures developed using VBA. Each of these individual databases can be compared to an ETL route. Further databases consolidate the data for reporting with regard to the SDLC reports. Procedure for executing the entire application: Power Shell scripts call the macros, which in turn call the complex VBA logicians for importing the data, cleaning the data and consolidating the data. The entire application was developed using Microsoft Access, VBA and Power Shell.
- Develop a host-controlled MS Access load of data.
- Loading of tables in Alterix and SSIS, T-SQL.
- Loading of DWH tables.
- Loading of Applications data (JIRA, RNOW etc.).
- Output and reporting.
- Creating complex workflows with Power Shell.
- Data synchronisation between different applications (DAP/ADO).
- Data synchronisation between different applications (DAP/ADO).
- Complex SQL queries in MS Access.
- Import of different data sources.
Access2Git project
The aim of this project is to break down the individual Access applications into their objects, save the objects in the form of text files and import these in turn into Github. This ensures that the individual objects, which in turn form an application, can be properly managed in a version and source code management. Of course, objects can also be obtained from the source code management and thus the applications can be formed again from the individual objects. It is also possible to merge objects from different versions into a new version. The entire application was developed using Microsoft Access and VBA.
- - Administration of the MS Access databases involved (approx. 20 DB).
- - Object-related backup and restore of all MS Access databases.
- - Object-related creation of versions and rebuild of applications.
- - MS Access object output to GITHUP.
- - Creation of the logic by means of VBA.
- Creating the user interfaces in Access using VBA.
- Creating the input validations using VBA.
Project MAQualyfication
The application is already a dynamic Excel, which performs various calculations based on the self-assessment of the employees and subsequently performs a graphical evaluation regarding the self-assessment of the employees of the GROUP CTO department of UBS AG. The application was developed using Microsoft Excel and VBA. The data is entered manually in a template.
- Create dynamic tables using VBA.
- Calculate scores using VBA.
- Output of evaluations by means of dynamic diagrams.
- Synchronisation of data using VBA.
Project ProjectCallculation
The application was realised with Microsoft Excel and Microsoft VBA. Data is loaded from the systems, refined and calculated. Dynamic tables are created in which both hours and costs of the individual bookings of the systems are calculated against the effective figures of the responsible departments. Complex calculations are implemented to create the dynamic tables and to reconcile the financial data and the time data, and the results are output in various reports that are exported as individual Excel tables.
- Create dynamic tables using VBA.
- Calculate financial data using VBA.
- Display the calculated data in the form of dynamic tables.
- Exporting the dynamic tables as reports for the individual department heads as well as for the responsible persons in Finance
swiss made software