Web Reader#
Overview#
Web Reader is a tool to import data from website (e.g., Yahoo Finance) to Excel worksheets with (optional) data sampling and (optional) advanced settings. This tool is commonly used when users want to get data from a specific online data source. Using Web Reader, the user can simply search for keywords within a certain data source and import data by selecting matching tables from search results and additional parameters (if applicable) for generating data.
Web Reader requires the following inputs and outputs:
A data source selected from the data source list and an API key if applicable, with an output worksheet;
A key word to be typed in search box to find relevant tables within the data source;
Tables selected from search results and additional parameters (if applicable) chosen for generating the data;
Columns the user wants from the table and an (optional) index column to be assigned;
(Optional) Various inputs to perform data sampling;
Additional checkboxes for various settings (e.g., removing empty rows in the table)
Currently, available data sources include “American Community Survey 1-Year Detailed Tables (ACS),” “American Community Survey 1-Year Subject Tables (ACS),” “Federal Reserve Economic Data (FRED),” “Surveys of Consumers, University of Michigan (SCSMICH),” “U.S. Bureau of Labor Statistics (BLS),” “U.S. Bureau of Economic Analysis (BEA),” and “Yahoo Finance.” Some data source may need a credential key to import their data, such as FRED. Apply for a credentials key on their website in advance. And the user can save their credentials keys for future use.
Here are some links/websites containing overviews or raw data of each data source for users’ reference.
Census: https://data.census.gov/cedsci/table?d=ACS 1-Year Estimates Detailed Tables
FRED: https://fred.stlouisfed.org/
Surveys of Consumers, University of Michigan (SCSMICH): https://data.sca.isr.umich.edu/data-archive/mine.php
U.S. Bureau of Labor Statistics (BLS): https://beta.bls.gov/dataQuery/find?removeAll=1
U.S. Bureau of Economic Analysis (BEA): https://apps.bea.gov/itable/index.cfm
Compared to Worksheet Reader and File Reader, Web Reader doesn’t need existing data files/sheets
Videos#
Examples#
Example 1: Read Federal Reserve Economic Data (FRED)#
Here is a step-by-step example of using Web Reader to import GDP data from FRED.
Prepare an Excel worksheet to store the output data.
Open Finplicity, and then click Data Reader > From Web. The form of web reader will appear as below.
Notes: The first two steps is the same across all the examples, and thus will be hidden in other examples shown later.
In Step 1, use the dropdown menu to select Data Source. In this example, select “Federal Reserve Economic Data (FRED)”. And you will see this form as shown below.
Continue completing Step 1, fill in the FRED credentials key. If you want to add new keys, click “add new keys” to enter a new key and check “save to my keys” before clicking “Add” to avoid typing in every single time. If you want to select from saved keys, click “choose from my keys” and select from there. Below are two examples to enter keys. The first one is by adding new keys. And the second one is by selecting from saved keys.
And after either selecting from existing keys or adding a new key, the form looks like below.
Continue completing Step 1, select the Excel cell for output location. In this example, select “A1”. Now you have finished the first step in this form as shown below, and you can click “Next” to continue.
In Step 2, type the string which you want to search for. In this example, type “GDP” and click “Search” button. The form shows as below.
Continue completing Step 2, the search results from FRED will appear in the left box. You can click the series name in the box to see its metadata. Then, move your interested series to the right box. In this example, select “Federal Debt: Total Public Debt as Percent of Gross Domestic Product(GFDEGDQ188S)” and “Gross Domestic Product(GDP)”, and move them to the right box. Now you have finished the second step in this form as shown below, and you can click “Next” to continue.
Notes: some data sources only allow you to select one result from the left box, which you can refer to Example 2.
In Step 3, you will see whether you need to select parameters for the selected data series. For FRED, no additional parameter selection is required. So you can click “Next” to continue.
Notes: some data sources have additional parameters for you to choose from at Step 3, which you can refer to Example 2.
In Step 4, click “Retrieve Data” button to get a preview of the data series you selected. The form will show as below.
Continue completing Step 4, select the columns you want to import by adding corresponding headers from the left column to the right column. You can click left/right arrow to move the selected headers to the left/right side or click “Remove All” / “Add All” to move all headers to the left/right side. In this example, click “Add All” to select all headers. Then, select the “index.” Imported data will be sorted based on the selected index. If nothing is selected, the original order of the data will be preserved. In this example, select “Time”. Now you have finished all the required steps in this form as shown below.
Now you have finished all the required steps in this form, and you can click “Run” to import data or click “Save” to save this task. Below is the imported data. As you can see, “Time” column is the index to sort the data. Then we will learn how to sample the data in Step 5 and how to set advanced settings in Step 6.
In Step 5, change the settings for sampling. This step is optional and there is no sampling by default. In this example, use the following settings.
Import data with sampling: whether to enable sampling
Sample Without Replacement: do not allow to sample the same row for multiple times.
Block Length: the number of consecutive rows selected at a time
Optimal Block Length: optimal block length based on total number of rows
Sample With Replacement: allow to sample the same row for multiple times.
Block Length: the number of consecutive rows selected at a time
Optimal Block Length: optimal block length based on total number of rows
Sample Size: the number of imported rows
Sample Fraction: the number of imported rows as a fraction of total number of rows
Random Seed: use a random seed to control sampling (which yields random results)
Set Seed: use a custom seed to control sampling (which yields deterministic results)
In Step 6, change the advanced settings or use the default settings. This step is optional and there are some settings by default. In this example, use the following settings.
After choosing the settings at Step 5 and Step 6, now you have reached the end of the form. Go back to Step 1 to change the output location to F1, and then click “Run” button to generate the data. You will see the below result.
Example 2: Yahoo Finance (Historical Market Data – Daily)#
Here is a step-by-step example of using Web Reader to import “AAPL” historical daily market data from Yahoo Finance.
After opening the output excel workbook and the web reader form as in Example 1, let’s start from Step 1 in the form. Use the dropdown menu to select Data Source. In this example, select “Yahoo Finance”. And select the output location to be “L1.” Now you have completed Step 1, and you will see this form as shown below.
In Step 2, type the string which you want to search for. In this example, type “aapl” and click “Search” button. The form shows as below.
Continue completing Step 2, the search results from Yahoo Finance will appear in the left box. You can click the series name in the box to see its metadata. Then, move your interested series to the right box. In this example, select “Apple Inc.(AAPL)”, and move them to the right box. Now you have finished the second step in this form as shown below, and you can click “Next” to continue.
Notes: this data sources only allow you to select one result from the left box. So after you make one selection, the left box will be gray out, you need to remove the selection from the right before adding a new one.
In Step 3, select the data you want to retrieve for the stock AAPL from the drop-down list named “Method”. In this example, choose “historical market data – daily”. And then fill in the “Date Range.” In this example, choose start date as “1/1/2024” and end date as “2/10/2024.” Now you have finished the third step in this form as shown below, and you can click “Next” to continue.
In Step 4, click “Retrieve Data” button to get a preview of the data series you selected. And after the preview shows up, select the columns you want to import by adding corresponding headers into the right column. You can click left/right arrow to move the selected headers to the left/right side or click “Clear All” / “Add All” to move all headers to the left/right side. In this example, click “Add All” to select all headers. Select the “index” to be “Time.” Now you have finished all the required steps in this form as shown below.
Now you have finished all the required steps in this form, and you can click “Run” to import data or click “Save” to save this task. Below is the imported data. It is the same as in Example 1 to sample the data in Step 5 and set advanced settings in Step 6. Here we skip those two optional steps.
Example 3: American Community Survey 1-Year Detailed Tables (ACS)#
After opening the output excel workbook and the web reader form as in Example 1, let’s start from Step 1 in the form. Use the dropdown menu to select Data Source. In this example, select “American Community Survey 1-Year Detailed Tables (ACS)”. And select the output location to be “V1.” Now you have completed Step 1, and you will see this form as shown below.
In Step 2, type the string which you want to search for. In this example, type “population” and click “Search” button. The form shows as below.
Continue completing Step 2, the search results from ACS will appear in the left box. You can click the series name in the box to see its metadata. Then, move your interested series to the right box. In this example, select “Sex by Age(B01001)”, and move them to the right box. Now you have finished the second step in this form as shown below, and you can click “Next” to continue.
Notes: this data sources only allow you to select one result from the left box. So after you make one selection, the left box will be gray out, you need to remove the selection from the right before adding a new one.
In Step 3, select the data you want to retrieve for the stock AAPL from the drop-down list named “State”. In this example, choose “CA”. And then optionally choose from the “County” drop-down if you want to request county-level data. In this example, leave it unselected since we want to get state-level data. Now you have finished the third step in this form as shown below, and you can click “Next” to continue.
In Step 4, click “Retrieve Data” button to get a preview of the data series you selected. And after the preview shows up, select the columns you want to import by adding corresponding headers into the right column. You can click left/right arrow to move the selected headers to the left/right side or click “Clear All” / “Add All” to move all headers to the left/right side. In this example, click “Add All” to select all headers. Select the “index” to be “Time.” Now you have finished all the required steps in this form as shown below.
Now you have finished all the required steps in this form, and you can click “Run” to import data or click “Save” to save this task. Below is the imported data. It is the same as in Example 1 to sample the data in Step 5 and set advanced settings in Step 6. Here we skip those two optional steps.
Example 4: U.S. Bureau of Labor Statistics (BLS)#
After opening the output excel workbook and the web reader form as in Example 1, let’s start from Step 1 in the form. Use the dropdown menu to select Data Source. In this example, select “U.S. Bureau of Labor Statistics (BLS)”, add/choose an API key following Example 1, and select the output location to be “A1.” Now you have completed Step 1, and you will see this form as shown below.
In Step 2, type the string which you want to search for. In this example, type “labor” and click “Search” button. The form shows as below.
Continue completing Step 2, the search results from BLS will appear in the left box. You can click the series name in the box to see its metadata. Then, move your interested series to the right box. In this example, select “(Seas) Labor Force Participation Rate(LNS11300000)”, and move them to the right box. Now you have finished the second step in this form as shown below, and you can click “Next” to continue.
Notes: this data sources only allow you to select one result from the left box. So after you make one selection, the left box will be gray out, you need to remove the selection from the right before adding a new one.
In Step 3, there is no required parameters. So you have finished the third step in this form as shown below, and you can click “Next” to continue.
In Step 4, click “Retrieve Data” button to get a preview of the data series you selected. And after the preview shows up, select the columns you want to import by adding corresponding headers into the right column. You can click left/right arrow to move the selected headers to the left/right side or click “Clear All” / “Add All” to move all headers to the left/right side. In this example, click “Add All” to select all headers. Select the “index” to be “Time.” Now you have finished all the required steps in this form as shown below.
Now you have finished all the required steps in this form, and you can click “Run” to import data or click “Save” to save this task. Below is the imported data. It is the same as in Example 1 to sample the data in Step 5 and set advanced settings in Step 6. Here we skip those two optional steps.
Example 5: U.S. Bureau of Economic Analysis (BEA)#
After opening the output excel workbook and the web reader form as in Example 1, let’s start from Step 1 in the form. Use the dropdown menu to select Data Source. In this example, select “U.S. Bureau of Economic Analysis (BEA)”, add/choose an API key following Example 1, select a data category from the dropdown list, and select the output location to be “E1.” Now you have completed Step 1, and you will see this form as shown below.
In Step 2, type the string which you want to search for. In this example, type “gross” and click “Search” button. The search results from BEA will appear in the left box. You can click the series name in the box to see its metadata. Then, move your interested series to the right box. In this example, select “Value added by Indus…(5)”, and move them to the right box. Now you have finished the second step in this form as shown below, and you can click “Next” to continue.
Notes: this data sources only allow you to select one result from the left box. So after you make one selection, the left box will be gray out, you need to remove the selection from the right before adding a new one.
In Step 3, select the parameters from given dropdown lists. You will need to select from each dropdown list displayed in the page. In this example, choose “Annual” as the frequency and “Agriculture, forestry, fishing, and hunting (A, Q)” as the industry. Now you have finished the third step in this form as shown below, and you can click “Next” to continue.
In Step 4, click “Retrieve Data” button to get a preview of the data series you selected. And after the preview shows up, select the columns you want to import by adding corresponding headers into the right column. You can click left/right arrow to move the selected headers to the left/right side or click “Clear All” / “Add All” to move all headers to the left/right side. In this example, click “Add All” to select all headers. Select the “index” to be “Time.” Now you have finished all the required steps in this form as shown below.
Now you have finished all the required steps in this form, and you can click “Run” to import data or click “Save” to save this task. Below is the imported data. It is the same as in Example 1 to sample the data in Step 5 and set advanced settings in Step 6. Here we skip those two optional steps.
FAQ#
Where can I get the FRED credentials key?
You can visit https://fred.stlouisfed.org/docs/api/api_key.html in a browser to apply for a FRED credentials key.
Where can I get the BEA credentials key?
You can visit https://apps.bea.gov/API/signup/index.cfm in a browser to apply for a BEA credentials key.
Where can I get the BLS credentials key?
You can visit https://data.bls.gov/registrationEngine/ in a browser to apply for a BLS credentials key.
What if no available variables for my search string?
A warning message will pop up to indicate user that there is no search result for the string.
Which data sources allow me to select multiple series/tables at a time from search results?
Currently, only FRED allows multiple selections from search results.
How can I choose the correct combination of parameter sets in Step 3: Data Filtering?
You should choose the parameter sets based on your understanding of the data source, as well as the specific table you choose. We provide drop-down lists for each parameter, but we can’t guarantee random combinations of parameters will work especially for cases where you are supposed to select multiple parameters. A message saying “No parameters required for the selected data source” will pop up to indicate users that there is no available parameters for the selected data source.
How can I skip optional steps?
Step 5: Data sampling and Step 6: Advanced Settings are two optional steps in this form. After you complete Step 4: Select Columns, “Run” and “Save” buttons will be enabled which means you have already completed all the required steps and click either of them will directly lead you to the result.
When should I use Step 5: Data Sampling?
Data sampling is an optional function in this form. It will be good to use this function when you know the raw data is either very large or not enough for your purpose. You can either shrink the size of the data or expand it by choosing different settings in data sampling.