A few days ago I received an email telling me that the spreadsheet that got prices from the Yahoo finance website no longer worked. One clear downside of using web queries is that they sometimes require work if the publisher changes the way the website is presented or built. In this case, the “last price” is not as easy to access. He mentioned a MSN plugin but I thought I’d try to build a new one using MSN Finance and it proved fairly easy to do. First off, I did a manual web query to see if the last price of any stock would appear (it should) and it did. One problem is that it is not written “Last Price” so what would be the best way to “find it”? I decided to look at the prior line (which in all cases is “Print Report”

So I decided to do the query in column A:

=IF(C1=”Print Report”,1,0)

I added this in A2. This simply verifies what the prior line had. Then, I need the price only, not the variation so I decided to take whatever was before the “space”. I did the following:

=IF(A2=1,FIND(” “,C2))

For that specific line, this gives me the last price.

Then, I simply needed a query that would go through each line of the page, adjust the query, find the price and add it to the page. It’s very similar to what I had done in the previous file. Here is my code:

You can of course download my entire spreadsheet here.

I’ve been discussing annuities quite a bit in recent days so i thought it would be interesting to give a shot at calculating the value of an annuity in excel. This is obviously not a perfect method and I’ll try to give you an idea of things that could be done to improve on the calcs but it does give you a good idea.

This is a very simple annuity where someone pays an amount to start receiving an annual amount at a given age until he or she dies. So basically I want to determine the cost. I need to know what the annuity would be paying at that point every year, if the amount will be adjusted for inflation, etc.

I first looked for data on life expectancy which ended up being available per country (it does of course depend on being a man or woman). Here is a sample:

Then I entered a place for assumptions:

And finally a place where I did all of my calcs:

I used two excel functions that I’ve used here before, vlookup and PV. You can see the whole details in my spreadsheet. Here is a snapshot of my answer for my initial assumptions:

I also did a tab where I prove my calcs. To be fair, there are several things that could be improved:

-life expectancy will be higher by the time a current 32 year-old dies so that number should be boosted
-other factors would help to determine if the person will live a long life (general health, etc)
-inflation and market returns could certainly be changed
-etc

You can take a look at the spreadsheet here

One of the primary ways that I use Outlook to save time at work. I’ll try to discuss some of the things that I do in upcoming posts but today I wanted to start with a fairly simple one. What is it?

Every day I send out emails with a postponed date. It’s a reminder to myself or others in the team of specific events, things to do, etc. Yes, I know that reminders can work but I personally feel like sending emails is much more effective.

So I use excel to send out these requests, it’s much easier to do and I’m able to send out tens of different ones in just a few minutes…! Take a look at the most basic spreadsheet:

Here is the code that I used:

One I click on the “send” button, the email appears in my outlook outbox:

If the date and time is already past, it is sent automatically and would appear in my sent items. There are many possibilities here such as using it for email reminders, sending emails later (no matter what the reason), etc.

You can download the spreadsheet here

I still think it’s very strange that despite Excel being one if not the most used software in the world, there is little if any attention that is paid to it in school. It makes no sense. There are many things lacking in our schools and you could probably argue that things like basic finances, investments, learning basic coding and many other subjects should be looked into.

Learning Excel basics though seems even more obvious. These days, a large proportion of jobs require working with spreadsheets which are almost entirely Excel-based. Some use very simple spreadsheets while others have incredibly complex ones or even databases using more advanced software.

Being able to use excel basic and more advanced functions, to build macros can help get ahead and while it’s true that it’s possible to learn this stuff at work, it’s certainly a lot easier if you can start earlier.

How Did You Learn To Use Excel?

Did you learn at school? At work? With Books? I’m curious, please email me or comment below!