Does it ever seem awkward to run Visual Basic subroutines within Microsoft Excel? If you have a plain unvarnished subroutine, you must click through to the Developer tab, and then point and click to find the macro of interest. “There are more convenient ways to access the macros you build,” said Andrew Nikolishyn of Vesta Solutions. He was the instructor of a day-long seminar on “Visual Basic for Financial Professionals,” held on location at the CFA Society of Toronto on May 1, 2015.

In Part 1 of this posting, I described how participants could access the Developer tab to create and modify simple macros. Here are three more helpful things we covered.

Push a Button, Run the World

Hum a Few Bars_Fig 2

  1. To summon a macro directly from the Excel environment, click on the Developer tab.
  2. Choose Insert, and then choose the most appropriate button, a.k.a. Form Control, among the 12 Form Controls available. (See the Figure above)
  3. Once you select the button, give the button a name related to what the macro will do. (For example, “Clear the Page” or “Format the Page.”)
  4. You will be prompted to assign a macro to the button, so choose from the list given in the dialog box. Once you assign a button, every time you click on that button, it will carry out the macro—straight from the Excel page where you have placed the button.

Automate that Web Query

Nikolishyn showed an example web query.

  1. In Excel, click Data > From Web. Type in search “yahoo.com”. Click Go. You might get Script Error messages that ask “Do you want to continue running scripts?” Click No. [This happened to me several times.]
  2. Select the Finance link and look up AAPL (the stock ticker symbol for Apple).
  3. From the Summaries list (on the left hand side), click on Historical Prices.
  4. Select the data table by activating the  arrow to a highlight green. Click the highlighted green box. (See Figure below.)
  5. The arrow will turn into a checkmark.
  6. In the lower right-hand screen, click the Import button.
  7. On the Excel worksheet, click in the cell where you want to place the upper-left corner of the web search.
  8. Note: at a later time, you can verify the web query by clicking Data > Existing Connections

Hum a Few Bars_Fig 3

A Fast Way to Select A Block of Data

Let’s say you’ve just executed the web query described in the preceding section. Next you want to select the entire block of data, perhaps to look for the maximum price. Nikolishyn pointed out there’s a fast way to “grab” that block of data.

  1. Record a macro that does the following: Make the worksheet active. Click on an occupied cell.
  2. Stop recording. Open the macro in the VB Editor.
  3. Add the line “Select Current Range” – that will select not just the occupied cell, but the entire contiguous data set.

 

These were three takeaway lessons from the seminar—three things that I plan to use.

The curriculum was ambitious for a one-day seminar, with various other topics covered:

  • Creating a custom macro button on the Quick Access Toolbar;
  • Stock-related example of properties and methods (sell stock if P/E triggers);
  • Declaration of variables;
  • Dynamic opening of a file or creation of file; and
  • Copy/paste of a dynamic range.

Over all, I found the seminar was a superb refresher on VBA—from a financial analyst’s perspective, with small projects that could be taken back to the office and used right away.ª

Click here to read about the first half of the seminar.

Click here to download a macro that will draw a colour table with colour codes as shown below:

ThreeTakeaways_ColourTable