Introduction

On the basis of past experience, it has become clear that the published course synopsis (reproduced below) covers too wide an area to focus on during one semester. Because students have already learned about public affairs reporting and public journalism in other journalism courses, this course will now place much greater emphasis on data analysis techniques using spreadsheets and database managers. While students are encouraged to obtain databases from local government agencies, time restraints preclude the coverage of local government meetings or the inclusion of lectures on social science research methods.
 

Course synopsis

This course is designed to achieve the following outcomes: practical experience in using computer-assisted reporting (C.A.R.) techniques as well as social science research tools to write at least two in-depth stories; experience in covering at least two local government meetings; and an understanding of the theory and practice of public affairs reporting, as well as the emerging concept of public journalism.
(The Bulletin 2006-2007)
 

Objectives

• The online sources available to gather information on public agencies and public figures. 
• The software tools available for large-scale data analysis
• The prospects for journalists to go beyond the local and toward the global  

• Analyzing data available from online and offline sources to locate trends for in-depth reporting.
• Using Excel spreadsheets and Access database manager as essential reporting tools 
• Using C.A.R. techniques to produce relevant graphs, charts and tables as part of reporting. 
• Finding links to gather local, national and international data to create stories 

3. To develop professional attitudes relating to: 

• Meeting deadlines and observing journalistic ethics. 
• Appreciation of computer assisted reporting 

Text:

Assessment:

1. Nine weekly exercises as specified in the class schedule (600 points)
2. One International CAR Project as described below       (200 points)
3. One Local CAR Project (text and graphics)                  (100 points)
4. Individual class presentations                                         (100 points)

Weekly Exercises:
Complete the suggested tasks for each week within that week. No handwritten answers or loose pages are acceptable. Clearly mention the exercise category (e.g., Weekly Exercise 3) on the first page. If the exercise contains multiple items, number each answer sequentially. Retain each weekly assignment on your jump disk. Grades assigned each week..

International CAR Project.
Refer to Gunaratne, S. (2003), An evolving triadic world to see how I used Excel and Access to analyze international data to produce a pivot table and bubble graphs.
Beginning Week 12, we will work on the International CAR Project to analyze ITU data on population, mainline and cellular telephones, Internet hosts, and personal computers  to create an Information Society Power Index. We hypothesize the ISPI to show an astounding gap between center countries and periphery countries.
Before we can analyze the data, we need to design a master list of countries and territories identified by categories that we intend to use for data analysis. We will create the master list on an Excel spreadsheet by copying on to Column A all the countries/territories listed on ITU data tables available in PDF format. We will use columns B and C  to identify each country by continent (e.g., Africa) and subregion (e.g., East Africa) respectively. Column D could identify the countries representing the three economic power centers of the world (i.e., NAFTA, EU, and Asia-Pacific). Column E could identify the OECD countries--"the rich man's club." Column F could identify the G-7 countries--the top economic powers of the world. You will use this master list in conjunction with Access to analyze your data by one or more of its categories. Save this spreadsheet as "MasterList.xls."
Next, open another Excel spreadsheet and copy on to Column A the identical list of countries in MasterList.xls. Copy on to Column B the ITU data on population for each country. Use Column C to enter data on mainline and cellular telephones; Column D for data on personal computers, and Column E for Internet hosts. Save this spreadsheet as "ISPIdata.xls." 
We will use these data to create an Information Society Power Index.
Finally, we shall use the data analysis power of Access to bring together these two files for "matchmaking" and creating a sophisticated pivot table
Note down the newsworthy points you see in the pivot tables and highlight them in a short feature devoid of technical jargon. Do not clutter your
write-up with figures. Use a sidebar to explain the methodology.
All material submitted should be of publishable quality.

Local CAR Project (Week 5: Outline due; Week 13: Final project due]
Early in the semester, start building a database that would lead to writing a localized CAR feature. You can study sample reports in the NICAR Data Library to get a sense of the type of data available from government and other sources and how you can analyze them to suit your locality. Interview authoritative sources to back up and humanize your story. Your graphics (pivot tables and charts) should meet the standards of publishability, and must include caption, explanation, identifying labels of graphic elements, and source/s. No handwritten insertions are acceptable.

Individual Presentations
During the final meeting or earlier, each student shall make a class presentation of his/her local CAR project, including the pros and cons of his/her CAR experience. 

Class Schedule

Week 1 

Aug. 27
Read: Houston, Ch. 1: High-Tech Journalism 
Explore: http://www.ire.org/carbook/chap1.html, IRE Resource Center and NICAR Data Library. 
Activity: Download the record layout of the Home Mortgage Disclosure Act data, as well as the Excel file and the CSV (comma separated value) file containing the data. Figure out how the record layout enables you to identify the data in the two files. You should have a folder to keep your weekly output of class work.

Aug. 29
Weekly Exercise 1 (for grading): Complete the first three suggested tasks (Houston, p. 13) during the class period. I suggest that you select one story (rather than three) from each of the two categories, and type your explanation on a separate sheet. Submit printouts of the two stories and your explanation as a coherent package.

Week 2 

Sept.  3 (Labor Day)

 
Sept.  5
 Read and practice: Houston, Ch. 2: Researching and Finding Data on the Internet
Activity: You should download the data referred to in the chapter (through Chapter 2 Web site) and follow the steps as you read the chapter. If you fail to complete the chapter by the end of the class session, you need to finish everything before you attend class Monday.

Week 3 

Sept. 10
Weekly Exercise 2 (for grading). Print out (no more than two pages of ) each of the following.
1. Find the Healthy life expectancy data at the World Health Organization Web site and download that file and open it in Excel.
2. Find the Total Midyear Population for the 1950-2050 table at the U.S. Bureau of Census site, download it, and import it into Excel.
3. Find the latest table for U.S. International Trade in Goods and Services, download it, and import it into Excel.
(Hint: Go to Links in Chapter 2 Web site to find URLs) 

Activity: Time permitting, find one Minnesota state Web site that has state data, and import the data to an Excel sheet  (Houston, p. 47, Exercises 1-3)

Sept. 12
Read and practice:  Houston, Ch. 3: Spreadsheets, Part 1. Explore NICAR
Activity: Those who are new to Excel will have to spend extra time to learn how spreadsheets can quickly calculate percentages, averages, rates, ratios, ranks, and other numerical functions. Everyone should practice the salary comparison exercises on pp. 49-65 by downloading crony.xls file available on Chapter 3 Web site. Download the Jordan.xls file from the same site to practice the exercises on averages on pp. 66-69.

 Week 4

Sept.  17
Read and practice: Complete work on Houston, Ch. 3 Spreadsheets, Part 1..
Activity: Download the CSV files on sewers and Healthy Life Expectancy (supplementary exercises on Ch. 3 Web site) into Excel sheets. Prepare for Wednesday's big assignment                        

Sept.  19
Weekly Exercise 3 (for grading): Obtain a list of all MSUM employees and their current salaries in electronic form so you could download the data into an Excel spreadsheet. Then, each student should do the following:
1. Sort the list in descending order of salaries. Create a table of the Top 20.
2. What is the median salary of MSUM employees?
3. Compare the median salary of men with that of women.
 

Week 5 

Sept.  24
Read and practice: Complete work on Houston, Ch. 4: Spreadsheets, Part 2 (pp.72-84).
Activity:  Download the data referred to in this chapter available as Excel files on Chapter 4 Web site. Then follow the author's instructions on how to use spreadsheets for calculating rates, ratios, and subtotals; as well as for ranking and filtering. It is very important to understand the differences among rates, ratios, and percentages.

[Submit outline of your local CAR project]

Sept.  26
Weekly Exercise 4 (for grading)
1. Please use the Homicide data (in Supplementary Exercises on Ch. 4 Web site) and follow along from page 73 to page 81 to do rates, ranking and filtering.
2. Please use the Drugs data (in Supplementary Exercises on Ch. 4 Web site) and follow along from page 82 to page 84 to calculate a rate and ratio.
3. Find the Bureau of Justice Statistics Web site and download data into a spreadsheet on crime by year in the United States.
 

Week 6 

Oct.  1
Read and practice: Houston, Ch. 4: Spreadsheets, Part 2 (pp.85-100)                            
Activity:  Download the data referred to in this chapter available as Excel files on Chapter 4 Web site. Then follow the author's instructions on how to use spreadsheets for calculating subtotals, and generating pivot tables, charts and graphs. It is essential that your tables, graphs and charts contain headings, sources, and descriptors. Whenever possible, format each table, chart or graph to fit into a single page in conjunction with the Page Setup command.

Oct.  3
Weekly Exercise 5 (for grading)
1. Use the Exxon data (in Supplementary Exercises on Ch. 4 Web site) and follow along from page 85 to page 93 to do subtotals and to create a pivot table.
2. Use the Crony data and follow along from page 94 to page 95 to create a bar chart.
3. Use the Sewers data and follow along page 95 to page 96 to create a pie chart.
4. Use Exxon data from the pivot table exercise and follow along from page 95 to page 98 to create another pie chart.
 

Week 7 

Oct. 8
Read and practice: Houston, Chapter 5: Database Managers, Part 1.
Activity: Go to data referred to in the chapter (on Ch. 5 Web site) and save the PrezRace.mdb database file to your disk. Then, open the file on Access, and  follow the author's instructions (pp.104-124. (Note: If you are using the latest version of Access, the instructions to return to Design View shown in Fig. 5.15 may cause confusion. In that case, get to Design View via  View menu.)  The query mode of Access is supremely important for data analysis. Via the query mode, you learn how to do selecting and searching, filtering, sorting, wildcards, and grouping,

Oct.  10
Weekly Exercise 6 (for grading): Go to Supplementary Exercises on Ch. 5 Web site and save to your disk the PDF file "The Campaign Finance Maze Exercise 1" and the Access data file containing TENNGIVE and TNCANDS. Open the data file on Access and complete the exercise spelled out by Houston in the PDF document. Submit no more than the first page of each of the formatted tables you created.

Explore FEC for data on campaign finance

Week 8 

Oct. 15  (Fall Breather)


Oct. 17
Read and practice: Houston, Chapter 6: Database Managers, Part 2

Activity:  To follow the author's initial instructions on "intentional" matchmaking (pp. 128-130), go to data referred to in the chapter (on Ch. 5 Web site) and get the missourimoney.mdb database file. The author has supplied no data file for the Enterprise Matchmaking section. Go back to the PrezRace.mdb Access database you used in the previous chapter and follow the instructions to complete the Structured Query Language section (pp.133-140).

Week 9 

Oct. 22
Activity: Continue to practice matchmaking and SQL

Weekly Exercise 7 (for grading): To demonstrate your database skills, complete the exercises in the PDF document "Access Q&A: Analyzing Richmond Crime Reports" by Sarah Cohen  in the Additional Exercises from IRE and NICAR section on the Ch. 6 Web site. Download to you disk both the PDF document and the related Access file, which contains two tables titled "Crimes" and "Tracts,"  before proceeding with the analysis.

Oct. 24
Read and practice: Houston, Ch. 7: Getting Data Not on the Internet 
Activity: Explore each of the links recommended for this chapter.                                                             

Week 10

Oct. 29
Read and practice:  Houston, Ch. 8: Building Your Own Database                                                     

Oct.  31
Activity: Analysis of census data by county, census tract, and block group to write local stories
Weekly Exercise 8 (for grading): Using census tract data for Cass County, N.D., or Clay County, Minn., document the distribution of Asians and Pacific Islanders in the county. Create a spreadsheet table showing the total population and the number of A&PI people in each tract. Rank order the tracts in ascending order of the proportion of A&PI people. (Go back to Ch. 3 if you cannot recall how to calculate proportions.)

Browse: 50 C.A.R. ideas for your beat 

Week 11 

Nov.  5
Read and practice: Houston, Ch. 9: Dirty Data                                
Activity: Deeper exploration of  of campaign data downloadable from the FEC Web site

Nov.  7
Weekly Exercise 9 (for grading): FEC data analysis. Using Access, find out the total individual contributions by state for a Senate or House candidate during the 2003-2004 election cycle. Produce a Pivot chart and table sorting the results in descending order. Also, identify  the top 10 contributors for the same candidate..

Week 12

Nov. 12  and  14
Read: Houston, Ch. 10: Doing the Computer-Assisted Reporting Story

Activity: Start work on the International CAR Project (for grading)
First, create master list of countries on spreadsheet. List countries in Col. 1. For each country, identify continent/region in Col.2; sub-region in Col. 3; membership in economic union (EU, NAFTA, ASEAN, etc.) in Col. 4; and principal trade partner (exports) in Col. 5. Where applicable, identify membership in OECD in Col. 6, and  membership in G-7 in Col. 6. Guide: http://www.geographyiq.com/world.htm

Week 13 

Nov. 19                          
Second, download country data on population, Internet hosts, personal computers, mainline telephones and cellular telephones from International Telecommunications Union: http://www.itu.int/ITU-D/ict/statistics/.

Analyze the world distribution of information technology using the categories in the master list. The names and spelling of countries imported with data must be identical with those in the master list to do the analysis with Access and Excel. Produce sophisticated pivot tables and pivot charts to highlight the results. Write a feature story for a metropolitan newspaper or an appropriate magazine.

[Deadline for submitting local CAR project]

Nov. 21  (Fall Break)

Week 14

Nov.  26 and  28
Activity:  Continue with International CAR Project
 

Week 15

Dec.  3  and   5
Activity: Complete International CAR Project
Individual presentations

Week 16 

Dec. 10
Activity: Individual presentations

Dec. 12 (Study Day)

Final Meeting:  3 p.m., Dec. 17  MC405 Computer-Assisted Reporting