Tài chính doanh nghiệp - Topic 7: Advanced excel awesomeness: macros, web queries, and other cool stuff

1Topic: 7Advanced Excel Awesomeness: Macros, Web queries, and other cool stuffFinancial ModelingL. Gattis2ReferencesFinancial Modeling 3rd Edition by Simon BenningaCh. 34: Array Functions and FormulasCh. 36: User Defined Functions with VBACh. 37: VBA Types and LoopsCh. 41: Web QueriesTopicsMacrosFunction Descriptions Data TablesData Controls (Drop Down, Spin Buttons)Conditional FormattingSparkle ChartsVlookup and hlookupPivot tables, SlicerWeb Queries 34MacroA User Function is used to make calculations and return a value to a cell or range of cellsA macro is used to automate tasks, for example:Copy value or formula from one cell to another cellDisplay messagesMacros (also called subroutines) are also stored in modules and have a similar structure as functionsSub macroname ()executable statementsEnd subMacros are run from developer/macro or alt-f8Macros must have no parameters5Macro ExampleSub cells1demo()Cells(1, 2) = 5End SubSub cells2demo()Cells(2, 2) = "How"Cells(2, 3) = "You"Cells(2, 4) = "Doin?"End SubThe following macros use the “cells” object and writes values in a specific excel cells using (r,c) indexing (Cell Reference Base is 1)6Running MacrosAlt-F8 or Developer Tab/ MacroAttaching macros to art objectsInsert art objectRight-click, assign macroAttaching macros to quick access toolbarRight-click on toolbarSelect CustomizeSelect Quick Access ToolbarDrop-Down: Select MacrosSelect Macro from listAdd, OK --- it’s now at the top of your workbookCreating short-cut keys (Ctrl-???)Tools/macro/macro (alt-f8)Select macroSelect OptionsType shortcut key letter7Macro ExamplesSub cells3demo()For i = 1 To 12 For j = 1 To 12 Cells(i, j) = i*j Next jNext iEnd SubSub cells4demo() For i = 1 to 10 Cells(15+i,5) = Cells(12,12) Next iEnd SubWhat is this macro going to create?8Recording MacrosDeveloper TabRecord new macroPerform tasks: e.g., copy matrix and paste into another range, then change the format of the new matrixDeveloper Tab, Stop recordingexecute macro and view code: This is a way of finding new VBA statements and objectsFunction DescriptionsAlt-F11, Insert Module, Copy FunctionFunction PVX(Cashflows As Range, r)'computes PV of colomn vectors of cashflow, discounted at rn = Cashflows.Rows.Count For i = 1 To n PVX = PVX + Cashflows(i, 1) / (1 + r) ^ i Next iEnd Function Alt-F8 (Macro), Type Function, Options, DescriptionThese description pop up when insertedTied to workbookVBA Comments stay with program, but are not visible when inserting function9Data TablesData tables allow you to perform sensitivity analysis of two variablesThe example below shows a the intrinsic value of stock stock value = pv of growth perpetuity = (FCF/k-g)Compute the value, Create a link, create a row and column of variable valuesSelect Grid Data, Data, What if, Data Table, Row and ColumnConditional FormattingSelect Data, Home, Conditional Formatting, Color ScalesChange format of top right to “white text”11Spark LinesSpark Lines are dynamic charts inserted into single cellsInsert, Line (Spark Line)Insert, Column (Spark Line)12VLOOKUP and SUMIFSUMIF sums the values in a range if a criteria is metSUMIF(range, criteria, [sum_range])COUNTIF counts the number of observationsVLOOKUP returns a value in a range if the far left value is equal to a criteriaVLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])HLOOKUP is a Horizontal lookupINDEX is a similar function13Copy to EXCELPivot TablesPivot tables allow you to summarize data range values and provide statistics such as sum, min, max, standard deviation, variance, etc. 14Pivot Tables15Select data rate including label, Insert, pivot table- Slide YEAR to ROW labels- Drop Down Row LabelsPivot Table SlicersSlicers allow you to filter results of a pivot table(Click in Pivot Table) Insert, Slicer, Select Year and Quarter16Data Controls: Spin and Drop-Down17Drop Down Menu-data tab / data validation-Select Allow list-Specify source cells1. Developer Tab:InsertSpin Button or Scroll BarSelect Cell Range2. Right Click on ButtonFormat ControlCurrent/Min/Max/Incr/LinkOther Data Validation / ProtectionData / Data Validation / Data ValidationAllowCell MessagesError AlertProtecting CellsSelect Cell, Right Click, Format, lockReview, Protect SheetFrom Review TabProtectionComments18Data: Text to ColumnsFama-French Data format is text (Col A)19FF3F Data: Column A, Data, Text to ColumnsRepairing DataLEFT/RIGHT, REPLACEFind and ReplaceText to number by multiplying by 1Other text functions include concatenate, 20Formula TabRecalculation methodManual or AutomaticTracingPrecedents, dependents(Range) Name ManagerDelete or edit named ranges2122Web QueriesWeb queries provide excel links to websites that can be refreshed to get the most recent data23Creating Static Web QueriesCopy Web address / Data / Get Data From Web Paste website address into address bar -- goSelect data item(s) by clicking on the arrowsSelect import at bottomSelect cell location and OK24Refreshing Web QueriesRight click and select refresh dataRight click and select “data range properties” and choose refresh frequency or refresh upon opening 25Parametric Web QueriesParametric web queries allow users to enter parameters (such as stock symbol) which then directs the web query to that stock’s information.If the parameter is changed, the web data will automatically be refreshedOnly works for web addresses which have a recognizable variable Parametric Web QueriesExcel / Data / Get Data From Web Paste website address into address bar -- goSelect data item(s) be clicking on the arrowsSelect import at bottomSelect cell location and OK27Creating Parametric Web QueriesCreate a web query file (.iqy) byRight click on excel query range and select “edit query”Select save query at topProvide a name (e.g., keystats) and folder to save (e.g., my documents)Use notepad (MS accessories) to view and edit the query28Creating Parametric Web QueriesReplace “IBM” with [“Enter Symbol?”]29Creating Parametric Web QueriesGo to new workbookType a symbol in a cell (e.g., IBM in A1)Data / Existing Connections / browse for moreSelect query file (e.g., keystats), OKSelect cell where data is to be displayed (A3)In the dialogue box, enter location of parameter value (e.g., A1)Check two boxes for automatic updatingTopicsMacrosFunction Descriptions Data TablesData Controls (Drop Down, Spin Buttons)Conditional FormattingSparkle ChartsVlookup and hlookup, sumifPivot tables, SlicerWeb Queries 30