Archive for August, 2012

Extracting Financial Data From A Web-Based Source

Excel macros

The other day, I received a comment from a reader:

“how can i import EV/EBITDA into excel spreadsheet and to make it in a way that it will updated automatically. let’s say from a website like”

Basically, he wanted to have a list of stocks and gather data from that specific website. That seemed like a challenge I was up for:) It should actually be fairly easy to do. First off, I created a sheet with a list of symbols. Then I created a tab where the web query would be done.

Then, I only needed to create a loop that would:

-go through each name
-do the web query for that ticker
-do a vlookup to find that information

So let’s do this step by step:

#1–go through each name

#2-do the web query for that ticker

#3-do a vlookup to find that information

#4-publish that info

Here is the final result! You can download the file here

How You Can Take a Successful Person Out For Lunch

Personal finance

Everyone writes about networking and suggests that you stay in touch.

You know the deal by now. You should be networking. You should be interacting with successful people in your field. You know that party. You just don’t know what to do.

This is why today I wanted to focus on how you can successfully network when you take a successful person out for lunch or coffee so that you don’t embarrass yourself. This is a culmination of what I’ve researched and experienced myself.

Let’s get started with the lunch or networking event…

Go somewhere easy.

Nobody has time to meet you for a fancy dinner in the middle of a busy work day. A cup of coffee works because you pay in advance. You don’t want that awkward moment where you both wait for the bill to come or the have the server interrupt you a dozen times.

Ask questions the whole time.

This is your turn to ask the questions, pick this person’s brain, and get as much feedback as you possibly can on the topic. I highly suggest that you come loaded with questions ready to fire out.

Oh and there’s one thing about questions that you need to know…

Ask good questions.

Don’t ask for their best tips or advice. That’s lame and they won’t know where to start. Don’t ask general questions because you’ll get vague responses that won’t help you much.

What are questions worth asking?

  • What did you do right after high school? What did you after college? You want to see what a successful person has done right after completing their studies. This will usually surprise you.
  • What does an average day look like in your life? I wonder if there’s time for video games?
  • Who else do you work with? You can find out the other players involved in making the team work.
  • What would you do if…? Then you present a specific scenario or hopefully one that you’re experiencing yourself.

Don’t talk about yourself.

Do you remember what The Rock used to say? Know your role and shut your mouth.

This is your time to be all ears and become a sponge for information. Don’t give your input on every single comment.

Do your research.

Don’t walk in confused or clueless about what this person is all about. It’s important that you take some time to do your research and figure out exactly what this person has been working on. This will score you some bonus points. It pays to be interested. People want to know that their work is being taken seriously.

That’s how you can have a decent networking lunch with a fairly successful person. I must admit that I’m still working at this. I’m not perfect and I have a long way to go. I am trying my best!

This article was mentioned in the Carnival of Personal Finance at Nerd Wallet.

Determine Your Life Insurance Needs Thanks To This Excel Spreadsheet

Excel function tutorials

This Post Is Part Of The Life Insurance Movement Hosted by Good Financial Cents

Today I wanted to create a small spreadsheet that would help you determine how much you need in terms of life insurance. Why? Not only because it’s the cheapest type of life insurance, but also because it’s something that most of us unfortunately have to consider at some point and I thought it would be a nice way to give a few more examples of different excel functions. Let’s start with the actual case.

In general, as individuals, we buy life insurance for 2 main reasons:

-to pay off any debt
-to replace part of our income (in order to help our family or other dependents) – we will assume 50% in this case

So let’s start off with a fairly simple spreadsheet. I will ask the user to:

“Enter your annual income here”
“Enter the amount of life insurance your have”
“Enter your total debts including your mortgage here”

Then, I would start off by seeing if all debt can be paid, you can see here:


I will even add conditional formatting to have the cell in green if that first condition is met or in red if not:

Then, I will determine how much will be left and how much of that can be paid out annually over 20 years. I will assume that this amount generates a return of 4%. How will I do it? The PMT function as follows:

=PMT(rate, nb periods, pv, fv)

=PMT(0.04,20,life insurance – debts)


As you can see, this gives me an amount of $51,507 per year for 20 years, which is not bad would not entirely replace my income. Let’s give the user the option to decide the proportion of his income to be replaced:

Then I will determine if it is enough:


If it is not sufficient, I will use the PV function to determine how much is missing from the current policy:

I will display 0 if nothing is missing:


The formula I will use is:


You can see the new result here:

And as always, I invite you to download the spreadsheet here

Excel Find Function

Excel function tutorials

Sometimes, problems that seem so incredibly simple turn out to be much more difficult to solve than you would expect. A good example is the following simple looking exercise. Suppose that you have the following list of names and you need to determine both the first and last name. How would you proceed? You could do it through a macro but the easiest way is probably by using the “find” function.

What is the find function? It helps you find a specific character in a string. Here is what the formula looks like in excel:


The easiest without any doubt is the last name. We will simply find the “,” by doing this:


Then, we will use the left characters up to that point minus 1 so:


The next step is finding the first name. We can build on what we did for the last name and will also need to find the next space following that (which will be the end of the first name). Here is what it would look like:

First name starts at:


First name ends at:

=FIND(” “,A2,FIND(“,”,A2))+2

So the first name would be at:

=MID(A2,FIND(“,”,A2)+2,FIND(” “,A2,FIND(“,”,A2)+2)-FIND(“,”,A2)-2)

The country would start at the end of the first name +1 so here I will use:

=RIGHT(A2,LEN(A2)-FIND(” “,A2,FIND(“,”,A2)+2))

You can see the end result here:

And download the spreadsheet here!:)

How to Budget For a Vacation

Personal finance

Have you been dying to go on a trip for the longest time? Do you finally want to save up for a big vacation?

Whenever I talk about a past trip or one that’s coming up I get asked the usual question from curious friends.

How do you afford to travel so often?

My answer is simple.

It’s all about prioritizing. If you want to travel you need to set your priorities straight so that you can manage to pull off a trip. You need to cut back on the crap that’s not allowing you to travel.

Below is the formula that I follow when it comes to planning one of the many trips that I attend per year:

Pick a rough time frame.

The first step is to decide when you think you’ll be able to go on this trip. If you’re broke it could be a year. If you’re doing okay, it could be a few months.

The good news is that if you have a year for the trip, it’s going to be easy to save. You just need to put aside $20 a week and you’ll have over one thousand dollars in your bank account after a year. That’s good enough for most travel destinations.

Ask around to see who’s interested.

The next step is to see who’s going to attend this trip with you. Do you have a girlfriend itching to go away? Perhaps your best friend wants to ditch the winter? I recommend that you ask your close friends to see who can pull this trip off with you.

Pick a destination.

Where do you want to go? How much money are you willing to spend? How much money can you realistically put aside for this trip?

After looking around, you’ll easily find an answer to this.

Start saving.

When I have a trip coming up I try to either save more money or make more money (or a combination of both). If you want to make more money you can start freelancing now. You can also increase the hours at your current job to have more money coming in. You can sell your crap. There are dozens of ways that anyone can increase their income right now.

As for the saving money equation: just cut out the crap that’s holding you back.

Create a sub-account.

The final step involves setting up a specific savings account where you’ll hold (or hide) the money for this trip.

To keep my focused on my goal I create a sub-account with ING Direction. I label this account after the trip destination. For example, “CANCUN 2013 BABY!” is a suitable title.

That’s how you can plan for and budget for a trip.

Where are you going to go next? Make sure to take some pictures for us.