Resource C -
Faster, Stronger, Better Analytics - Using Macros

A macro is a user-defined function created by combining your own custom formulas, MarketBrowser functions (see the functions guide) and/or previously defined macros. A macro can be a string of very simple commands or a very complex formula. Some macros are so complex that they are in effect small “programs.” There are 2 purposes for Macros:

• Save, name, and re-use your custom formulas—so you're can be faster and more accurate in your analysis
• Use the macro as part of a larger custom formula—by "stacking" macros and MarketBrowser functions, you can make ever more sophisticated, complex, & powerful analytics

Creating a Simple Macro

Work through this example to learn about:

• How MarketBrowser AE extends the spreadsheet metaphor to time series data
• How to create a simple macro to evaluate a portfolio
• How to copy a formula from one window to another
• How to make macros flexible by adding macro arguments

Step 1. Set Up Your Worksheet

For this tutorial, we'll need 9 cleared windows of a MarketBrowser Worksheet.
(You can create a new worksheet from the 'File' menu. Or clear the windows in your current worksheet by clicking 'Edit'->'Clear All')

Now set up three live charts in W1, W2 and W3 by typing symbols into them (We'll use .GOOG, .DELL, and .AAPL)

Step 2. The Spreadsheet Metaphor

Assume you own the following number of shares of each of the charted instruments, and would like a live value of your portfolio:

• W1 - .GOOG - 100 Shares
• W2 - .DELL - 200 Shares
• W3 - .AAPL - 150 Shares

To find the total value of your portfolio, you would build a custom formula:

1. You'll build this formula in W4. Click on that window to select it
2. Enter:
   
          =(100 * W1) + (200 * W2) + (150 * W3)
   
   

A plot of the live value of your portfolio appears in W4. This is similar to creating formulas in a spreadsheet program, except that in MarketBrowser AE, your formulas use entire series, instead of one number per cell (as in a spreadsheet).

Step 3. Save This Formula as a "Portfolio" Macro

Now in window 5 (W5) of this Worksheet your will create (or "define") a macro named MY_PORTFOLIO that calculates your basket value.

1. Select W5
2. Define the macro name and body using the #DEFINE function by typing:
   
          =#DEFINE MY_PORTFOLIO (100 * W1) + (200 * W2) + (150 * W3)
   
   
3. There! You've created a Macro. To make see your Macro you can go to the 'Custom' -> 'Macros' pulldown menu and select 'List/Edit'
   Note that on Windows, you can also use this menu to create and edit macros
4. You will see that MY_PORTFOLIO has no arguments, and has been defined as:
   
          (100 * W1) + (200 * W2) + (150 * W3).
   
   
5. Each time you type MY_PORTFOLIO within the current Worksheet, MarketBrowser AE will perform an internal text substitution to
   
          (100 * W1) + (200 * W2) + (150 * W3)
   
   
6. To test this, go to W5 and type:
   
          =MY_PORTFOLIO
   
   
7. W5 should be equivalent to W4

You have now written a simple macro. Read on for further discussion on the intricacies of creating more sophisticated macros.

Step 3. Add Versatility to Your Macro

But what if your portfolio shares holdings change? Do you have to create a new Macro each time your holdings change? Luckily, the answer is "no". We can re-create this Macro once so that each time it's used, you can redefine your holdings.

1. First, delete the original definition of MY_PORTFOLIO. Type:
   
          =#UNDEFINE MY_PORTFOLIO
   
   
2. Select W6
3. To make your original, inflexible macro formula you typed:
   
          =#DEFINE MY_PORTFOLIO (100 * W1) + (200 * W2) + (150 * W3)
   
   Now, you're going to enter a slightly modified version:
   
          =#DEFINE MY_PORTFOLIO(a,b,c) (a * W1) + (b * W2) + (c * W3)
   
   
4. This new macro allows the user to define 3 "arguments" each time he uses the Macro (where a is shares in W1, b is shares in W2, and c is shares in W3)
5. Test this by going to W6 and typing:
   
          =MY_PORTFOLIO(200,100,150)
   
   
6. W6 should produce the same result as W4 and W5
7. Next time you use MY_PORTFOLIO, you can change your holdings by changing the numbers in the parentheses

4. Next Steps

Although our example file defines only one, simple macro, there is no limit to the number of macros that can be defined. In addition, your macros can call from any of MarketBrowser AE's 700 functions. Even better, your macros can call other macros. By "stacking" formulas, functions, and macros; your analysis is only limited by your imagination. Happy Analyzing!

Thank you for reading...

We hope you have enjoyed this MarketBrowser AE resource. If you have suggestions or topics you would like to see, please contact us.

Welcome to the MarketBrowser family!

Warmest Regards,
The MarketBrowser Team

Ready to buy MarketBrowser AE? Order online. Interested in more tutorials? Tutorials Homepage. Got Questions? Visit our FAQ page. Feedback? We'd like to hear from you.

PS. Please help us improve these tutorials!
Take 3 minutes to tell us what you thought...