This is designed to be a step-by-step tutorial as to how to download different types of data from the Internet into Microsoft Excel.  Upon successful importing, the tutorial will show different ways of formatting the data with the purpose of creating graphical representations of the results – pie charts, regression lines, time series analysis, etc.

Listed below are the Internet sources covered in this tutorial. Some of these links are to websites offering research services rather than carrying the data on their server.

1. http://research.stlouisfed.org/fred2
2. http://205.232.165.149
3. http://www.economagic.com
4. http://www.federalreserve.gov/releases
5. http://www.ntu.edu.sg/library/stat/statna.htm
6. http://fisher.osu.edu/fin/fdf/osudata.htm
7. http://factfinder.census.gov/servlet/BasicFactsServlet
8. http://www.nber.org/data
9. http://www.csufresno.edu/Economics/econ_EDL.htm
10. http://gis.smith.udel.edu/rdms/econ
11. http://www.helsinki.fi/WebEc/webecc8d.html
12. http://www.worldbank.org/data

Data from these website will be in one of the following formats :

1. Excel or *.xls
2. Text or *.txt
3. HTML or *.htm(l)
4. Comma Delimited or *.csv
5. ASCII or *.asc
6. Acrobat Reader or *.pdf

This section explains in detail how to prepare after downloading each of the above formats into a file ready for upload into Excel:

1. If the data file is an Excel file, you will be able to automatically open it in Excel. From there, you can delete any unnecessary headers and text to make the file into a data file with a dependent variable and independent variable.  Example shown below :
2. If the data file is a Text file, you can save that file on your desktop without changing anything.  Once saved, you open Excel, go to File/Open. This will open the Import Wizard Window, which will also display the first few rows of the data file.
 

From here you have the option of selecting which row would be the first import row (in this case I would suggest Row # 6.  The Wizard will also default to “Fixed Width” data type, which is correct for Text Files.  By changing the row number and click the next button, we get the following screen:

Excel automatically attempts to set the break lines. By scrolling down, we can verify that all columns are accounted for.  In this particular case, we have a column “CR” which doesn’t carry any data. I suggest you leave that column as it is and delete it once imported in Excel. The break lines can be moved, deleted or created simply by double-clicking on the data preview screen.  By pressing the “Next” button we get the following screen:
 
 

The screen gives us the ability to format the column (Column Data Format) as well as select whether there are any columns we do not need to import (Do not import column (skip)). If we are satisfied with the format of the data, we can click on “Finish” button, which completes the Import Process.  Here is how the end result appears:

My suggestion is to retype the titles of the columns into one cell and delete remaining rows. This will be critical when formatting the file for regression or graphical analysis.

3. If the data file is an HTML file, the easiest way is to copy the data into notepad:

After pasting it, you should save the file as a text document. Open Excel, go to File/Open and you will see the Import Wizard again :

Click on the “Next” button.

Excel will recognize the delimiters and default to Tab.  By clicking “Next” you will access the screen where you can format the columns or in this case you can automatically hit “Finish” to complete the import process.

4. Comma delimited or CSV files are very similar to Text files. Here is an example of a CSV file displayed in Internet Explorer:

 To import such a file into Excel you will go through the same steps in the Import Wizard, except you will select comma as the delimiter in the screen below:

As you can see, the file is formatted perfectly with selecting the Comma delimiter. By click Finish, the end result looks as follows:

From here you should Format the data by deleting Lines 1-7 and adjusting any other cells you deem necessary.

5. The last file type is ASCII. Here is an example of how it looks:

This type of file can be saved as text and imported into Excel in the following manner:

Leave the default set at Fixed Width.

Excel will automatically format the data. Click Finish to complete import process.

Format as necessary.

6. PDF files can’t be imported into Excel. Adobe Acrobat is software designed to create high-resolution and quality printing files, but doesn’t have any form of code break to enable us to import it into different columns and rows in Excel. Most websites offer both a PDF and some other type of files for download.

Note: The above-mentioned information attempts to cover all possible Import Processes for different data files. There is a possibility that the file will require a combination of import processes or some form of manipulation. For additional information, please refer to the Excel help menu.
 

Statistical Analysis

Once the data is imported into Excel and formatted to eliminate any unnecessary columns or fields, we can analyze the data using Statistical Tools .  Here is an example of using the Data Analysis function for running a simple regression in Excel:
 

In this worksheet we have a dependent variable Value A (Interest Rate) and independent variable Value B (Total Reserves).  We will try to determine whether there is a relationship between the two.
First, go to Tools and select Data Analysis. The following screen will appear:

From here, select regression and click OK. The following screen appears:

From here you can select the Input for Y (Dependent Variable) and X (Independent Variable).  The Confidence Interval will default to 95%. You can change this. In the output options you can select either an output range within the same sheet, a new worksheet or new workbook. You can also select additional analysis related to Residuals, Residual and Normal Probability Plots. Hitting OK will display the following information:

This data summarize the results of the regression. You have information regarding the R2, Standard Error, Sample Size, Intercept, Coefficients of Independent Variables, t-Stat, P-value, F value, etc.
If you have selected the plots you will get the following charts:

along with the following analysis:

The above table gives information regarding residual plots, etc.
Using Charts and Graphs in Excel

Excel offers a number of user-friendly options for presenting data in a graphical format.  Once you have your data table set-up, you can go to Insert/Chart:

This will give you a series of different chart type options.  Since this example is a simple XY Plot, you can use the XY(Scatter) option.

Selecting the first sub-type:

Click Next:

You can give the chart and axes names as well as format the chart.  You can also do some additional formatting of the chart by creating a legend, selecting what type of gridlines and data labels should be used. Once done with the selections, click on next:

You have an option to either put the chart in the same sheet or put it in a new sheet. In my experience it is easier to look, format and modify at a later stage charts that are put in new sheets.

You can modify chart-formatting selections at any time by simply placing the cursor on the chart and right clicking on it and then selecting which formatting option you want to modify.

Example of Data Download

One of the links listed to be used for data gathering and accumulation is the Federal Reserve Statistical Release page.  Below is the data portion of the site for historical interest rate information:

From here we click on the ASCII file format:

Go to File and Save As option and select the text file format (*.txt).  Proceed to open Microsoft Excel. Once in Excel, select File/Open and select the file you downloaded:

Click on Next:

You will notice that the first break line crosses the description column of the file. You can eliminate or add any of the break lines by simply double-clicking on the line.

Once having checked that all break lines are good click on the Next button:

This step allows you to format the data in the different columns. If the data contains formulas or links, it’s a good idea to select the “Text” option. You can also choose not to import a particular column by selecting the “Skip column” option. Once formatted as needed, select Finish to complete the import.

The data is imported.  There are some data that you might not need so you can delete and edit that information as necessary. You can also create additional columns if you want to add percentage or other arithmetic calculations. Once saving the file, make sure you select the Excel (*.xls) data format.