spreadsheets

Using Functions to Import Website Data

One advantage to Google spreadsheets is that it is designed to work with the Web. Specific functions allow you to load data dynamically directly from a website. 

Import a data file published on the Web into your spreadsheet

CSV files (comma separated values) can be imported directly into a spreadsheet from anywhere on the Web.  CSV is one of the most common data formats and can be found with a simple Google search. 

Type ucla crime filetype:CSV in a Google search page and you should see this in your browser:

search for ucla crime csv file

Select the green URL below the first item and copy it.

Open a new spreadsheet and click in cell A1. 

Type =ImportData("

Paste the URL after the quote mark then type ") and hit enter.

After a moment the data will load and should look like this:

 crime stats imported into google spreadsheet

Many files will not be this clean and may require cleanup. But if you can use the file as is, it's especially useful. Governments regularly update CSV files on their servers. This may happen frequently with certain files such as election results.

A Google gadget made with one of these files also updates automatically if the CSV file is updated.

Import a table or list directly from a Web page

Tables can frequently be imported directly from a Web page into a spreadsheet. Let's import the same data from the Expanded Homicide Data Table 8 used earlier. Right click on the link and select "copy link" from the menu.

Open a new spreadsheet and click in cell A1. 

Type =ImportHtml("

paste the URL after the quote mark then type "; 

Then type "table";   This tells the function to look for the <table> html tag.

Now type 1)  This tells the function to pull data from the first table. To pull data from the second table on a page, replace the 1 with a 2.

Hit enter and the spreadsheet should look like this:

import data from html table

The "table" setting can be replaced with "list" so that it will look for the contents of <ul> <ol> and <dl> tags.

Load dynamic financial data

Live data from Google finance can be imported into your spreadsheet. The data updates automatically every time the spreadsheet is loaded. Quotes can have up to a 20 minute delay, which is common for financial data. 

Create a new spreadsheet that looks like this:

Google Finance live data

Type =GoogleFinance("DJI", "price") in cell B2

Type =GoogleFinance("INX", "price") in cell B3

Type =GoogleFinance(".IXIC", "price") in cell B4

The initials at the beginning of the parentheses are stock ticker symbols. You can find the symbol for any stock at Google Finance.

The cells should update in a few moments and your spreadsheet should look like this:

Google Finance market index spreadsheet

Load historic financial data

The same function can be used to load historic data. Let's pull in the daily closing price of Google stock for 2009.

Create a new spreadsheet. 

In cell A1, type  =GoogleFinance("GOOG", "close", "01/01/2009" , "12/31/2009", "DAILY") 

Hit enter and the daily closes for 2009 should load into your spreadsheet.

To do more with the GoogleFinance function, check out the documentation page