Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses trial
to watch this video
Build a spreadsheet using real world data. Learn how to find and import data from existing data sources on the web.
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
[MUSIC]
0:00
In this stage, we're gonna build
a spreadsheet using real world data.
0:05
We're gonna start
completely from scratch and
0:09
walk through the whole process together.
0:11
Specifically, we're gonna look at personal
consumption expenditures for the US.
0:13
And determine if what US consumers are
spending money on has changed over time.
0:18
For example,
as a percent of total expenditures,
0:23
are Americans spending more or
less on cars than they did 20 years ago?
0:26
How about 50 years ago?
0:31
What about healthcare or clothing?
0:32
Let's build a spreadsheet together that
will allow us to answer those questions
0:35
and also many more.
0:39
There are many sources of data on
the Internet that you can utilize for
0:42
building spreadsheets and
conducting analysis.
0:45
Most governments have agencies,
sometimes multiple agencies,
0:48
responsible for collecting and
reporting information.
0:51
For example, the Bureau of
Labor Statistics in the United States.
0:54
Non-governmental agencies also provide
research and data that could be used for
0:58
analysis.
1:02
For example,
the Asian Development Bank or the OECD.
1:04
In today's exercise, we're gonna work with
data from the Bureau of Economic Analysis,
1:08
a unit of the US Department of Commerce.
1:12
They have a wealth of data
that is available for
1:15
anyone on the Internet to download for
free.
1:17
So here we are at the homepage of
the Bureau of Economic Analysis,
1:20
also known as the BEA.
1:24
We want to pull data on personal
consumption expenditures.
1:26
So we're gonna go over to
the Interactive Data section of the site.
1:29
Since we're interested in US data,
1:35
we're gonna go to the National Data
section on Personal Income.
1:37
There's a lot of information available
here and throughout this site.
1:42
I know that I wanna start using data,
though, so clicking here.
1:45
And since we're looking for
personal consumption expenditures,
1:49
we know that we could go to Section
2- Personal Income And Outlays.
1:52
And then scroll down to the table
that we're interested in.
1:57
We are looking for a Personal Consumption
ExpendItures by Type of Product, so
2:01
we click on this table.
2:04
And now we're brought here.
2:07
So this has data for 2008 through 2015.
2:11
I want a longer data set, so
I'm going to click to Modify the data.
2:14
And I'm gonna scroll and I'm just
gonna pull all the way back to 1929,
2:20
cuz, hey, why not?
2:23
And here we are,
2:28
the table on the site has been updated to
include data all the way back to 1929.
2:29
We can see the different rows of personal
consumption expenditure categories.
2:35
There's Goods, then beneath,
that subcategories of Durable Goods, and
2:40
then beneath that different
kinds of Durable Goods.
2:44
Motor vehicles and parts,
Recreational goods and
2:47
vehicles and a bunch of items within that.
2:51
But we're not gonna work
by looking at this table.
2:55
We want to build a spreadsheet.
2:58
So we wanna download this table to
create a spreadsheet ourselves.
2:59
We have three options.
3:03
We can download in Excel format,
we can download as CSV, and
3:04
we can download a PDF.
3:08
Since we're working with Google Sheets in
this course, let's download the CSV file.
3:10
Okay, so we've downloaded our CSV file.
3:16
Let's go over to Google Sheets.
3:20
And begin importing that
CSV into Google Sheets.
3:22
Here were are in our brand
new blank spreadsheet.
3:27
And we want to import the CSV that we
just downloaded in this new spreadsheet.
3:29
And here we are.
3:49
We have all the data from
the BEA in our Google sheet.
3:50
In the next video, we're gonna
get started with formatting this
3:55
based on the best practices we've
learned previously in this course.
3:58
You need to sign up for Treehouse in order to download course files.
Sign upYou need to sign up for Treehouse in order to set up Workspace
Sign up