I have to admit, I did not have much understanding of XML up until a few hours ago. But I had read about the ability to use the importXML function in Google Docs spreadsheets and it looked promising so I decided to give it a try. I did use a few resources which I’ll be sure to link to at the end of this post. So what is ImportXML? It’s a function made available in Google Docs that makes it easy to make different types of web queries

How Does It Work?

The one downside is that using it requires some understanding of the html. But it can be extremely powerful. You can see 2 examples here:

#1-Imagine that I’m trying to get access to the list of all links in this page on a blog that I visit:

It’s certainly possible for me to get them all one by one or to download them in one way. Another option though is to do a simple ImportXML after seeing that these links were located inside of this code:

I did the following query:

=importxml(“http://www.smartpassiveincome.com/archives/”,”//div[@class=’format_text’]//li/a/@href”)

And you can see the result here:

How about another use? I have a few dividend-related sites and imagine that I’d need to know the top 20 sites for the keyword: “dividend investing”

I added the keyword in cell A1 to make it easy to change and then used the following function in B1:

=”http://www.google.com/search?hl=en&gl=US&num=20&q=”&A1

This game me a list of 20 websites. Ideally, I wanted to have clickable links so I used the “find” function which I’ve discussed in Excel and then the “mid” function, you can see the result here:

Resources: ImportXML Guide for Google Docs (http://www.distilled.net/blog/distilled/guide-to-google-docs-importxml/#chapter1)

One of the features that I use quite a bit in Excel as most of you know is the ability to do web queries. This can be done either manually or through macros. I was happy to find out that to some extent, it can now also be done in Google Docs spreadsheets. Obviously, since macros cannot be used in Google docs, the only way is to do queries directly.

So suppose you’d like to get access to the price of a security on Yahoo Finance, let’s imagine Google (GOOG), the price can be found at:

http://finance.yahoo.com/q?s=GOOG

Then, you could simply use the following in your Google docs spreadsheet:

=index(importhtml(“http://finance.yahoo.com/q?s=CLF.TO”,”table”,2),1,2)

Most of that formula is generic and easy to understand. The tricky part is knowing which table to refer to. I’d say that in all honesty the best solution is often to just try. You can get a bit more info from Google’s help here:

http://support.google.com/drive/bin/answer.py?hl=en&answer=155182

Then, you can simply add a few different elements to make sure that the data refreshes at a regular interval, for example, if you’d like the data to refresh every hour you could:

Add this to cell B2:

=Minute(Now())

And then adjust the formula to:

=index(importhtml(http://finance.yahoo.com/q?s=GOOG&B2,”table”,2),1,2)

As you can see, the possibilities are significant

A few weeks ago, I took a look into how you could extract stock prices fairly easy to excel from a website such as Yahoo finance. It’s fairly straight forward. Even easier though is doing this through Google Spreadsheets, the leading cloud spreadsheets solution. There are benefits and downsides but to me it’s a great product to do this. Why?

-Prices are updated automatically!! Google Docs can access data from Google finance if you use the right functions and get live pricing. This enables you to have a live price, live market values and asset allocation, etc.
–It’s very easy to use: Once you know the few formulas that you need to use, it becomes very easy to work on the spreadsheet, add positions, etc
–It’s Cloud Computing: I’ve written about this but the reality is that being able to access your spreadsheets from any location is a tremendous benefit.

You can see an example of a spreadsheet here:

Try to take a look at any point while North American markets are open (9:30AM-4PM EST) and you will see all of the numbers being updated live! This is something that is simply not possible in Microsoft Excel without adding data subscriptions or subscribing to a service like Bloomberg which is very expensive. You can go see the spreadsheet here:)

A few months ago I discussed using Google docs and provided a spreadsheet that updates stock prices live. One major selling point of course of using cloud based software is that it is:

-backed up automatically
-accessible from any location
-can be used by multiple users without always sending updated versions by email

Excel Still Heads And Shoulders Above Competition

That being said, nothing comes close to how broad excel has became over the years. As the writer of an excel blog, you can probably imagine that since 95% of what I write about is not yet possible on alternatives like Google docs, I remain a very heavy excel user.

Excel In The Clouds

I do know that Microsoft is working on some cloud based version of excel and that might end up being what we’ll eventually all be using. In the meantime however there are alternative methods. Using software such as Dropbox or Google Drive, you can have those services automatically sync specific folders on your computer. How does it work?

1-You install their software and select a directory
2-Each time you update a file in that directory and save it, the updated version will be updated on the web version but also on linked devices.

By doing that you get several of the benefits of cloud computing such as;

-automatic backups
-possible to open your file from any location that is connected
-all of the powerful excel features
-you can also sync other files such as photos, documents, etc

There are some drawbacks though compared to using a solution like Google docs:

-can’t have simultaneous users working on the same file at once
-the file must be saved in order for the web to have the most recent file

I personally have been using Dropbox and am incredibly happy with it. Google Drive works fine too but I’m sticking with Dropbox for now. There are also alternatives offered by Microsoft and others.

Have you been using such services and if so how has been your experience?

I’m obviously a huge user of excel and have been using it increasingly as I continue to improve my skills in both building macros and using more advanced excel functions. That being said, I’ve also been using Google Docs Spreadsheets increasingly in recent years as a general move towards the clouds. Why? Here are the main reasons:

–Sharing: Without a doubt, this is the #1 reason! Instead of constantly sending files to my co-workers, we can both work on the same spreadsheets, sometimes at the same time by using Google docs. This ends up making a huge difference

–Available from anywhere: Being able to reach my documents, modify them from home, work, school, and more adds so much convenience, I do not have to worry about having the most updated version

–Easy to use: No matter what type of device I’m using, I can usually start working on a spreadsheet within minutes without having to worry about the version that I am using or anything of that nature. It also helps avoid the issues involved in working with Mac versions of excel, with different versions, etc.

–Special Uses: There are many very innovative and cool ways to use Google spreadsheets. I have used them to create surveys for my users, to publish data to websites, etc.

Things That Are Missing

That being said, I still use excel for most of the more complex things. Why?

–Functions: Google docs spreadsheets do have several functions but not anywhere near as many as Excel does. There are some special ones that work amazingly well, especially in regards to gathering financial data, but overall it remains much weaker. Hopefully that will change over time.

–Macros: While in theory, it’s possible to build macros for Google docs, it’s not anywhere close to being “user friendly” for the moment

–User Interface: I still feel like it is still much easier to view Excel documents, probably simply because the web browser that I use to view Google docs does take out some of the space in my screen.

What About You?

Have you started using Google docs? If so, in what ways?