One More Reason To Get Office365

Excel 2013, Google Cloud Spreadsheets

As many of you already know, I’m a big fan of moving my data and spreadsheets to the clouds. Over the past few months, I’ve been moving many of my invoices, tax documents and more into my Dropbox account which makes it easier to find, takes up less space in my house and is easy to get rid of when the time comes.

I’ve also started moving a lot of my life to cloud-based documents, either in Office2013 (for files that use macros, specific functions or add-ins such as ycharts. Thus, I now have files that:

-keep a live picture of my investment accounts
-list different things such as travel lists, things to do, etc
-files for work
-all pictures since I’ve moved to digital
-movies and music
-ebooks
-etc

As you can imagine, the cloud is becoming a big part of my life:) That is why I was THRILLED to see the latest change to the Office365 offering. The price remains the same but now comes with 1TB of free cloud-based storage.

1TB = 1000 GB

That is likely more than you’ll need for a long time. You could have hundreds of movies and still not come close to that limit. To give you an idea.

Dropbox charges $9.99/month for 100mb
Google Drive charges $9.99/month for 1TB
Office365/OneDrive charges $9.99/month for 1TB + Microsoft Office

It’s a pretty good deal (free trial from here) and no I’m not being paid by Microsoft to write about this haha:) Although if someone from MSFT wants to send me a check, I’d welcome that!

I’d be very cusious in hearing if you’ve started converting your files to cloud? The vast majority of the Excel users on this blog have not converted to Office365 so I’ll be interested to see if that changes over time.

Gathering Financial Data in Excel With YCharts

Excel 2013, Excel function tutorials

One of the most requested subjects on this blog is getting financial information into Excel. The easiest way of course is having a Bloomberg terminal which makes it possible to access a seemingly unlimited amount of data about the economy, publicly listed companies, indexes, etc. The possibilities are incredible. Think there is a drawback? Of course. Given it has a financial professional target audience, the cost is typically between $1000-3000/month depending on the type of service you have, if you’re getting live prices, etc. Yes you read that right, it’s per month.

It seemed incredible to me that no one was trying to disrupt this market no matter how complex it is. I have written a few times about how it’s possible to get some financial data from Google finance in Google docs, cloud-based spreadsheets:

-Using Google Spreadsheets As An Alternative To Excel For Stock Prices And Information
-Managing A Stock Or ETF Portfolio From Google Docs
-How To Add Exchange Rates In Google Docs

There’s no doubt that those capabilities are very interesting and make it easy to track the value of a portfolio for example. What if you’re trying to do more though?

One clear candidate emerges: YCharts

Ycharts is a website where you can do a lot of what you’d do on a site like Yahoo finance or Google finance. You can build stock lists, look into a company’s financials, past statements, etc. The big difference is that if you join one of their paid memberships, you can get access to their very powerful excel plug-in. While not exactly Bloomberg, the possibilities with this spreadsheet are very very impressive. Look at how I’m using it personally:

I have a list of 300-400 stocks that are on my screener and represent potential purchases. I then have all of those stocks in a spreadsheet and look to get specific metrics such as market cap, price, dividend yield, P/E and some growth metrics with their custom formulas. For example, to get Blackrock’s dividend yield, I’d enter:

=YCP(“BLK”,”dividend_yield”)

Every time I refresh my spreadsheet, I’ll get updated values. This is what this specific spreadsheet looks like:

They also provide templates that you can use to either look at many companies or at one in more details:

http://ycharts.com/excel/templates

I’m guessing at this point i’ts starting to look like I was paid to write about this but I’m just a big fan of what they’re doing and have been a paying member for 6 months now.

Has anyone else given YCharts a try? If so, I’d love to get your thoughts.

Counting Number Of Unique Entries (CountIf Function)

Excel function tutorials

I received a question that made me think of something I had tried to do in a spreadsheet a few months ago but never got around to discuss on this blog. Suppose that you have a large set of data that looks like:

There are a large number of things that could be done with such a set of data but one always tricky aspect is getting answers to questions that involve multiple columns. For example, if someone were to ask you, how many different locations of the store sold at least 1 Early Grey Russian black tea box on each day? I cannot simply add up the number of sales since some stores sell multiple boxes. What could I do? I’ll add 2 columns and include the “countif” function which will give me my answer:)

First, I will create a column with a “UniqueID” with the concactenate:

Then, I will use the “countif” function to see if the ID is unique:

As you can see, all numbers that are “1″ are non-Unique. I can then manipulate that column in different ways

As always, you can download the spreadsheet here:)

Nest if Functions – It’s All About Doing It Step By Step

Excel function tutorials

I often get these types of questions where someone is trying to do a nested if function but having trouble doing so. Here is one such example:

“I am trying to write a and/if statement to read if b2=”manager” and d2=”b” and h2

Is there some help someone could provide me with this?"

The only tricky part in doing this is splitting the problem into different parts and resolving those one at a time.

Basically here, I'm looking to see if:

-all 3 conditions are met - if so, display:

The first condition is:

=IF(B1="manager",1,0)

The second condition is:

=IF(D1="b",1,0)

After nesting both together, my new formula would be:

=IF(AND(B1="manager",D1="b"),1,0)

Then, I'd add the final condition:

=IF(AND(B1="manager",D1="b",H1<=740),1,0)

Simple enough? I'll then replace "1" by under:

=IF(AND(B1="manager",D1="b",H1<=740),"under",0)

Here is a file with such an example:

How I send Outlook Email Through Excel

Excel 2013, Excel macros

One of the main things that I like to do when automating excel files is getting the whole process done. What do I mean? Not only creating a file and saving it to a drive, but also sending it out to the group that I need it to get to on a daily basis.

For example, I have this file that creates a list of tennis players:

Suppose that I run this file every day, save it to a drive and send it to 4-5 people? I can do all of that (more or less) from my excel file. How? By helping Microsoft Excel communicate with Outlook.

It’s similar to what you’d do when building any other macro. I personally use 2 different steps:

#1-Add the “standard” email funfction to my file. For this to be done, I always copy the same text to my files:

Then, I can simply ad the relevant code to my existing macros. Here is a simplified version that would send out a file:

It’s fairly straightforward and it works:) Once I click the button, an email shows up and I can simply press “send”

You can download the spreadsheet here!