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.

In the 1980’s, a prominent U.S. magazine predicted the “Death of the Stock Market.”  It was written during a major downturn in the U.S. and global economies.  It was also written right before a major economic recovery that would last for years.  If you had heeded the warning of the “experts” and sold at that low point, you would have likely lost most of your investment, rather than doubling or tripling your investments over the next few years.

Another example of media (expert) bearishness is evident in this profile of thirty years of Time Magazine covers.   Almost every year for the past thirty years Time Magazine has featured a cover demonstrating how bad the present state of the economy is.  If it’s not a recession then it’s worries about inflation, or concerns about trade deficits, and on and on.  Are these accurate reflections of the economy, or are they what help to sell more issues of a magazine?

Finally, who doesn’t recall the time “monkeys throwing darts” outguessed the Wall Street Journal investing experts?

The point: the economy is too complex for any of us to fully understand.  The only thing that has been consistently true is that we can never fully anticipate the future.  Unexpected things are always occurring, and when they do, they often change everything.

When I used to read more magazines and check on my stocks every day, my risk tolerance would decrease exponentially.  Of course, being informed is important.   But at the same time, some perspective is needed.   Particularly in these modern times, where the media is known to sound the alarms a little too often.

The same thing is true for picking careers.  By the time you complete your education in the “hot” field it may be cold.  That’s why I believe there is no better compass than the one inside each of us.  Certain rules, principles and philosophies are timeless, but there are so many inputs that we still need to view everything through our own judgment—our own inner compass.

Moreover, our personal goals are often quite personal.

For instance, when my Wife and I bought our house six months ago, I figured interest rates would keep going down.  I also figured the value of the house might not have hit its bottom.  Time has proven I was correct in both these assumptions–at least where we live.   But we were tired of renting, planned on sticking in our house for the long-term, and frankly ignored the “experts.”  It may not have made good financial business sense, but we did it anyway, because that’s what we thought right under our own particular circumstances.  The thought of staying in an apartment we hated for another 2-3 years wasn’t worth the benefit of gaining more time to eye the housing market.

Likewise, when I left my job during one of the worst Recessions in U.S., history to start my own office, the empirical data suggested likely failure.  While only time will tell the ultimate outcome, so far, I’ve been happier than ever before in my chosen profession.

Conclusion 

It’s nice to be well-informed, but ultimately it’s up to us to know what’s in our own best interests.  When you go about being well-informed, it might not hurt to remember that the media can often cry wolf.  In other words, sometimes what you should fear the most: is the so called experts themselves.

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!

I’ve been really into reading lately.

For years (while I was in graduate school), I found it difficult to read outside the classroom.  Growing up I was an avid reader of just about anything I could get my hands on, but college–and to a much greater extent, graduate school—changed that.  I was so burnt out on all the assigned reading that it actually ruined my love of reading–to the point that even following graduation I rarely completed a new book.  The majority of months went by without me even giving a new book a try.  I would make excuses like “I don’t have the time,” or “I can’t find anything interesting to read.”

Thus, for years and years, my reading consisted of magazines or the sports section of the local paper (and of course blogs!).

So, I was actually quite surprised by my recent reading renaissance.  I’ve been on a reading binge like never before.

Eye Strain and Beyond! 

I started reading historical biographies on a whim.  I had a new commute and I needed something to pass the time.  To my shock, I was totally into the historical biographies.  When I used to read a lot before, it was invariably fiction (sometimes “literature” and sometimes mainstream “thrillers.”  But now here I was diving into the life of Winston Churchill or John Adams and surprisingly (and whole-heartedly) loving every second of it.  There were days (and there still are) where I can’t wait for the end of the night to come—the part of the day where I get to crawl in bed and read for an hour or so.

But now I’m panicking because I worry I’m wasting too much time lost in books.  As I’ve written before, it’s well-documented that I have a compulsive personality when it comes to hobbies.  This reading kick is surely just another expression of that.

What I’ve been worrying about the most lately, is whether reading is at all productive.  I’m also a huge sucker for trying to be productive at all times—it’s a sort of compulsion (that often results in my wasting time).

I’m almost certain reading is generally more valuable than watching 99% of television, but I’m also sure there could be more productive ways to spend my time (and even my reading time).

• What if I instead spent all my reading time focusing on work-related reading, to try and further my career and knowledge?
• What can be gained from biographies when they present just one man or woman’s unique perspectives and how they handled them?
• Is it true that, as Truman Capote once said, that a great work of fiction was more valuable than any other types of reading combined?

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