In the past few weeks, we have taken a deeper look into why and how you can build excel macros, either through recording or through writing/modifying excel VBA code. In a perfect world, the conception of the macro would be the one time that you would even think about it, let alone modify the macro. However, things change and for a variety of reasons, the macro that you build might have some problems such as:

-Being unable to finish its code, getting you this nice window:

-Not giving you the anticipated or desired result
-There might also be some improvements or changes that you’d like to make

The reality is that it’s very unlikely that you will be able to never work on the macro again. When you do, there are a few things that you can do in order to work on the macro and fix any bugs. First off, you can click ALT+F11 buttons to open the “VBA code”. Then, click twice on the module where your code can be found:

After that, you can simply click anywhere in the macro and press F8. Ideally you can see both the code screen and the excel screen so that you can see the macro in action as you move through it. Each time that you click F8, the macro will move to the next line and you will see the yellow marker move with it as well.

If you would like to move to a specific part of the macro

1-simply click in the left margin to the area that you would like, that will create a red marker

Then, you can click anywhere in order and press the “play” button, it will process the macro up to that point, at which time you can press F8 one step at a time.

During the time that you have the redmarker, the macro will automatically pause when it reaches that point. To remove it, simply click on the red marker again.

If at any point you would like to stop and start again, simply click on the top stop button and then you can start over by using the same steps.

Variables

A last point, if you use variables in your macro and would like to be certain what the value of that variable is, you can simply run through the macro pressing F8, and when you want to know the value (at that given time), simply take your mouse over the variable and you will see the value at that point.

Let’s start with this, let me know of course if you have questions or any other excel functions/problems that you would like to have looked into!

It seems simple enough, you need to format according to specific rules. Why? Generally to easily spot data that fits a specific criteria. I’ll use a very common example. Suppose that you have a list of stocks and want to compare their current price to yesterday’s close in %. Then, you would like to easily spot the stocks that displayed gains from those that didn’t. Here is the original data:

In order to make it easier, I would like to have all stocks that show gains in green and others in red. The first option would be to do it by hand. I could sort the stocks by Gain/Loss and then manually adjust the color:

Manual Formatting Adjustments

I do get the result that I want although I would need to do this exercise every single time that I update the prices which leads to lost time, and risk of error. It’s clearly not an ideal situation. What else could be done?

Conditional Formatting

In this case, I can use a criteria that will apply to a range of cells and will determine their format. Here is how to do it:

#1-Select the range of cells and then select: format/conditional formatting

#2-Select the rules you would like:

The result as you can see below is great

Not only that, any time the prices are changed, the Gain/Loss number will update automatically. It’s a perfect solution right? Not quite. While conditional formatting is great, it does have some limitations that sometimes make it insufficient. Suppose that you either need to or want to make the entire line of any stock that is either +5% or -5% appear in the same format, to make it even more obvious. Seems simple enough but that is not possible with conditional formatting. You need a third way.

Formatting Using Macros

If you are not comfortable creating your own macros, I recommend that you take a look at our excel macro introduction. As we discussed, it’s always easier to start by “recording” a macro and then modifying it when you’re not used to doing so. In our case, let’s simply take a look at the code to change an entire line to green and start from there. So I record a macro, and here is the result and code:

As you can see the current macro simply changes the color of line #2. In reality, I want the macro to go through each line and change the background color for lines that have a gain better than 5% (0.05). So let’s modify the macro. I will also add 2 lines to remove any background color when starting.

As you can see, the code is still fairly simple and could be modified but it gives me much more flexibility and could be used to format based on almost any condition! Hopefully this helps you out. I added a button and as always, you can download the spreadsheet here to check for yourself

Anyone who has worked with large sets of data in excel has used filtering to get a better look at the data. Take an example where I have the list of the top 200 female tennis players in the world and need to find players that fit a specific criteria.

The easier method is to simply select the cells at the top of the columns:

And then click: “Data/Filter/Auto-Filter

Then, I can simply select a criteria such as a specific country or even select a “custom filter” where I can select up to two conditions that must be met.

That certainly gives you a lot of different possibilities right? Take a look at a filter on all Russian players:

There are however many reasons why that might not be enough. What if for example you wanted to specify 3 different countries? Or you wanted to quickly process a list of different queries? Or you have very custom data set queries?

In Comes Advanced Filters In Excel VBA

It is a simple feature, very easy to use but it’s one of the most powerful functions in excel in my opinion. How does it work? You need a few things to have this work. If you are not familiar with excel macros, it might be a good idea to take a look at our excel macros introduction. Back to advanced filtering though, in order to get it done, you need:

-For the data set to have headers
-Right next to it (or it can be located elsewhere), you need to have the same headers, here is my example:

Then, simply enter the criteria that you would like. In this example, I would like all players from these countries:

RUS
ITA
CZE
POL
GER

You can see a screenshot here:

Here is the code that I’m using, as you can see it’s very simple.

There are 3 ranges that you can “edit”:

-the first one is where the data that you are filtering is found
-the second one is where the criteria should be found. You need to edit the end of the range (in this case O6) to only include lines that have a criteria. If I had put O7, that would have meant including an “empty” criteria – this is critical
-the third one is where you’d like to display results

The result is quite extensive, you can see a shot here.

I could also add additional criteria points, for example, only including players that have played over 25 tournaments as you can see here:

The new result is:

It is extremely powerful and you can use it very large sets of data, adding multiple types of crtieria, etc. It is also very quick.

As always, you can download this spreadhseet here!

After publishing an introduction to excel macros both by recording and writing code, I received one email asking me about one problem; How to create excel files that could in theory either be printed for each student or sent to students and/or their parents. I know that this problem is not so much about finance but you could easily build something along the same concept for finance. For example, if you received a a list of stocks and wanted to create files for each one, you could use a similar method.

I’ll start off by showing you an example of what the initial file would be like:

The idea would be to have a macro that will create a file for each student (there could be tens or hundreds of them) that would look like:

The only way to do this quickly would be to use a macro written with code. Why? Because you would need variables. That being said, you can still use a recorder to get you started. I will start the recorder and then look at the code that results, first I created a new file and then wrote down the information as I would like it to appear, naming the file and then closing it. Here is the code that results:

Of course, I need to make a few changes here. First off, let’s remember that the macro must take in the information for each student. Let’s take a look:

As you can see, the loop function means that the macro will go line by line in the main file and capture the information of that student. What we now need to do is to use that information for the actual file. Here are more modifications:

Now, the main things to add would be to add the teacher’s name and save the file with the correct name, here is my final code:

You can download our full excel spreadsheet here.

Last week, we built a very simple spreadsheet that allowed us to get the last price of Microsoft’s stock. That can certainly prove very useful but it makes it difficult to gather larger quantities of data. If you were looking for stock prices of 5, 10, 20 or even 50 stocks, what would you? Have 50 different queries running all at once that would get the data. It would certainly not be very efficient. Instead, we can use macros to achieve the same result. I will try to get it done in a simple and clear way to help you understand. I will go line by which should help you understand how it is done. To get started, I created a simple excel file with 2 different tabs.

1st Tab = Ticker List
2nd Tab = Web Query

First off, I will start by creating a macro that goes to the “tickers” tab and reads each ticker. Here is what I get:

As you can see, I have a variable named “I”, this simply replaces the line where I start. For example, i=5 to start off. So when I start, I tell the macro to start at the cells 5,2 (5th line, 2nd row) and move down until that cell is empty. That way, the macro will be able to read the tickers one at a time until there are none. The macro will do the parts between “Do until” and “Loop” until it gets to that condition. Of course, for every ticker, we need to do a web query right? So let’s add a big part that looks scary but really isn’t:

I know, it looks complicated. But you really do have almost nothing to modify here. The only thing we need to change is the “URL” as MSFT will not always be the ticker we are looking for. We do have a variable for that. So let’s use that instead. I will replace:

http://finance.yahoo.com/q?s=MSFT&ql=0

With:

http://finance.yahoo.com/q?s=” & ticker & “&ql=0”

That way, I will be do a query for each ticker. The missing part is to capture that price. To do so, I had added a vlookup under the tickers. I will thus simply get the macro take the result of that vlookup and put it in the right cell. Here is what I have before doing this:

Now, I will simply add a simple operation:

Cells(i, 3).Value = Application.VLookup(“Last Trade:”, Sheets(“WEB”).Range(“A1:B1000”), 2, False)

The final result is:

When I run the macro, here is the result:

Clearly, the code could be more optimal, faster, etc. But I think it remains a quick and easy way to get it done. You can download our spreadsheet here and be sure to check our intro of excel macros if you have questions!