{ Analytica Model Time_of_use_pricing, encoding="UTF-8" }
SoftwareVersion 5.5.0


{ System Variables with non-default values: }
SampleSize := 1000
TypeChecking := 1
Checking := 1
SaveOptions := 2
SaveValues := 0

{!-50299|DiagramColor Model: 65535,65535,65535}
{!-50299|DiagramColor Module: 65535,65535,65535}
{!-50299|DiagramColor LinkModule: 65535,65535,65535}
{!-50299|DiagramColor Library: 65535,65535,65535}
{!-50299|DiagramColor LinkLibrary: 65535,65535,65535}
{!-50299|DiagramColor Form: 65535,65535,65535}
NodeInfo FormNode: 1,0,0,,0,0,,,,0,,,0
{!-50299|NodeColor Text: 62258,62258,62258}



Model Time_of_use_pricing
Title: Time of use pricing
Description: Calculates pricing according to a time-of-use rate tariffs.  ~
~
Imports some real usage data, uses that to create a forecast of usage by hour across a couple years time, and then prices the hourly usage according to California <a href="https://www.pge.com/tariffs/assets/pdf/tariffbook/ELEC_SCHEDS_E-TOU-C.pdf">TOU-C</a> and <a href="https://www.pge.com/tariffs/assets/pdf/tariffbook/ELEC_SCHEDS_E-TOU-D.pdf">TOU-D</a> tariffs (for examples). This example just covers the time-of-use variability part of the tariff, and not other components.~
~
Developed during an Analytica user group webinar, 30-Sep-2020.
Author: Lonnie Chrisman, Ph.D.~
Lumina Decision Systems
Date: Wed, Sep 30, 2020 10:56 AM
DiagState: 2,70,20,1080,383,17
WindState: 2,38,400,720,350
FontStyle: Arial,15
FileInfo: 0,Model Time_of_use_pricing,2,2,0,0,D:\Documents\Analytica\Example Models\Time of Usage\Time of use pricing.ana

Module Historical_usage
Title: Historical usage
Description: This module imports historical usage data from a spreadsheet that came from ~
<a href="https://www9.nationalgridus.com/energysupply/load_estimate.asp">NationalGridUS.com</a>
Author: Lonnie Chrisman, Ph.D.~
Lumina Decision Systems
Date: Wed, Sep 30, 2020 1:30 PM
NodeLocation: 80,40,1
NodeSize: 48,24
DiagState: 2,291,93,624,345,17,,

Variable ave_cust_usage
Title: ave cust usage
Units: kWh/h
Description: The electrical average usage per customer (by usage_date and hour) in the Massachusetts region covered by the MECO spreadsheet data that is imported. ~
~
The source spreadsheet data was in a fact-table format, with no headers.~
~
This defines the global result indexes as well as the transformed data (transformed from a fact-table form into a multidimensional array form).  The indexes use the ComputedBy trick to enable them to be defined at the same time as this calculation, saving the need for an intermediate global variable (in this case sheet).
Definition: Local sheet:= SpreadsheetRange(wb,'MECO ALL!');~
Rate_code := Unique(sheet[.Column='A']);~
Usage_date := Unique(sheet[.Column='B']);~
MdTable(sheet,sheet.Row,sheet.Column,[Rate_code,Usage_date],~
			valueColumn:Array(Hour,'C'..'Z') )
NodeLocation: 232,128,1
NodeSize: 48,24
WindState: 2,373,304,720,350
ValueState: 2,188,84,771,468,,MIDM
Aliases: Alias Al1973000061
ReformVal: [Hour,Usage_date]

Index Hour
Title: Hour
Description: The hour of the day.  ~
~
Although Analytica indexes usually start at 1 by convention, in the case of Hour it is better to use 0..23 instead of 1..24 so that Hour=8 corresponds to the hour starting at 8am.  It matches the standard "military time" convention for hour numbering.
Definition: 0..23
NodeLocation: 96,312,1
NodeSize: 48,24
WindState: 2,403,391,720,350
Aliases: Alias Al1939445629
{!40300|Att_SlicerPopupSize: 227,364}

Index Usage_date
Title: Usage date
Description: Index for the date of the historic usage data.~
~
In many cases, especially when data is being imported, the most convenient place to define the values for a globar index is in the variable Definition that is processing the incoming data. Index values may depend on the data, creating a chicken-and-egg problem. ComputedBy( ) gives you a solution to this, which maintains referential transparency since you still know where it is being computed.
Definition: ComputedBy(ave_cust_usage)
NodeLocation: 96,192,1
NodeSize: 48,24
WindState: 2,448,398,720,350
ValueState: 2,244,250,416,303,0,MIDM
Aliases: Alias Al228169597

Constant Rate_filename
Title: Rate filename
Description: Demonstrates a trick for remembering which file was selected in the file explorer during the previous SpreadsheetOpen call. If the file exists, it reads it without asking. If the file isn't there, SpreadsheetOpen puts up a file explorer so you can find the spreadsheet.  Then wb assignes to this variable, which remembers the default value for next time.
Definition: ComputedBy(wb,'MECOLS0620.xlsx')
NodeLocation: 96,40,1
NodeSize: 48,24
WindState: 2,377,409,720,350

Variable wb
Title: wb
Description: The excel workbook object. Notice that this saves the filename so it doesn't have to ask later.  See the Description for Rate_filename.~
~
wb->Name returns the filename without the full path.  Use this when you expect the Excel file to be in the same folder as your model. Then when you share both with someone else, they won't have to find the excel file the first time.~
~
wb->FullName returns the full file path.  Use this when your Excel file might be in a different folder.  The path saved might be specific to your computer, so when you share your version of the model with a colleague, they may need to find it in the file explorer dialog.
Definition: Local wb:=SpreadsheetOpen(Rate_filename);~
Rate_filename := wb->Name;~
wb
NodeLocation: 96,128,1
NodeSize: 48,24
WindState: 2,349,411,720,350
ValueState: 2,515,89,416,303,,MIDM

Index Rate_code
Title: Rate code
Description: A rate code category that happens to be present in the incoming spreadsheet data.
Definition: ComputedBy(ave_cust_usage)
NodeLocation: 96,256,1
NodeSize: 48,24
WindState: 2,432,280,720,350

Variable cached
Title: cached
Description: This would hold a cached version of the spreadsheet, once the button is pressed.
NodeLocation: 232,208,1
NodeSize: 48,24

Button Import_into_model
Title: Import into model
Description: In this model, we are not caching the data in the model. However, this button demonstrates an alternative way of structuring your import, which caches the data with your model, thus breaking the link to the spreadsheet, but allowing you to import, or re-import, the data with the press of a button.~
~
One advantage of the button approach is you can run the model without having the spreadsheet file.~
~
One disadvantage is that the data is stored with the file, thus your model file is larger, and you might be using older data without realizing it (if you haven't pushed the import button for a while).
NodeLocation: 232,276,1
NodeSize: 48,28
WindState: 2,424,43,720,350
OnClick: MsgBox("This button is here to demonstrate how you could cache data is the model instead of keeping a live import.~
~
However, once you press this, you will replace the ComputedBy definitions for the indexes, so I recommend not proceeding. Press Cancel to abort.", buttons:1, title:"Alert!!");~
~
Local sheet:= SpreadsheetRange(wb,'MECO ALL!');~
Rate_code := Unique(sheet[.Column='A']);~
Usage_date := Unique(sheet[.Column='B']);~
cached := ~
	MdTable(sheet,sheet.Row,sheet.Column,[Rate_code,Usage_date],~
				valueColumn:Array(Hour,'C'..'Z') )

Close Historical_usage

Module Projected_usage
Title: Projected usage
Description: Maps historical past usage data to future usage estimates.~
~
A key challenge here is using a representative past date for the future data -- e.g., landing on the same day of the week, at the same time of year, etc.~
~
One thing we didn't do here was to add in factors that will be different in the future compared to the past.  For example, more solar panels.  These could be estimate adjustments to past data.
Author: Lonnie Chrisman, Ph.D.~
Lumina Decision Systems
Date: Wed, Sep 30, 2020 1:30 PM
NodeLocation: 80,120,1
NodeSize: 48,24
DiagState: 2,348,60,624,345,17

Variable forecasted_usage
Title: forecasted usage
Units: kWh/h
Description: The projected future average electrical usage by residential customer by future date and hour.~
~
There were a few 0s in the data (possible due to day light savings time). We use the preceding hour's data to replace the 0s.
Definition: Local x := ave_cust_usage[ Rate_code='R-1', Usage_date = Usage_date_for_forec ];~
if x=0 then x[Hour=Hour-1] else x
NodeLocation: 256,112,1
NodeSize: 48,24
ValueState: 2,374,100,635,470,1,MIDM
Aliases: Alias Al2107217789
ReformVal: [Forecast_date,Hour]
Att_ResultSliceState: [Rate_code,4,Hour,1,Forecast_date,1]

Index Forecast_date
Title: Forecast date
Description: The date index used for forecasted usage data.
Definition: 1-Sep-2020 .. 31-Aug-2022
NodeLocation: 256,48,1
NodeSize: 48,24
WindState: 2,401,349,720,350
Aliases: Alias Al1038325629
NumberFormat: 2,D,4,2,0,0,4,0,$,0,"d-MMM-yyyy www",0,,,0,0,15

Alias Al1973000061
Title: ave cust usage
Definition: 1
NodeLocation: 96,112,1
NodeSize: 48,24
Original: ave_cust_usage

Alias Al228169597
Title: Usage date
Definition: 1
NodeLocation: 96,48,1
NodeSize: 48,24
Original: Usage_date

Alias Al1939445629
Title: Hour
Definition: 1
NodeLocation: 368,48,1
NodeSize: 48,24
Original: Hour

Variable Usage_date_for_forec
Title: Usage date for forecast
Description: When mapping historic usage to future usage, this determines which historic date will be used for the forecast date.   It isn't enough to just use the same date in the previous year, because the day of the week matters for the tariff pricing. So this finds the most recent date in Usage_date that lands on the same day of the week as forecast date and is as close as possible to the same date in the year.  So it will always use usage date from a Thursday when mapping historic data to Thursday.~
~
Note: Another interesting, but different, approach would have been to project the past data using a linear regression.  The array Usage_date[Usage_date=d1 , defVal:null] would give the historic points for the regression.  Left as an exercise for the reader.
Definition: LocalIndex yearsBack := -1..-5;~
Local wd := DatePart(Forecast_date,'w');~
Local d1 := Nearest_date_on_wd( DateAdd( Forecast_date, yearsBack, 'Y'), wd);~
Max(Usage_date[Usage_date=d1 , defVal:null],yearsBack)
NodeLocation: 96,176,1
NodeSize: 56,24
WindState: 2,409,352,720,350
ValueState: 2,221,17,680,447,,MIDM
{!40700|Att_CellFormat: CellSpan(Forecast_date,CellNumberFormat('Suffix',4,0,0,dateFormat:'d-MMM-yyyy www',fullPrecision:0,numbersAsDates:0,datesAsNumbers:0,digits_:4,zeroes_:2),1,730)}

Function Nearest_date_on_wd(date, wdNum)
Title: Nearest date on wd
Description: Returns the nearest date to «date» that falls on a given weekday, where «wdNum» is 1=Sun..7=Sat.
Definition: Local wd0 := DatePart(date,'w');~
Local diff := Mod(wdNum-wd0, 7, pos:true);~
if diff <=3 then date+diff else date+diff-7
NodeLocation: 96,248,1
NodeSize: 56,24
WindState: 2,353,106,720,350

Close Projected_usage

Module Tariff_periods
Title: Tariff periods
Description: Contains calculations that determine which billing category a forecast_date falls into.<a href="https://www.pge.com/tariffs/assets/pdf/tariffbook/ELEC_SCHEDS_E-TOU-C.pdf"></a><a href="https://www.pge.com/tariffs/assets/pdf/tariffbook/ELEC_SCHEDS_E-TOU-D.pdf"></a>
Author: Lonnie
Date: Wed, Sep 30, 2020 1:30 PM
NodeLocation: 208,40,1
NodeSize: 48,24
DiagState: 2,156,121,888,365,17

Index Season
Title: Season
Description: A list of the season names recognized by the TOU-D tariffs (which divides the year into just summer and winter).
Definition: ['Summer','Winter']
NodeLocation: 816,48,1
NodeSize: 48,24

Index Peak_cat
Title: Peak cat
Description: The peak tier categories used in the tariffs.
Definition: ['Peak','Off-Peak']
NodeLocation: 816,104,1
NodeSize: 48,24

Variable Is_peak
Title: Is peak
Description: True when the forecast_date & Hour combination is at a peak time in the selected Tariff.
Definition: DetermTable(Tariff_plan)(16<=Hour<21,17<=Hour<=20 and is_weekday and not  is_holiday)
NodeLocation: 464,112,1
NodeSize: 48,24
DefnState: 2,301,165,768,303,0,DFNM

Variable Is_Summer
Title: Is Summer
Description: True when the Forecast_day is in the summer
Definition: Local y:=DatePart(Forecast_date,'y');~
Summer_start[Year=y] <= Forecast_date <= Summer_End[Year=y]
NodeLocation: 464,216,1
NodeSize: 48,24
ValueState: 2,196,202,416,303,1,MIDM

Variable Is_Holiday
Title: Is Holiday
Description: True if the Forecast_date is a holiday.
Definition: Max(Date_of_holiday[Year=DatePart(Forecast_date,'y')]=Forecast_date,Holiday)
NodeLocation: 336,80,1
NodeSize: 48,24

Variable Hourly_season
Title: Hourly season
Description: The same of the season corresponding the the given Forecast_date.~
~
One technique to note is that it is a good practice in a case like this to set the Domain to the Season index, which will help catch errors in the event you misspell a Season name (including using the wrong capitalization) or use a season name that isn't recognized.
Definition: if Is_Summer then 'Summer' else 'Winter'
NodeLocation: 584,216,1
NodeSize: 48,24
ValueState: 2,212,218,416,303,0,MIDM
Domain: Season
{!40300|DomainExpr: Season}

Variable Hourly_peak_cat
Title: Hourly peak cat
Description: The name of the peak category, corresponding to the season.  ~
~
One technique to note is that it is a good practice in a case like this to set the Domain to the Peak_cat index, which will help catch errors in the event you misspell a peak name (including using the wrong capitalization) or use a season name that isn't recognized.
Definition: if Is_peak then 'Peak' else 'Off-Peak'
NodeLocation: 584,112,1
NodeSize: 48,24
Domain: Peak_cat
{!40300|DomainExpr: Peak_cat}

Variable Is_Weekday
Title: Is Weekday
Description: True if the Forecast_date is a weekday.
Definition: 1<DatePart(Forecast_date,'w')<7
NodeLocation: 336,136,1
NodeSize: 48,24

Constant Summer_start
Title: Summer start
Description: The first day of summer in each year.
Definition: MakeDate(Year,6,1)
NodeLocation: 320,200,1
NodeSize: 48,24

Constant Summer_End
Title: Summer End
Description: The last day of summer.
Definition: MakeDate(Year,9,30)
NodeLocation: 320,272,1
NodeSize: 48,24

Index Year
Title: Year
Description: A list of years spanning both historical usage and forecasted dates.
Definition: Min(DatePart(Usage_date,'y')) .. Max(DatePart(Forecast_date,'y'))
NodeLocation: 320,336,1
NodeSize: 48,24

Index Holiday
Att_PrevIndexValue: ['New Year''s Day','President''s Day','Memorial Day','Independence Day','Labor Day','Veterans Day','Thanksgiving Day','Christmas Day']
Title: Holiday
Description: A list of the holidays that are recognized as such by PG&E.
Definition: ["New Year's Day","President's Day",'Memorial Day','Independence Day','Labor Day','Veterans Day','Thanksgiving Day','Christmas Day']
NodeLocation: 72,64,1
NodeSize: 48,24

Variable Date_of_holiday
Title: Date of holiday
Description: Describes which day each holiday lands on in each given year.~
~
Warning: It looks like PG&E recognize independence day on 5-July when 4-July lands on a sunday, and Veteran's day on 12-Nov when 11-Nov falls on a Sunday. These two tweaks are not currently included in this logic.  Another exercise for the reader.
Definition: Table(Holiday)(MakeDate(Year,1,1),Nth_day_of_month(year,2,'Mon',3),Nth_day_of_month(year,5,'Mon',-1),MakeDate(Year,7,4),Nth_day_of_month(year,9,'Mon',1),MakeDate(Year,11,11),Nth_day_of_month(year,11,'Thu',4),MakeDate(Year,12,25))
NodeLocation: 200,80,1
NodeSize: 48,24
DefnState: 2,196,202,584,303,0,DFNM
ValueState: 2,24,79,833,308,,MIDM
NumberFormat: 2,D,4,2,0,0,4,0,$,0,"d-MMM-yyyy www",0,,,0,0,15

Function Nth_day_of_month(year, month, wd, n : atom)
Title: Nth day of month
Description: Returns the date of the nth weekday, «wd», in the given month.  «wd» is 'Sun', ..., 'Sat'.  Then rest are numbers.  If n<0 then return the (-n)th from last day. Last monday would be Nth_day_of_month(y,m,'Mon',-1).
Definition: LocalIndex day:=1..31;~
Local cand := MakeDate(year,month,day,valueForInvalid:null);~
LocalIndex dayw := Subset(DatePart(cand,'w') = @[Weekday=wd],I:day);~
Local d:= if n>0 then Slice(dayw,n) else Slice(dayw,IndexLength(dayw)+n+1);~
MakeDate(year,month,d)
NodeLocation: 72,144,1
NodeSize: 48,24
WindState: 2,309,38,720,350

Index Weekday
Title: Weekday
Description: List of weekday names. Used to make the calling of Nth_day_of_month more convenient and readable.
Definition: ['Sun','Mon','Tue','Wed','Thu','Fri','Sat']
NodeLocation: 72,216,1
NodeSize: 48,24

Alias Al1038325629
Title: Forecast date
Definition: 1
NodeLocation: 72,280,1
NodeSize: 48,24
Original: Forecast_date

Close Tariff_periods

Module Pricing
Title: Pricing
Description: Calculations of costs of forecasted electricity usage.
Author: Lonnie Chrisman, Ph.D.~
Lumina Decision Systems
Date: Wed, Sep 30, 2020 1:30 PM
NodeLocation: 208,120,1
NodeSize: 48,24
DiagState: 2,267,45,727,457,17

Variable Hourly_price
Title: Hourly price
Units: $
Description: The forecasted cost of the electricity used in each hour.
Definition: Hourly_rate * forecasted_usage
NodeLocation: 336,232,1
NodeSize: 48,24
ValueState: 2,496,78,636,475,1,MIDM
Aliases: FormNode Fo980260733
ReformVal: [Forecast_date,Null]
Att_ResultSliceState: [Hour,21,Forecast_date,1]
Att_ColorRole: Null

Decision Tariff_rates
Title: Tariff rates
Units: $ / kWh
Description: The time-of-use portions of the electricity rates as defined in the tariff documents:~
~
<a href="https://www.pge.com/tariffs/assets/pdf/tariffbook/ELEC_SCHEDS_E-TOU-C.pdf">TOU-C tariff rate schedule</a> ~
<a href="https://www.pge.com/tariffs/assets/pdf/tariffbook/ELEC_SCHEDS_E-TOU-D.pdf">TOU-D tariff rate schedule</a>~

Definition: DetermTable(Tariff_plan,Season,Peak_cat)(~
0.41333,0.34989,~
0.31624,0.29891,~
0.36476,0.2698,~
0.29089,0.27351)
NodeLocation: 224,48,1
NodeSize: 48,24
DefnState: 2,127,15,416,303,0,DFNM
Aliases: FormNode Fo1718458237
ReformDef: [Peak_cat,Season]
Att_EditSliceState: [Sys_LocalIndex('DomainIndex'),2,Season,1,Peak_cat,1]

Variable Daily_price
Title: Daily price
Units: $
Description: The projected cost of electricity used in a given day.
Definition: Sum(Hourly_price,Hour)
NodeLocation: 336,312,1
NodeSize: 48,24

Objective Monthly_bill
Title: Monthly bill
Units: $
Description: Forecasted monthly bill for electricity usage.
Definition: Aggregate( Daily_price, Round(Forecast_date,dateUnit:'M'), Forecast_date, Forecast_month)
NodeLocation: 336,392,1
NodeSize: 48,24
ValueState: 2,83,71,747,418,1,MIDM
Aliases: FormNode Fo2054002557
GraphSetup: {!50007|Graph_BarOutline:0}~
Graph_HLabelRotation:45~
Att_ForceCategorical Graph_Primary_Valdim:1

Decision Tariff_plan
Title: Tariff plan
Description: Select the Tariff rate plan that you with to compute (or both)
Definition: Choice(Self,0)
NodeLocation: 80,48,1
NodeSize: 48,24
Aliases: FormNode Fo322279293
Domain: ['TOU-C','TOU-D']
{!40300|DomainExpr: Discrete('TOU-C','TOU-D',type:['text'])}
{!40200|Att_ChoiceIndexes: Keyword Self}

Variable Hourly_rate
Title: Hourly rate
Units: $ / kWh
Description: The hourly rate that applies to a given hour and date.
Definition: Tariff_rates[Season=Hourly_season, Peak_cat=Hourly_peak_cat]
NodeLocation: 224,120,1
NodeSize: 48,24
DefnState: 2,36,52,416,303,0,DFNM
ValueState: 2,470,215,653,392,0,MIDM
ReformVal: [Hour,Forecast_date]

Alias Al2107217789
Title: forecasted usage
Definition: 1
NodeLocation: 160,232,1
NodeSize: 48,24
Original: forecasted_usage

Index Forecast_month
Title: Forecast month
Definition: Unique(Floor(Forecast_date,dateUnit:'M'))
NodeLocation: 160,392,1
NodeSize: 48,24
ValueState: 2,423,47,416,535,,MIDM
NumberFormat: 2,D,4,2,0,0,4,0,$,0,"yyyy MMM",0,,,0,0,15

Close Pricing

FormNode Fo322279293
Title: Tariff plan
Definition: 0
NodeLocation: 444,24,1
NodeSize: 132,16
NodeInfo: 1,,,,,,,89,,,,,,0
Original: Tariff_plan

FormNode Fo980260733
Title: Hourly price
Definition: 1
NodeLocation: 444,88,1
NodeSize: 132,16
NodeInfo: 1,,,,,,,89,,,,,
Original: Hourly_price

FormNode Fo2054002557
Title: Monthly bill
Definition: 1
NodeLocation: 444,120,1
NodeSize: 132,16
NodeInfo: 1,,,,,,,89,,,,,
Original: Monthly_bill

FormNode Fo1718458237
Title: Tariff rates
Definition: 0
NodeLocation: 444,56,1
NodeSize: 132,16
NodeInfo: 1,,,,,,,89,,,,,
Original: Tariff_rates

Close Time_of_use_pricing