Visual Basic for Financial Applications

About

PLEASE NOTE: This seminar is strictly limited to 20 delegates per venue, we cannot guarantee you a place so please book early to ensure your participation!

Finance professionals must handle, analyse and act on enormous amounts of data.  Spreadsheets such as excel are a powerful financial tool.  Visual Basic adds further options for the busy finance professional, allowing custom programming and automation to be included for preparing financial models allowing you to move beyond the constraints of Excel.

VBA is accessible to those with no programming knowledge, yet is powerful enough to create specialised business applications.  This two day course includes everything that Power Users will need to know when creating financial spreadsheets and is the ideal follow on course to Advanced Spreadsheet Skills for Finance. 

Key Learning Outcomes
• Learn what Visual Basic for Applications is, and what you can do with it
• Learn how to record macros, link to keys and use to improve productivity
• Create shared macros with network enabled “addins”
• Create and edit your own code
• Create objects, properties and methods for your code
• Ensure Macro security
• Create loops to repeat common tasks
• Learn how to test, improve and debug your code
• Learn how to integrate workflows across Microsoft Office applications

Who should attend?
This course has been developed specifically to meet the needs of financial executives and decision-makers from both the public and private sectors  who need to extend the power and usefulness of their MS Office products individually and in integrating them into a larger workflow including:
• CFOs
• Finance Directors
• Financial Controllers
• Business Analysts, Managers and Administrators
• Financial Planners
• Management Accountants
• CEOs, MDs & General Managers

Training methodology:
This course contains a high level of practical work, backed up by lecture and discussion, to provide the most valuable and intensive learning experience possible. All participants will be provided with their own laptop with the latest software for use in practical exercises.

A workshop format is followed whereby course concepts are reinforced by  participants working through practical exercises. Visual Basic development - as with most development - has a  characteristic workflow: A problem is defined and a solution is developed in increments or stages. The course follows that lifecycle and illustrates stages in the development of answers to a set of practical exercises.. By the end of each exercise participants will have answers that fully meet the exercise specifications.

Core focus is on MS Excel  but Word, Internet Explorer, PowerPoint and Outlook are also detailed.

At this premium training seminar you will:
• Learn what Visual Basic for Applications is, and what you can do with it
• Learn how to record macros, link to keys and use to improve productivity
• Create shared macros with network enabled “addins”
• Create and edit your own code
• Create objects, properties and methods for your code
• Ensure Macro security
• Create loops to repeat common tasks
• Learn how to test, improve and debug your code
• Learn how to integrate workflows across Microsoft Office applications

Outline

Creating Macro Shortcuts
• How do you record a macro?
• How do you link it to a shortcut key?
• Changing shortcut keys
• Using macros on multiple PCs
• Autoloading macros and shortcuts
• Hiding and unhiding
• Repairing and re-enabling files
• Creating your own “personal.xls” shortcut file

Network Accesable Excel Addins
• Generating Addins
• Automatically loading addins?
• How can you make the Addin do something automatically whenever a user opens a spreadsheet?
• Ensuring Addins are current and up to date

Toolbars and Menus
• Adding items to existing menus
• Linking menu items to macros
• What happens to linked toolbars and menus when workbooks are moved?

Macro security
• Password protecting macros
• Protection against “crackers”
• Evaluating different security levels i.e. Very High, High, Medium and Low
• Macro attachments in emails and IT security

Recording & Editing macros
• What does recorded macro code look like?
• Editing Macro code
• What happens when you make a mistake?
  - Tools for testing and debugging code

Writing macros from scratch
• What is a variable?
• What types of variables are there?
• What is a statement?
• What determines the order in which statements are done?
• How is program "flow" determined?
• Understanding the object model and spreadsheets

Writing functions
• Differences between Macros and Functions
• Defining a function
• Constraints when working with functions

Other programming topics
• Programming Forms
• Programming  Events
• Programming Classes

Workflow integration
• How do you reference one Office application from another?
• How can you determine when a command issued to another application is complete?
• How do you disconnect from and close another application when you've finished with it?
Examples and Exercises include the following:
• An "Exception-report generator" that reads a set of financial data in a folder, determines which of those sets contain values that exceed defined limits, and which generates an exception report in tabular and graphical form in a Word document.
• An extended version of the Excel NPV function that works with a term structure of discount rates (the Excel NPV function only allows a fixed discount rate)
• A "web-crawler" application that scans defined pages on the internet (or on a corporate intranet site), extracts financial data from those pages and aggregates that information in a spreadsheet.
• A simulation-based analysis studying peak and mean customer service times as a function of resources committed.
• A Monte-Carlo based valuation exercise

Facilitator

Alex Palfi, Principal, Tykoh Group Pty Limited

Alex Palfi is Principal of Tykoh Group Pty Limited - an Australian-based training provider specialising in tailored one and two day workshops on technical finance and business topics.

Prior to setting up Tykoh in January 2009 Alex was a Division Director at Macquarie Group in Sydney.  In that role he developed a range of finance workshops both for Macquarie and non-Macquarie people and presented those workshops internationally in London, New York, Tokyo, Los Angeles, Korea, Hong Kong, Singapore, Taipei, Johannesburg, Bangkok, Sao Paulo, Sydney, Melbourne, Auckland and Wellington.  Those workshops focused on Financial modelling, financial spreadsheets, valuation, derivatives, credit risk and Visual Basic programming.

Prior to joining Macquarie in 1994 Alex worked for a number of financial services organisations as a financial software developer and before that lectured at Technical Institutes in New Zealand in programming and electrical engineering.

Alex's university qualifications are in engineering.  He obtained Masters and Bachelors degrees in Electrical Engineering from the University of Canterbury in New Zealand.

Alex Palfi is also facilitating:

• Advanced Spreadsheet Skills for Finance
• Financial Modelling

In-house Training

Prices and Registration

* Early bird price available when you register and pay before the dates listed.