{"text_format":"1","workbook":"xlsx","n_sheets":5,"sheets":["Convert","MAPPINGS","Units","Positions","Instructions"],"not_rendered":{"charts":0,"images":0,"macros":1,"external_links":0,"defined_names":3,"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":"Convert","index":0,"n_rows":18,"n_cols":1,"rows_kept":18,"cut":false,"hidden":false,"merged":[]}
["Note: Macros must be enabled to use this converter.  To enable macros,"]
["click \"Enable Content\" button on the Security Warning that appears above."]
[]
[]
["This workbook contains code that calendarizes reading-based energy \nconsumption/activity data into monthly amounts."]
[]
["Important:"]
["This tool converts only the first tab of the source spreadsheet."]
[]
[]
[]
[]
[]
["Version 1.5"]
["Release Date: Jan. 28, 2020"]
[]
["This may be distributed to clients as an \"as is\" tool"]
["for calendarizing consumption/activity data in preparation for loading into CGRT."]

{"sheet":"MAPPINGS","index":1,"n_rows":1,"n_cols":7,"rows_kept":1,"cut":false,"hidden":false,"merged":[]}
["REPORTING_CODE","DESCRIPTION_1","DESCRIPTION_2","RC_TYPE","MAP_TO_RU_ID","SOFI_SITE_ID","SOFI_SITE_NAME"]

{"sheet":"Units","index":2,"n_rows":15,"n_cols":3,"rows_kept":15,"cut":false,"hidden":false,"merged":[]}
["SMARTTOOL_UNIT_NAME","SOFI_UNIT_NAME","SOFI_UNIT_ID"]
["KWH","kWh",50]
["kWh","kWh",50]
["kwh","kWh",50]
["GJ","GJ",49]
["gj","GJ",49]
["L","l",123]
["l","l",123]
["KG","kg",89]
["kg","kg",89]
["Kg","kg",89]
["LBS","lbs",91]
["Lbs","lbs",91]
["Pak","unit",190]
["Pkg","unit",190]

{"sheet":"Positions","index":3,"n_rows":126,"n_cols":7,"rows_kept":126,"cut":false,"hidden":false,"merged":[]}
["GCCT_ID","SOURCE","SMARTTOOL_DISPLAY","SMARTTOOL_METHOD_OF_CONSUMP","SMARTTOOL_CONSUMABLE","SOFI_POSITION_ID","SOFI_POSITION_NAME"]
[646,"Buildings","District Energy - Tier 1","District Energy","District Energy - T1",21,"District Energy (T1)"]
[655,"Buildings","District Energy - Tier 10","District Energy","District Energy - T10",30,"District Energy (T10)"]
[647,"Buildings","District Energy - Tier 2","District Energy","District Energy - T2",22,"District Energy (T2)"]
[648,"Buildings","District Energy - Tier 3","District Energy","District Energy - T3",23,"District Energy (T3)"]
[649,"Buildings","District Energy - Tier 4","District Energy","District Energy - T4",24,"District Energy (T4)"]
[650,"Buildings","District Energy - Tier 5","District Energy","District Energy - T5",25,"District Energy (T5)"]
[651,"Buildings","District Energy - Tier 6","District Energy","District Energy - T6",26,"District Energy (T6)"]
[652,"Buildings","District Energy - Tier 7","District Energy","District Energy - T7",27,"District Energy (T7)"]
[653,"Buildings","District Energy - Tier 8","District Energy","District Energy - T8",28,"District Energy (T8)"]
[654,"Buildings","District Energy - Tier 9","District Energy","District Energy - T9",29,"District Energy (T9)"]
[202,"Buildings","Steam","District Energy","Steam",28,"District Energy (T8)"]
[628,"Buildings","Steam - Med (85%)","District Energy","Steam - Med",26,"District Energy (T6)"]
[482,"Buildings","B100","Heating","B100",4,"B100"]
[529,"Buildings","B20","Heating","B20",5,"B20"]
[528,"Buildings","B5","Heating","B5",6,"B5"]
[283,"Buildings","Marine Diesel","Heating","Marine Diesel",8,"Diesel (Marine)"]
[206,"Buildings","Diesel","Heating","Diesel",7,"Diesel (Standard B4)"]
[530,"Buildings","BioHeat 5%","Heating","BioHeat 5%",12,"Fuel Oil (B5)"]
[210,"Buildings","Light Fuel Oil","Heating","Light Fuel Oil",11,"Fuel Oil (Light)"]
[213,"Buildings","Gasoline","Heating","Gasoline",9,"Gasoline (Standard E5)"]
[623,"Buildings","Renewable Natural Gas","Heating","Bio Gas",13,"Natural Gas (R100)"]
[205,"Buildings","Natural Gas","Heating","Natural Gas",15,"Natural Gas (Standard)"]
[207,"Buildings","Propane Gas","Heating","Propane",16,"Propane"]
[303,"Buildings","Wood Fuel - Industrial","Heating","Wood Fuel-Industrial",17,"Wood Fuel (Industrial)"]
[302,"Buildings","Wood Fuel - Residential","Heating","Wood Fuel-Residential",18,"Wood Fuel (Residential)"]
[712,"Buildings","Electricity","Lights & Plugs","Electricity",787,"Electricity - BC - Integrated Grid - BC Hydro"]
[718,"Buildings","Fortis Electricity","Lights & Plugs","Electricity-Fortis",784,"Electricity -  BC - Integrated Grid - Fortis"]
[715,"Buildings","Nelson Electricity","Lights & Plugs","Electricity-Nelson",845,"Electricity -  BC - Integrated Grid - Nelson Hydro"]
[713,"Buildings","Penticton Electricity","Lights & Plugs","Electricity-Penticton",846,"Electricity -  BC - Integrated Grid - Penticton Electrical"]
[714,"Buildings","Alberta Electricity","Lights & Plugs","Electricity-Alberta",32,"Electricity - Alberta"]
[710,"Buildings","Alberta Bullfrog Electricity","Lights & Plugs","Electricity-Alberta Bullfrog",868,"Electricity - Bullfrog"]
[716,"Buildings","China Electricity","Lights & Plugs","Electricity-China",872,"Electricity - China"]
[717,"Buildings","India Electricity","Lights & Plugs","Electricity-India",874,"Electricity - India"]
[719,"Buildings","Japan Electricity","Lights & Plugs","Electricity-Japan",875,"Electricity - Japan"]
[768,"Buildings","Nova Scotia Electricity","Lights & Plugs","Electricity-Nova Scotia",869,"Electricity - Nova Scotia"]
[720,"Buildings","Ontario Electricity","Lights & Plugs","Electricity-Ontario",870,"Electricity - Ontario"]
[772,"Buildings","Quebec Electricity","Lights & Plugs","Electricity-Quebec",871,"Electricity - Quebec"]
[711,"Buildings","UK Electricity","Lights & Plugs","Electricity-UK",873,"Electricity - UK"]
[422,"Fleet","Aviation Gasoline","Aviation","Aviation-Aviation Gasoline",37,"Aviation - Gasoline"]
[423,"Fleet","Aviation Turbo Fuel","Aviation","Aviation-Aviation Turbo Fuel",38,"Aviation - Turbo Fuel"]
[332,"Fleet","B100","Heavy Duty Truck","Heavy Duty Truck-B100",40,"Heavy Duty Truck - Diesel (B100)"]
[535,"Fleet","B20","Heavy Duty Truck","Heavy Duty Truck-B20",41,"Heavy Duty Truck - Diesel (B20)"]
[534,"Fleet","B5","Heavy Duty Truck","Heavy Duty Truck-B5",42,"Heavy Duty Truck - Diesel (B5)"]
[333,"Fleet","Diesel","Heavy Duty Truck","Heavy Duty Truck-Diesel",43,"Heavy Duty Truck - Diesel (Standard)"]
[334,"Fleet","E10","Heavy Duty Truck","Heavy Duty Truck-E10",44,"Heavy Duty Truck - Gasoline (E10)"]
[538,"Fleet","E100","Heavy Duty Truck","Heavy Duty Truck-E100",45,"Heavy Duty Truck - Gasoline (E100)"]
[537,"Fleet","E15","Heavy Duty Truck","Heavy Duty Truck-E15",46,"Heavy Duty Truck - Gasoline (E15)"]
[536,"Fleet","E5","Heavy Duty Truck","Heavy Duty Truck-E5",48,"Heavy Duty Truck - Gasoline (Standard)"]
[331,"Fleet","Gasoline","Heavy Duty Truck","Heavy Duty Truck-Gasoline",48,"Heavy Duty Truck - Gasoline (Standard)"]
[350,"Fleet","Natural Gas","Heavy Duty Truck","Heavy Duty Truck-Natural Gas",49,"Heavy Duty Truck - Natural Gas"]
[349,"Fleet","Propane","Heavy Duty Truck","Heavy Duty Truck-Propane",50,"Heavy Duty Truck - Propane"]
[335,"Fleet","B100","Light Duty Truck","Light Duty Truck-B100",52,"Light Duty Truck - Diesel (B100)"]
[540,"Fleet","B20","Light Duty Truck","Light Duty Truck-B20",53,"Light Duty Truck - Diesel (B20)"]
[539,"Fleet","B5","Light Duty Truck","Light Duty Truck-B5",54,"Light Duty Truck - Diesel (B5)"]
[336,"Fleet","Diesel","Light Duty Truck","Light Duty Truck-Diesel",55,"Light Duty Truck - Diesel (Standard)"]
[337,"Fleet","E10","Light Duty Truck","Light Duty Truck-E10",56,"Light Duty Truck - Gasoline (E10)"]
[543,"Fleet","E100","Light Duty Truck","Light Duty Truck-E100",57,"Light Duty Truck - Gasoline (E100)"]
[542,"Fleet","E15","Light Duty Truck","Light Duty Truck-E15",58,"Light Duty Truck - Gasoline (E15)"]
[338,"Fleet","Gasoline","Light Duty Truck","Light Duty Truck-Gasoline",60,"Light Duty Truck - Gasoline (Standard)"]
[541,"Fleet","E5","Light Duty Truck","Light Duty Truck-E5",60,"Light Duty Truck - Gasoline (Standard)"]
[339,"Fleet","Natural Gas","Light Duty Truck","Light Duty Truck-Natural Gas",61,"Light Duty Truck - Natural Gas"]
[340,"Fleet","Propane","Light Duty Truck","Light Duty Truck-Propane",62,"Light Duty Truck - Propane"]
[322,"Fleet","B100","Light Duty Vehicle","Light Duty Vehicle-B100",64,"Light Duty Vehicle - Diesel (B100)"]
[545,"Fleet","B20","Light Duty Vehicle","Light Duty Vehicle-B20",65,"Light Duty Vehicle - Diesel (B20)"]
[544,"Fleet","B5","Light Duty Vehicle","Light Duty Vehicle-B5",66,"Light Duty Vehicle - Diesel (B5)"]
[324,"Fleet","Diesel","Light Duty Vehicle","Light Duty Vehicle-Diesel",67,"Light Duty Vehicle - Diesel (Standard)"]
[326,"Fleet","E10","Light Duty Vehicle","Light Duty Vehicle-E10",68,"Light Duty Vehicle - Gasoline (E10)"]
[548,"Fleet","E100","Light Duty Vehicle","Light Duty Vehicle-E100",69,"Light Duty Vehicle - Gasoline (E100)"]
[547,"Fleet","E15","Light Duty Vehicle","Light Duty Vehicle-E15",70,"Light Duty Vehicle - Gasoline (E15)"]
[546,"Fleet","E5","Light Duty Vehicle","Light Duty Vehicle-E5",72,"Light Duty Vehicle - Gasoline (Standard)"]
[328,"Fleet","Gasoline","Light Duty Vehicle","Light Duty Vehicle-Gasoline",72,"Light Duty Vehicle - Gasoline (Standard)"]
[329,"Fleet","Natural Gas","Light Duty Vehicle","Light Duty Vehicle-Natural Gas",73,"Light Duty Vehicle - Natural Gas"]
[347,"Fleet","Propane","Light Duty Vehicle","Light Duty Vehicle-Propane",74,"Light Duty Vehicle - Propane"]
[341,"Fleet","Marine Diesel","Marine","Marine-Marine Diesel",76,"Marine - Diesel (Standard)"]
[342,"Fleet","Marine Gasoline","Marine","Marine-Marine Gasoline",77,"Marine - Gasoline (Standard)"]
[343,"Fleet","Gasoline","Motorcycle","Motorcycle-Gasoline",79,"Motorcycle - Gasoline (Standard)"]
[344,"Fleet","B100","Off Road Vehicle","Off Road Vehicle-B100",81,"Off Road Vehicle - Diesel (B100)"]
[550,"Fleet","B20","Off Road Vehicle","Off Road Vehicle-B20",82,"Off Road Vehicle - Diesel (B20)"]
[549,"Fleet","B5","Off Road Vehicle","Off Road Vehicle-B5",83,"Off Road Vehicle - Diesel (B5)"]
[345,"Fleet","Diesel","Off Road Vehicle","Off Road Vehicle-Diesel",84,"Off Road Vehicle - Diesel (Standard)"]
[402,"Fleet","E10","Off Road Vehicle","Off Road Vehicle-E10",85,"Off Road Vehicle - Gasoline (E10)"]
[553,"Fleet","E100","Off Road Vehicle","Off Road Vehicle-E100",86,"Off Road Vehicle - Gasoline (E100)"]
[552,"Fleet","E15","Off Road Vehicle","Off Road Vehicle-E15",87,"Off Road Vehicle - Gasoline (E15)"]
[551,"Fleet","E5","Off Road Vehicle","Off Road Vehicle-E5",89,"Off Road Vehicle - Gasoline (Standard)"]
[346,"Fleet","Gasoline","Off Road Vehicle","Off Road Vehicle-Gasoline",89,"Off Road Vehicle - Gasoline (Standard)"]
[351,"Fleet","Natural Gas","Off Road Vehicle","Off Road Vehicle-Natural Gas",90,"Off Road Vehicle - Natural Gas"]
[352,"Fleet","Propane","Off Road Vehicle","Off Road Vehicle-Propane",91,"Off Road Vehicle - Propane"]
[355,"Procurement",0,"11 x 17 Paper","11x17-0",95,"11 x 17 - 0% Recycled"]
[358,"Procurement",0.1,"11 x 17 Paper","11x17-10",96,"11 x 17 - 10% Recycled"]
[385,"Procurement",1,"11 x 17 Paper","11x17-100",105,"11 x 17 - 100% Recycled"]
[361,"Procurement",0.2,"11 x 17 Paper","11x17-20",97,"11 x 17 - 20% Recycled"]
[364,"Procurement",0.3,"11 x 17 Paper","11x17-30",98,"11 x 17 - 30% Recycled"]
[367,"Procurement",0.4,"11 x 17 Paper","11x17-40",99,"11 x 17 - 40% Recycled"]
[370,"Procurement",0.5,"11 x 17 Paper","11x17-50",100,"11 x 17 - 50% Recycled"]
[373,"Procurement",0.6,"11 x 17 Paper","11x17-60",101,"11 x 17 - 60% Recycled"]
[376,"Procurement",0.7,"11 x 17 Paper","11x17-70",102,"11 x 17 - 70% Recycled"]
[379,"Procurement",0.8,"11 x 17 Paper","11x17-80",103,"11 x 17 - 80% Recycled"]
[382,"Procurement",0.9,"11 x 17 Paper","11x17-90",104,"11 x 17 - 90% Recycled"]
[353,"Procurement",0,"8.5 x 11 Paper","8.5x11-0",107,"8.5 x 11 - 0% Recycled"]
[356,"Procurement",0.1,"8.5 x 11 Paper","8.5x11-10",108,"8.5 x 11 - 10% Recycled"]
[383,"Procurement",1,"8.5 x 11 Paper","8.5x11-100",117,"8.5 x 11 - 100% Recycled"]
[359,"Procurement",0.2,"8.5 x 11 Paper","8.5x11-20",109,"8.5 x 11 - 20% Recycled"]
[362,"Procurement",0.3,"8.5 x 11 Paper","8.5x11-30",110,"8.5 x 11 - 30% Recycled"]
[365,"Procurement",0.4,"8.5 x 11 Paper","8.5x11-40",111,"8.5 x 11 - 40% Recycled"]
[368,"Procurement",0.5,"8.5 x 11 Paper","8.5x11-50",112,"8.5 x 11 - 50% Recycled"]
[371,"Procurement",0.6,"8.5 x 11 Paper","8.5x11-60",113,"8.5 x 11 - 60% Recycled"]
[374,"Procurement",0.7,"8.5 x 11 Paper","8.5x11-70",114,"8.5 x 11 - 70% Recycled"]
[377,"Procurement",0.8,"8.5 x 11 Paper","8.5x11-80",115,"8.5 x 11 - 80% Recycled"]
[380,"Procurement",0.9,"8.5 x 11 Paper","8.5x11-90",116,"8.5 x 11 - 90% Recycled"]
[354,"Procurement",0,"8.5 x 14 Paper","8.5x14-0",119,"8.5 x 14 - 0% Recycled"]
[357,"Procurement",0.1,"8.5 x 14 Paper","8.5x14-10",120,"8.5 x 14 - 10% Recycled"]
[384,"Procurement",1,"8.5 x 14 Paper","8.5x14-100",129,"8.5 x 14 - 100% Recycled"]
[360,"Procurement",0.2,"8.5 x 14 Paper","8.5x14-20",121,"8.5 x 14 - 20% Recycled"]
[363,"Procurement",0.3,"8.5 x 14 Paper","8.5x14-30",122,"8.5 x 14 - 30% Recycled"]
[366,"Procurement",0.4,"8.5 x 14 Paper","8.5x14-40",123,"8.5 x 14 - 40% Recycled"]
[369,"Procurement",0.5,"8.5 x 14 Paper","8.5x14-50",124,"8.5 x 14 - 50% Recycled"]
[372,"Procurement",0.6,"8.5 x 14 Paper","8.5x14-60",125,"8.5 x 14 - 60% Recycled"]
[375,"Procurement",0.7,"8.5 x 14 Paper","8.5x14-70",126,"8.5 x 14 - 70% Recycled"]
[378,"Procurement",0.8,"8.5 x 14 Paper","8.5x14-80",127,"8.5 x 14 - 80% Recycled"]
[381,"Procurement",0.9,"8.5 x 14 Paper","8.5x14-90",128,"8.5 x 14 - 90% Recycled"]
[745,"Fugitive","HFC R-134","Refrigerants","HFC R-134",132,"HFC R-134"]
[645,"Fugitive","HFC R-134a","Refrigerants","HFC R-134a",133,"HFC R-134a"]
[644,"Fugitive","HFC R-404a","Refrigerants","HFC R-404a",134,"HFC R-404a"]
[643,"Fugitive","HFC R-407c","Refrigerants","HFC R-407c",135,"HFC R-407c"]
[642,"Fugitive","HFC R-410a","Refrigerants","HFC R-410a",136,"HFC R-410a"]

{"sheet":"Instructions","index":4,"n_rows":32,"n_cols":4,"rows_kept":32,"cut":false,"hidden":false,"merged":[]}
["Instructions"]
[]
["This tool calendarizes consumption data (i.e., convert data with Reading Start and Reading End dates that span more than one month into single monthly values). "]
["Is is compatable with both SMARTLoad template format and *non-SMARTLoad format. "]
["Typical use is for calendarizing non-Portfolio Manager building energy data (for example, Fortis BC Electricity)."]
["It will also convert Fleet or Supplies (paper/fugative) consumption data in Purchase Date format to a monthly Term. See row 17 below."]
[]
["Steps"]
[1,"Enable macros and press \"Calendarize Data\" button."]
[2,"Select the file containing the energy data with reading start and end dates. See note below for converting "]
[3,"Select the calendar year that you wish to be output."]
["4.a","If the selected file is a SMARTLoad Template, then the Reporting Code, Building ID, and Street Address fields will be included in the output file."]
["4.b","*Note, If the selected file is not a SMARTLoad Template, in addition to Quantity, Reading Start and End date fields, the following fields must be included:"]
[null,null,"> Building Fuel (e.g., Electricity, Natural Gas, Propane Gas, Etc.) - Refer to \"Position\" Tab, Column C for complete list "]
[null,null,"> Unit of Measure (e.g., kWh, GJ, l, kg, Etc.) - Refer to \"Units\" Tab, Column A for complete list"]
[5,"When prompted for each, select the column for the Fuel, Quantity, Unit of Measure, Reading Start and Reading End dates.  Select a single column for each. "]
[null,"*Note, for Fleet/Supplies you must select the Purchase_Date column for both Reading Start and Reading End dates."]
[6,"Save the output file containing the calendarized data for loading."]
[7,"You MUST populate the Site IDs for each row of data BEFORE bulk loading (steps below). You have two options: "]
["7.a","Within the output file, you may populate the Site IDs for each row of data. Locate your Site IDs by exporting an Organization Structure Configuration Report from Analytics > Intelligence Centre."]
["7.b","Copy/paste the calendarized Quantity and Term columns for one or more Sites into the relevant tab of a CGR Bulk Load Template Generator"]
[]
["Bulk Loading an Output File into CGRT"]
[1,"Launch CGRT:",null,"https://gov-bc.mythinkstep.com/login"]
[2,"Go to Data Entry / File Importer."]
[3,"Browse for and select the Calendarized Output File or Load File that you saved in Step 7 above. "]
[4,"Click Upload. Next to Available Mappings select the dropdown and select ‘Load Template or Calendarizer File’ from the list. "]
[5,"Click Read. A list of prepopulated fields will display. "]
[6,"Click Test. If no errors occur, click Import. "]
[7,"Navigate to your Job Tray. Click the refresh icon in the lower right corner of the dropdown. When the file turns green, click on the file."]
[8,"A dialogue box will appear indicating that the file imported with no errors. "]
[9,"You may navigate to your building Sites (Data Entry > Data Acquisition) and verify that the data imported into CGRT aligns with your calendarized output file. "]