Financial Data Access with SQL, Excel & VBA

(1)

Computational Finance and Risk Management

Financial Data Access

with SQL, Excel & VBA

Guy Yollin

(2)

Outline

1 Introduction to VBA

2 VBA macro recorder: simple example

3 VBA macro recorder: extending the simple example

4 The Excel VBA object model

5 The Application object, the Range object, the Cells property

6 The VBE, the Object Browser, the Developers Reference

(3)

Lecture references

John Walkenbach

Excel 2010 Power Programming with VBA

Sams, 2010

Chapter 7 - 11

J. Green, S. Bullen, R. Bovey, M. Alexander

Excel 2007 VBA Programmer’s Reference

Wiley, 2007 Chapter 1

Duane Birnbaum and Michael Vine

Excel VBA Programming for the Absolute Beginner, 3rd Edition

(4)

Outline

(5)

VBA

VBA (Visual Basic for Applications) is a scripting language built into Microsoft Office applications

MS Office applications that support VBA:

Excel Access Word Powerpoint Outlook

(6)

Usefulness of VBA

Excel is a common tool for quantitative data analysis, review, and storage VBA can enhance Excel in the following ways:

Automation of labor-intensive tasks

Formatting tables Creating graphs

Updating data from databases or the web

Advanced analytics

Custom worksheet function development Analysis requiring sophisticated workflows

(7)

Logistics of VBA programming

VBA Coding†:

The VBA code that you write (or record) is stored in a VBA module VBA modules are stored in an Excel workbook

You use the Visual Basic Editor (VBE) to view and edit VBA code You can use the Macro Recorder to record a sequence of user actions and create a VBA procedure to reproduce them

(8)

Logistics of VBA programming

VBA Code†:

VBA Code is structured as procedures

A VBA Sub procedure is a series of statements than can be executed in a number of ways

macro button of the developer tab Visual Basic Editor

shortcut key quick access toolbar another procedure

form control embedded on a worksheet

A VBA Function procedure returns a single value (or an array) and can be called from another VBA procedure or used in a worksheet formula

(9)

(10)

Outline

(11)

The Developer ribbon

(12)

Macro recording example

Problem A common problem is that the column width is not properly adjusted when CSV files are first opened

Solution Record a macro to adjust the column widths

(13)

Turn on macro recorder

Click Record Macro to begin the process

(14)

Adjust column width

Perform Excel tasks

(15)

Add macro to the Quick Access Toolbar

Right click the Quick Access Toolbar and select customize

(16)

Launch macro from Quick Access Toolbar

Click the button on the Quick Access Toolbar to run macro

(17)

The recorded macro

1 _{selects all cells in the worksheet}

(18)

Outline

(19)

Typical table formating

Bold column names

First row shaded background

(20)

Macro Recorder workflow

The basic process for getting started with VBA with the aid of the Macro

Recorder is as follows†:

1 _{Record the actions that you want to code}

2 _{Review the code and find the lines that perform those actions}

3 _{Delete the rest of the code}

4 Modify the recorded code

5 Add variables, control structures, and other code that the Macro

Recorder cannot record

†_{Getting Started with VBA in Excel 2010}

(21)

(22)

Significant border code

' remove any d i a g o n a l l i n e

S e l e c t i o n.Borders(xlDiagonalDown) .L i n e S t y l e = xlNone

S e l e c t i o n.Borders(xlDiagonalUp) .L i n e S t y l e = xlNone

(23)

Title bold/background from macro recorder

Sub TestMacro( ) ' ' TestMacro Macro ' ' With S e l e c t i o n.I n t e r i o r .P a t t e r n = x l S o l i d .P a t t e r n C o l o r I n d e x = x l A u t o m a t i c .ThemeColor = xlThemeColorDark1 .TintAndShade = −0.14996795556505 .PatternTintAndShade = 0 End With

S e l e c t i o n.Font.Bold = True

End Sub

(24)

Significant title bold/background code

Sub TestMacro( ) ' ' TestMacro Macro ' ' S e l e c t i o n.I n t e r i o r.P a t t e r n = x l S o l i d S e l e c t i o n.TintAndShade = −0.14996795556505

End Sub

set interior pattern to solid

(25)

Final table formatting procedure

Sub FormatTable( )

' s e l e c t the c u r r e n t r e g i o n

A c t i v e C e l l.C u r r e n t R e g i o n.S e l e c t

' draw b o r d e r around a l l c e l l s

S e l e c t i o n.Borders(xlDiagonalDown) .L i n e S t y l e = xlNone

S e l e c t i o n.Borders(xlDiagonalUp) .L i n e S t y l e = xlNone

S e l e c t i o n.Borders(x l E d g e L e f t) .L i n e S t y l e = x l C o n t i n u o u s S e l e c t i o n.Borders(xlEdgeTop) .L i n e S t y l e = x l C o n t i n u o u s S e l e c t i o n.Borders(xlEdgeBottom) .L i n e S t y l e = x l C o n t i n u o u s S e l e c t i o n.Borders(x l E d g e R i g h t) .L i n e S t y l e = x l C o n t i n u o u s S e l e c t i o n.Borders(x l I n s i d e V e r t i c a l ) .L i n e S t y l e = x l C o n t i n u o u s S e l e c t i o n.Borders(x l I n s i d e H o r i z o n t a l) .L i n e S t y l e = x l C o n t i n u o u s

' s e l e c t the top row o f the c u r r e n t r e g i o n

S e l e c t i o n.Rows( 1 ) .S e l e c t

' make column t i t l e s bold with a l i g h t gray background

S e l e c t i o n.I n t e r i o r.P a t t e r n = x l S o l i d

S e l e c t i o n.I n t e r i o r.TintAndShade = −0.15

(26)

Outline

3 VBA macro recorder: extending the simple example 4 The Excel VBA object model

(27)

Object-oriented, event-driven paradigm

Excel VBA is based on an object-oriented, event-driven paradigm

Objects Object represents elements of an application a worksheet, a chart, a range

Properties Objects have properties that you can get and set; properties are attributes of an object that describe its characteristics

Range("A1").Font.Bold = True

Note that a property can return an object Methods Objects have methods or actions they can perform

Range("A1").ClearContents

(28)

Object-oriented, event-driven paradigm

Object hierarchy Excel object classes are arranged in a hierarchy Application Workbooks collection Workbook Worksheets collection Worksheet Range

(29)

(30)

Referencing items in the hierarchy

To reference items in the object hierarchy, a period (dot) is used as a separator character

parent.child Application.Workbooks("Book1.xlsx") Workbooks("Book1.xlsx").Worksheets(1)

object.property Worksheets("Sheet1").Range("A1").Value

object.method Range("A1").ClearContents

If you omit a specific reference to an object, Excel uses the

appropriate active object (note: it is the programmers responsibility to make sure an appropriate object is active)

(31)

(32)

Abbreviated references

Note the following line of code:

Range("A1") = 42

Is interpreted as:

A p p l i c a t i o n.ActiveWorkbook.A c t i v e S h e e t.Range("A1") .Value = 42

Because:

The Value property is the default property of the Range object ActiveSheet is assumed

(33)

Outline

(34)

Important Application object properties (∼300)

ActiveCell Returns a Range object that represents the active cell

ActiveSheet Returns an object that represents the active sheet (the sheet on top)

ActiveChart Returns a Chart object that represents the active chart

ActiveWorkbook Returns a Workbook object that represents the workbook in the active window (the window on top)

ActiveWindow Returns a Window object that represents the active window (the window on top)

RangeSelection Returns a Range object that represents the selected cells

Selection Returns the selected object in the active window (Range object or chart object)

(35)

Important properties and methods of the Range object

Properties

Value Get/set cell values (default property for Range object)

Text Returns formatted text in a cell

Formula Get/Set formula in a cell

Address Returns text string of cell address

Font Returns a Font object

Methods

Select Selects a range

Copy Copy a range

Clear deletes contents and formatting

ClearContents deletes contents but leaves formatting

(36)

Other important properties

Cells Returns a Range object that represents the cells in the specified range

Worksheets("Sheet2").Cells(2, 3)

Offset Returns a Range object that represents a range that’s offset from the specified range

Range("A1").Offset(1, 2)

(37)

Outline

5 The Application object, the Range object, the Cells property 6 The VBE, the Object Browser, the Developers Reference

(38)

Object Browser

From the VBE, you can press F2 to open the Object Browser

(39)

Developer Reference

(40)

(41)

(42)

(43)

Immediate window

From the VBE, you can press Ctrl+G to make the immediate window visible

(44)

Outline

(45)

The Colorful Stats project

The Colorful Stats project is an introductory VBA exercise developed in chapter 1 of Excel VBA Programming for the Absolute Beginner

Colorful Stats is implemented as an event procedure

Event procedures are

self-contained blocks of code that require some type of stimulus in order to run. The stimulus often comes directly from the user (for example, a mouse click), but may also result

(46)

Add a control to a worksheet

Click the Insert Controls button on the developer ribbon Select a control (in this case an ActiveX command button) Note the Design Mode button is now depressed

(47)

Edit control properties

Click the Controls Property button (or double click on the control) to open the Properties dialog

(48)

Edit event procedure

Click View Code (or double click the control) to open the VBE Select the type of event to respond to (e.g. click) and edit the code for the event procedure

(49)

Colorful Stats source code

P r i v a t e Sub c m d C a l c u l a t e _ C l i c k( ) '−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− ' Add f o r m u l a s f o r summary s t a t s '−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−− With A c t i v e S h e e t

' These f o r m u l a s a r e e n t e r e d i n t o the new w o r k s h e e t .

.range("D2") .Formula="=COUNT( "&ActiveWindow.S e l e c t i o n.Address&" ) "

.range("D3") .Formula="=MIN( "&ActiveWindow.S e l e c t i o n.Address &" ) "

.range("D4") .Formula="=MAX( "&ActiveWindow.S e l e c t i o n.Address &" ) "

.range("D5") .Formula="=SUM( "&ActiveWindow.S e l e c t i o n.Address &" ) "

.range("D6") .Formula="=AVERAGE( "&ActiveWindow.S e l e c t i o n.Address &" ) "

.range("D7") .Formula="=STDEV( "&ActiveWindow.S e l e c t i o n.Address&" ) " '−−−−−−−−−−−−−−−−−−−−−−

' Add l a b e l s and s t a t s '−−−−−−−−−−−−−−−−−−−−−−

.range("C2") .Value=" Count : "

.range("C3") .Value=" Min : "

.range("C4") .Value="Max : "

.range("C5") .Value="Sum : "

.range("C6") .Value=" Average : "

.range("C7") .Value=" Stan ␣Dev : "

.range("C2 : D7") .S e l e c t End With

'−−−−−−−−−−−−−−−−−−−−−−−−−−−−− ' Format the l a b e l s and s t a t s . '−−−−−−−−−−−−−−−−−−−−−−−−−−−−−

With S e l e c t i o n .Font.S i z e= 16 .Font.Bold=True

.Font.C o l o r=RGB(232 , 211 , 162) ' Husky Gold

(50)