{"text_format":"1","workbook":"xlsx","n_sheets":3,"sheets":["Readme","GeoRequests","VarRequests"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":0,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Readme","index":0,"n_rows":43,"n_cols":3,"rows_kept":43,"cut":false,"hidden":false,"merged":[]}
["Spreadsheet based on the work of Jan Vink, Cornell University"]
["Demo downloadable at https://pad.human.cornell.edu/tools/API2Excel/index.cfm"]
["Free to use and alter at your own risk"]
["In some cases Excel will block macros from running. Microsoft has a page with some things to try to allow this workbook to run. The first step is of course trusting the source."]
[]
["This workbook let users create a rectangle table with 1 or more geographies as rows and 1 ro more data fields as columns. The data is retrieved using the Census Bureau API."]
["This version is tested for ACS data products and the decennial SF1 and DHC. Some of the other Census Bureau products (like population estimates) contain multiple rows for the same geography and are less suitable for this application."]
[]
["The Census Data API User Guide contains helpful information on defining API queries (https://www.census.gov/data/developers/guidance/api-user-guide.html). The examples within each of the available APIs (https://www.census.gov/data/developers/data-sets.html) are also helpful, especially as an overview of calling specific geographies summary levels."]
["All settings are case sensative!"]
[]
["SETTINGS"]
["The worksheet GeoRequests lists one or more defintions for geographic requests. The examples given by the available API data products list how to define different geographies."]
[null,"BaseURL","All (or most) available Census Bureau's APIs allow for retrieving NAME and a GEO_ID for all geographies in the dataset that are within the requested set. But the number of geographies is not always the same, as geographies are not static and some datasets, like the 1 year ACS do no return data for all geographies. Since 2023, the Census Bureau also has a dedicated geography API (https://api.census.gov/data/2023/geoinfo)"]
[null,"GeoRequestID","Short identifier that describes the collection of geographies being requested, for example counties in a region, tracts in county"]
[null,"Geography requests","The geography request parameters. Often this is a combination of a \"for\" and an \"in\" parameter. The \"for\" defines the geographic summary level and identifiers that specify  FIPS codes (just the FIPS part for this summary level, include leading zeroes) or an \"*\" if all are requested. The \"in\" parameter(s) limits the area where the \"for\" area can be found. For example: for=county:001,003&in=state:36 returns data for counties with FIPS code 001 and 002 within the state with FIPS code 36 (New York). Instead of \"for\" and \"in\" parameters, it is also possible to use \"ucgid\" parameters to directly define one or more geographies."]
[]
[]
[]
["The worksheet VarRequests lets the user define the datafields in the final result. Each data field adds a single data field, but can be derived from multiple variables retrieved through the API. The derivation calculation needs to be defined"]
[null,"IndexID","Short identifier for the data field. This ID will be used to name a worksheet, and also as a column header in the summarized results"]
[null,"Description","Description of this indicator"]
[null,"Year","The part of the API query that identifies the year"]
[null,"Survey","The part of the API query that identifies the survey (e.g. acs or dec)."]
[null,"Product","The part of the API query that identifies the product within the survey (e.g. subject or profile). Detailed ACS tables do not have a product identifier"]
[null,"Variables","A comma separated list of variable names. Do not use spaces in between names. The variable names can be found in the Census API pages, table shells or in .zip downloads from data.census.gov"]
[null,"Calculation","Often the indicator of interests combines multple cells out of a one or more tables, for example to collapse age groups, combine males and females, or to calculate share and ratios.\nThe formula in this field defines how the indicator is calculated. It is written in a way Power Query handles formula (in Power Query M language). It is important that column headers (the variable names) are surrounded with square brackets."]
[null,"IncludeTotal","If this field is not empty, the macro will add total rows for each geography request, and calculate the indicator based on those totals. For shares for example, the total row will create totals for the numerator and the denominator and calculate the shares based on those totals. This is implemented to create indicators for a collection of geographies, like regions. Totals will not be included in the final summary table, if there is only a single geography specified in the Geography Query"]
[]
["START MACRO"]
["On the VarRequests worksheet is a button that starts the macro \"collectData\". This macro can also be started through the Developer Tab if that is enabled."]
[]
["RESULT"]
["For each of the queries defined a separate worksheet will be created, containing the full API query results, the result of the index calculation and the group totals if requested."]
["The Geo worksheet contains a list of geographic IDs and names. The geographic IDs will be used to link the calculated indices into a single table Result."]
["Note that the worksheet Result contains lookup formula and not the values. This is done because it is not guaranteed that each query results in the same set of geographies."]
[]
["DEBUGGING"]
["Different things can go wrong in using this worksheet and no attempt is made to catch errors and make helpful hints as to where the error might originate."]
["The settings are case sensative, so that might be a reason and so are unexpected spaces."]
[]
["Each query result in a set of instructions for Power Query. This set of instructions can be found by clicking \"Data\" in the main menu and click Queries & Connections."]
["A list of queries should open and if one or more contain errors, right click on that query and click \"Edit\". The opens the Power Query Editor and one can go step by step where the error is introduced."]

{"sheet":"GeoRequests","index":1,"n_rows":5,"n_cols":3,"rows_kept":5,"cut":false,"hidden":false,"merged":[]}
["BaseURL","GeoRequestID","Geography requests"]
["https://api.census.gov/data/2023/acs/acs5","State","for=state:36"]
[null,"CapitalDistrict","for=county:001,021,039,083,091,093,113,115&in=state:36"]
[null,"TST_Boces","for=school%20district%20(unified):22140,06420,09330,13020,15570,16710,20790,28980&in=state:36"]
[null,"UCGIDExample","ucgid=0400000US06,0500000US06059,1600000US0602000"]

{"sheet":"VarRequests","index":2,"n_rows":10,"n_cols":8,"rows_kept":10,"cut":false,"hidden":false,"merged":[]}
["Columns",null,"API elements (prepended by https://api.census.gov/data)"]
["IndexID","Description","Year","Survey","Product","Variables","Calculation","IncludeTotal"]
["TotPop","Total population",2023,"acs","acs5","B01001_001E","[B01001_001E]","x"]
["PercNotFluentEngl","Number of people that don't speak Englis \"well\"",2023,"acs","acs5/profile","DP02_0115PE","[DP02_0115PE]"]
["PercForeign23","Percent foreign born",2023,"acs","acs5/subject","S0501_C01_001E,S0501_C03_001E","100*([S0501_C03_001E]/[S0501_C01_001E])"]
["PercForeign22","Percent foreign born",2022,"acs","acs5/subject","S0501_C01_001E,S0501_C03_001E","100*([S0501_C03_001E]/[S0501_C01_001E])"]
["PovWhUnder6","Poverty rate for White Alone children under 6",2023,"acs","acs1","B17020_003E,B17020_011E","100*[B17020_003E]/([B17020_003E]+[B17020_011E])","x"]
["OccupancyRate20","Occupancy rate (2020)",2020,"dec","dhc","H3_001N,H3_002N,H3_003N","100*[H3_002N]/[H3_001N]","x"]
["OccupancyRate10","Occupancy rate (2010)",2010,"dec","sf1","H003001,H003002,H003003","100*[H003002]/[H003001]","x"]
["LandArea","Land Area (sq miles)",2023,"geoinfo",null,"AREALAND,INTPTLAT,INTPTLON","[AREALAND]/2589988.110336","x"]