There is one very simple function that can be used in order to find the top or lowest ranked line of a data set. If you rememebr the post I had written a macro to capture stock prices, you can easily imagine how you would get a data set as the one below:

Basically, a list of stocks and the price they are currently trading at. If you wanted to get the highest prices, you could do that by using the “rank” function in excel. First off, you need a free column such as the one on the left, then you could simply use in cell A2:

=RANK(C2,C:C)

I then dragged this formula in order to get the ranking for each stock. Once that is done, I can simply create myself a small table such as the one below:

In order to update the table, I can simply update the prices. How? I used vlookup functions for the stocks and prices, as you can see here.

=VLOOKUP(L8,A:C,2,FALSE)

=VLOOKUP(L8,A:C,3,FALSE)

One possible Issue

There is one possible problem here. What if two stocks had the exact same price? Let’s try changing the price of BIDU from 125.26 to 189.32 and see the new result:

As you can see, I get some errors. That might be something that you want but in most cases, it’s far from ideal. How do I resolve such situations? I simply add a small number to the price, just enough to avoid any possible “ties”. Let’s take a look at my 2 new columns:

After adjusting my “rank” formulas to use the new “adjusted price”, you can see my new rankings:

This helps me avoid any errors in my rankings.

As always, you can download our spreadsheet here. Let me know if you have any questions or comments on the rank function.

Popularity: 1% [?]

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

Popularity: 1% [?]

Excel is very powerful, that is no secret, but many do not know that Excel can also communicate very well with other types of software. Want to send an email? Excel can communicate with outlook to get that done, it can also communicate with Access databases. One thing that I personally use a lot is gathering data from the internet through excel. It’s no surprise that Microsoft made it easy to gather data from the web as excel uses Internet Explorer to get the data. There are probably a billion different ways that this can be used but we’ll start with a financial use (of course!). There are a lot of reasons why an investor would like to know a stock’s price. Of course, if you own a stock, getting its current price will make it easy to know how much your investment is worth.

This is just one example but it can be done with almost any data that is available on the web. As we did with excel macros, I will start by doing a simple demo. Then, next week, I will give an example that will show you how to improve the speed, efficiency but also how you can use a macro to gather the prices of a dozen or more stocks.

You could use a large number of websites to gather financial data but we will use Yahoo Finance and for today we are looking to get Microsoft’s (MSFT) last price.

In any excel spreadsheet, you can import external data (either from a database, a webpage, etc). You can select the menu here:

Then, as if you were in a browser, you can enter “finance.yahoo.com” and press enter

Finally, simply enter the ticker “MSFT”, click “Get Quotes” as you would if you were simply visiting the page.

Then, you must select which part of this page you would like to extract, simply click on the arrow for that section. In this example, I will select the first one which would include all elements of the page. And then click “import”

You can then click on “ok” and the result will appear:

As you can see, you lose a lot of the formatting and images when you extract the information and in this case, since we selected the entire page, it makes it a bit difficult to read.

That being said, you can see the “Last Trade”. I could simply do a vlookup reference to easily see this page.

How Could I Refresh This Data?

There are many different ways. One would be to right click on the query and select “Refresh query”:

If you would like to take a look at my file, feel free to download it here.

Popularity: 1% [?]

One of the questions that we have been receiving quite a bit in recent months has been regarding the difference between the Sumif and Vlookup functions in excel. They are fairly different but there is some confusion regarding their use. Let’s start off by explaining both:

-Sumif function: Is used to sum the quantity (or any other data in a separate column) for a specific criteria. We already wrote about this function, you can find out more about the sumif and countif functions
-Vlookup function: Is used to find a reference associated to a specific criteria

These are the formulas used:

=SUMIF(A:A,”Strawberry”,B:B)
=VLOOKUP(“Strawberry”,A:B,2,FALSE)

When do you use each one?

Vlookup

-If you only have one sample for each “fruit”
-If the data you are looking for is not a number
-If you want to easily spot a “missing fruit” – vlookup will give you a “N/A” result when a vlookup returns no results

Sumif

-If you have more than one sample
-If you want to avoid getting “N/A” errors that will screw up calculations in your spreadsheet

Does that explain it well enough?

Popularity: 1% [?]

If you are like me, you have a few excel spreadsheets that are becoming larger by the day. It can become very complex to keep up and you will often use functions such as the “match” function that we discussed last week. Another function that can help when you are using reference functions is the offset function. We used it in last week’s post but it was done rather quickly. Here is a better example:

As you can see, if each product has 2 regions, how could you find what those are and what the prices are? A vlookup would not work because of the fact that the result is on a different line. In order to proceed, we will use the match function but also the offset one.

Here is the formula for the offset function:

=OFFSET(reference, rows, cols, height, width)

In this case, we will need to find “orange” through the match function and then use the offset to find both locations. This could work on a much larger excel.

Where is it? =MATCH(“orange”,A:A,0)
Location #1 =OFFSET(A1,MATCH(“orange”,A:A,0),1)
Location #2 =OFFSET(A1,MATCH(“orange”,A:A,0)+1,1)

When you are unsure about how a file will evolve, if columns or ranges will be moved around, using the match or offset function will make it much easier to maintain the files over time.

Popularity: 1% [?]