'Created by BT 06/18/2018 'File is Data pull for MMR tables wfcreate MMRData m 1997 2050 cd c:\warem32\ '*************************************************************************** 'NGB file data pull pagerename untitled NGBData %ED = "2023" pageselect NGBData import Y:\monthly\eviews\tables\MMRData.xlsx range="NGB" colhead=7 namepos=first @freq m 1997 'Importing attributes pageload(page=attr) y:\monthly\eviews\tables\MMRData.xlsx range=ngb!B1:AT2 byrow colhead=0 names=(name, description) pageselect ngbdata importattr attr @keep description 'create total bcf series (not bcf/day series) group g_all * g_all.drop RESID INVUS INVCAN Days Series01 %vars = g_all.@members for %x {%vars} series {%x}ann = {%x}*days next include y:\monthly\eviews\tables\seasonal_balances_data 'creating annual series pagecreate untitled A 1997 2050 pagerename untitled AnnualNGBData pageselect AnnualNGBData copy(c=s) NGBData\*ann AnnualNGBData\*ann group g_all * g_all.drop RESID SERIES01 %zz = g_all.@members %vars = @replace(%zz,"ann","") import Y:\monthly\eviews\tables\MMRData.xlsx range="daysann" @freq a 1997 for %x {%vars} series {%x} = {%x}/daysann next 'retitle US series series ddtius = ddtiusann series dtius = dtiusann series stwus = stwusann series dinjus = dinjusann series ddtus = ddtusann series dnmxexus = dnmxexusann series dlngexus = dlngexusann series sprodus= sprodusann series slngsous= slngsousann series scanimpus= scanimpusann series swdus = swdusann series stus=stusann series dresus=dresusann series dcomus=dcomusann series dindus=dindusann series dpowus=dpowusann series dvehus=dvehusann series dplpfus=dplpfusann series dtus=dtusann series uaus=uausann series niwus = niwusann series dtexus = dtexusann delete *usann 'retitle can series series ddtican = ddticanann series dtican = dticanann series stwcan = stwcanann series drescan = drescanann series dcomcan = dcomcanann series dindcan = dindcanann series dpoWCAN = dpoWCANann series dvehcan = dvehcanann series dplpfcan = dplpfcanann series dinjcan = dinjcanann series ddtcan = ddtcanann series dnplexcan = dnplexcanann series dlngexcan = dlngexcanann series dtcan = dtcanann series sprodcan = sprodcanann series slngsocan = slngsocanann series swdCAN = swdCANann series stcan = stcanann series uacan = uacanann series niwcan = niwcanann delete *canann daysann pageselect NGBData delete *usann *canann pageselect annualngbdata importattr attr @keep description '**************************************************************************** 'ECON data pull 'Annual pagecreate untitled A 2005 2050 pagerename untitled ECONA pageselect ECONA wfopen Y:\monthly\deflators\ngbdata copy NGBData::USDA\CGDPRUS MMRData::ECONA\CGDPRUS copy NGBData::USDA\GDPRUS MMRData::ECONA\GDPRUS 'copy NGBData::USDA\DeflatorYRUS MMRdata::ECONA\DeflatorYRUS copy NGBData::CANDA\deflatorYRCAN MMRdata::ECONA\DeflatorYRCAN copy NGBData::exporta\RXcan MMRData::ECONA\RXcan wfclose ngbdata wfopen y:\monthly\eviews\workfiles\2020\mmr022020_lt copy mmr022020_lt::annual\deflatoryrus mmrdata::econa\deflatoryrus wfclose mmr022020_lt pagedelete attr pageload(page=attr) y:\monthly\eviews\tables\MMRData.xlsx range=attributes!CK1:CO2 byrow colhead=0 names=(name, description) pageselect econa importattr attr @keep description 'Monthly pagecreate untitled m 2005 2050 pagerename untitled ECONM pageselect ECONM wfopen Y:\monthly\deflators\ngbdata copy NGBData::RXm\RXcan MMRdata::ECONM\rxcan copy NGBData::CANDM\deflatoryrcan MMRdata::ECONM\deflatoryrcan 'copy NGBData::USDM\deflatoryrus MMRdata::ECONM\deflatoryrus wfclose ngbdata wfopen y:\monthly\eviews\workfiles\2020\mmr022020_lt copy mmr022020_lt::monthly\deflatoryrus mmrdata::econm\deflatoryrus wfclose mmr022020_lt pagedelete attr pageload(page=attr) y:\monthly\eviews\tables\MMRData.xlsx range=attributes!CK1:CO2 byrow colhead=0 names=(name, description) pageselect econm importattr attr @keep description '**************************************************************************** 'OIl and European gas prices pagecreate untitled a 2005 2050 pagerename untitled OilGas pageselect OilGas import Y:\monthly\eviews\tables\MMRData.xlsx range="EuroGas_OilLT" colhead=7 namepos = first @freq a 2005 'import attributes pagedelete attr pageload(page=attr) Y:\monthly\eviews\tables\MMRData.xlsx range=EuroGas_OilLT!B1:H2 byrow colhead=0 names=(name, description) pageselect oilgas importattr attr @keep description series DPBC = 6.25 series CPGC = 5.83 series CODPBC = 5.8 series cpnwti = pnwti/CODPBC series cpndf2guco = pndf2guco/100*42/CPGC series cpndguco = pndguco/DPBC series cpndf2nyor = pndf2nyor/100*42/CPGC series cpndnyor = pndnyor/DPBC pagedelete attr pageload(page=attr) y:\monthly\eviews\tables\MMRData.xlsx range=attributes!CO1:CS2 byrow colhead=0 names=(name, description) pageselect oilgas importattr attr @keep description '**************************************************************************** 'Basis price data pull pagecreate untitled m 2005 2050 pagerename untitled basis pageselect basis import Y:\monthly\eviews\tables\MMRData.xlsx range="BASIS" colhead=7 namepos=first @freq m 2005 pagedelete attr pageload(page=attr) y:\monthly\eviews\tables\MMRData.xlsx range="basis!B1:AJ2" byrow colhead=0 names=(name, description) pageselect basis importattr attr @keep description 'creating hub prices group g_all * g_all.drop RESID series01 pnhh %zz = g_all.@members %hubs = @replace(%zz,"BASIS","") for %x {%hubs} series pn{%x} = pnhh + basis{%x} next 'smpl 2005m1 2006m3 'series pndspt = 0 'smpl 2005m1 2012m3 'series pntgp=0 smpl 2005 2050 'creating spreads to major markets 'APPA and Northeast Spreads for %x appa hh dawn series smnyor{%x} = pnnyor-pn{%x} next 'Southeast Spreads for %x HH EATX FLZ3 series smtcz4{%x} = pntcz4 - pn{%x} next series smflz3hh = pnflz3-pnhh 'Midwest and Eastern Canada spreads 'Chicago for %x aeco vent mdct eatx series smchic{%x} = pnchic-pn{%x} next 'Michigan for %x chic mdct hh series smmi{%x} = pnmi-pn{%x} next 'Dawn for %x aeco mi series smdawn{%x} = pndawn-pn{%x} next 'California series smmaliaeco = pnmali-pnaeco series smpgecmali = pnpgec-pnmali series smsocaopal = pnsoca-pnopal series smpgecsoca = pnpgec-pnsoca 'Spreads from supply areas 'Western Canada for %x wcs2 aeco series sssuma{%x} = pnsuma-pn{%x} next for %x king mali vent chic mi dawn series ss{%x}aeco = pn{%x} - pnaeco next 'rocky mountains for %x stan soca sanj chey series ss{%x}opal = pn{%x}-pnopal next for %x mdct appa series ss{%x}chey = pn{%x}-pnchey next 'MDCO for %x vent chic mi eatx waha series ss{%x}mdct = pn{%x}-pnmdct next 'East TX for %x chic hh katy series ss{%x}eatx = pn{%x}-pneatx next 'waha for %x sanj mdct katy eatx series ss{%x}waha = pn{%x}-pnwaha next 'San Juan for %x opal soca waha series ss{%x}sanj = pn{%x}-pnsanj next 'AECO C$/GJ job copy ECONM\RXCAN basis\RXCAN series GJC = 1.0546 series PNAECOCGJ = PNAECO/GJC/RXCAN 'creating real prices copy econm\deflatoryrus basis\deflatoryrus series prhh = pnhh/deflatoryrus copy econm\deflatoryrcan basis\deflatoryrcan series praeco = pnaeco/deflatoryrus series praecocgj = pnaecocgj/deflatoryrcan 'pull in attributes pagedelete attr pageload(page=attr) Y:\monthly\eviews\tables\MMRData.xlsx range="attributes!B1:CJ2" byrow colhead=0 names=(name, description) pageselect basis importattr attr @keep description include y:\monthly\eviews\tables\seasonal_price_data 'creating annual prices 'create weighted annual prices 'wfopen days 'copy days::monthly\daysm mmrdata::basis\daysm 'wfclose days 'group g_vars pn* pr* basis* sm* ss* '%vars = g_vars.@members 'for %x {%vars} ' series ann{%x} = {%x}*daysm 'next pagecreate untitled A 2005 2050 pagerename untitled PricesAnnual pageselect PricesAnnual for %x pr* pn* basis* sm* ss* copy(c=a) mmrdata::basis\{%x} pricesannual\{%x} next 'create weighted annual prices 'wfopen days 'copy days::annual\daysa mmrdata::PricesAnnual\daysa 'wfclose days 'copy(c=s) mmrdata::basis\ann* mmrdata::PricesAnnual\ann* 'group g_vars ann* '%vars = g_vars.@members '%vars = @replace(%vars,"ANN","") ' 'for %x {%vars} ' series {%x} = ann{%x}/daysa 'next copy econa\deflatoryrus pricesannual\deflatoryrus copy econa\deflatoryrcan pricesannual\deflatoryrcan series prhh = pnhh/deflatoryrus series praeco = pnaeco/deflatoryrus series praecocgj = pnaecocgj/deflatoryrcan 'hardcode DSPT TGP first year values! smpl 2006 2006 series basisdspt = 0.281111111 series pndspt = 6.684077658 '**************************************************************************** 'US HDD/CDD Data Pull pagecreate untitled m 2000 2050 pagerename untitled DegreeDay pageselect DegreeDay pagecreate(page=ddannual) a 2000 2050 wfopen y:\weather\history\monthly\monthly_weather for %x hdd cdd for %y pl can us nagguco nagsoea copy monthly_weather::{%x}\*{%y}* mmrdata::degreeday\*{%y}* copy monthly_weather::{%x}annual\*{%y}* mmrdata::ddannual\*{%y}* next next wfclose monthly_weather pageselect degreeday delete *plsoea *pltexa *plsela *summer* *winter* *nagus* pageselect ddannual delete *plsoea *pltexa *plsela *nagus* pagedelete attr pageload(page=attr) Y:\monthly\eviews\tables\MMRData.xlsx range="attributes!CU:GH2" byrow colhead=0 names=(name, description) pageselect degreeday importattr attr @keep description ''****Rest of year values from normals pageselect degreeday !x = @obs(hddus) %endhist = @otods(!x) !eh = @dateval(%endhist) !ey = @datepart(!eh,"yyyy") !em = @datepart(!eh,"mm") if (!em<12) then !fm = (!em+1) %yf = @str(!ey) %mf = @str(!fm) %fstart = %yf+"m"+%mf %fend = %yf+"m12" group regs hdd* %vars = regs.@members %vars = @replace(%vars,"HDD","") for %x {%vars} for %y cdd hdd smpl %fstart %fend series {%y}{%x} = n{%y}{%x} next next else endif pageselect ddannual pagedelete attr pageload(page=attr) y:\monthly\eviews\tables\MMRData.xlsx range="attributes!CU:JV2" byrow colhead=0 names=(name, description) pageselect ddannual importattr attr @keep description '**************Data export procs pagecreate untitled m 2005 %ED pagerename untitled MthExportXLSX 'US NGB DATA %vars = "sprodus slngsous scanimpus stus dresus dcomus dindus dpowus dvehus dplpfus ddtus dnmxexus dlngexus dtexus dtus uaus niwus invus" '"dresus dcomus dindus dpowus dvehus dplpfus dinjus ddtius ddtus dnmxexus dlngexus dtius dtus sprodus slngsous scanimpus swdus stwus stus uaus invus" for %x {%vars} copy ngbdata\{%x} MthExportXLSX\{%x} next pagecreate untitled m 2005 %ED pagerename untitled MthExportACCESS for %x {%vars} copy ngbdata\{%x} MthExportACCESS\{%x} next '******XLSX EXPORT pageselect MthExportXLSX logmode(filename="c:\temp\log.txt") all pagesave(t=excel, mode=update, attr) c:\warem32\mmr_st_pgcr.xlsx range="Table3 USBalancesMonthly!a6" @freq m 2005 %ED @keep sprodus slngsous scanimpus stus dresus dcomus dindus dpowus dvehus dplpfus ddtus dnmxexus dlngexus dtexus dtus uaus niwus invus '****CANADA MONTHLY NGB DATA %vars = "sprodcan slngsocan stcan drescan dcomcan dindcan dpowcan dvehcan dplpfcan ddtcan dnplexcan dlngexcan dtcan uacan niwcan invcan" '"drescan dcomcan dindcan dpowcan dvehcan dplpfcan dinjcan ddtican ddtcan dnplexcan dlngexcan dtican dtcan sprodcan slngsocan swdcan stwcan stcan uacan invcan" for %x {%vars} copy ngbdata\{%x} MthExportXLSX\{%x} next for %x {%vars} copy ngbdata\{%x} MthExportACCESS\{%x} next pagesave(t=excel, mode=update, attr) c:\warem32\mmr_st_pgcr.xlsx range="Table4 CanBalancesMonthly!a6" @freq m 2005 %ED @keep sprodcan slngsocan stcan drescan dcomcan dindcan dpowcan dvehcan dplpfcan ddtcan dnplexcan dlngexcan dtcan uacan niwcan invcan delete *can '****PRICE OUTLOOK pageselect basis group g_basis basis* pn* pr* sm* ss* %vars = g_basis.@members 'group g_price pn* '%price = g_price.@members '%vars = @wunion(%basis,%price) for %z xlsx access for %x {%vars} copy basis\{%x} mthexport{%z}\{%x} copy econm\deflatoryrus mthexport{%z}\deflatoryrus copy econm\rxcan mthexport{%z}\rxcan copy econm\deflatoryrcan mthexport{%z}\deflatoryrcan next next delete g_basis pageselect mthexportXLSX group g_all basis* sm* ss* pagesave(t=excel, mode=update, attr) c:\warem32\mmr_st_pgcr.xlsx range="Table2 NAGasBasisMonthly!a6" @freq m 2005 %ED @keep pnhh prhh pnaeco pnaecocgj praeco praecocgj basisaeco basisagua basisappa basisbost basischey basischic basiscig basiscgml basisdawn basisdspt basiseatx basisflz3 basishsc basiskaty basisking basismali basismdct basismi basisnyor basisopal basispgec basisrex3 basissanj basissoca basissotx basisstan basissuma basistcm3 basisleid basistcz4 basistcz5 basisvent basiswaha basiswcs2 smchicaeco smchiceatx smchicmdct smchicvent smdawnaeco smdawnmi smflz3hh smmaliaeco smmichic smmihh smmimdct smnyorappa smnyordawn smnyorhh smpgecmali smpgecsoca smsocaopal smtcz4eatx smtcz4flz3 smtcz4hh ssappachey sscheyopal sschicaeco sschiceatx sschicmdct ssdawnaeco sseatxmdct sseatxwaha sshheatx sskatyeatx sskatywaha sskingaeco ssmaliaeco ssmdctchey ssmdctwaha ssmiaeco ssmimdct ssopalsanj sssanjopal sssanjwaha sssocaopal sssocasanj ssstanopal sssumaaeco sssumawcs2 ssventaeco ssventmdct sswahamdct sswahasanj deflatoryrus deflatoryrcan rxcan 'pnhh prhh pnaeco pnaecocgj praeco praecocgj g_all deflatoryrus deflatoryrcan rxcan group g_all pn* pr* g_all.drop pnhh pnaeco pnaecocgj prhh praeco praecocgj pagesave(t=excel, mode=update, attr) c:\warem32\mmr_st_pgcr.xlsx range="Table1 NAGasPricesMonthly!a6" @freq m 2005 %ED @keep pnhh prhh pnaeco pnaecocgj praeco praecocgj pnagua pnappa pnbost pnchey pnchic pncig pncgml pndawn pndspt pneatx pnflz3 pnhsc pnkaty pnking pnmali pnmdct pnmi pnnyor pnopal pnpgec pnrex3 pnsanj pnsoca pnsotx pnstan pnsuma pntcm3 pnleid pntcz4 pntcz5 pnvent pnwaha pnwcs2 deflatoryrus deflatoryrcan rxcan 'pnhh prhh pnaeco pnaecocgj praeco praecocgj g_all deflatoryrus deflatoryrcan rxcan delete basis* pn* pr* sm* ss* g_all deflatoryrus deflatoryrcan rxcan '*******Monthly HDD/CDD data export smpl 2005 %endhist pageselect degreeday for %x hdd cdd group g_{%x} {%x}pl* {%x}nag* {%x}us {%x}can dn{%x}* %vars = g_{%x}.@members for %y {%vars} for %z XLSX ACCESS copy degreeday\{%y} mthexport{%z}\{%y} next next next pageselect mthexportxlsx group g_hdd *hdd* group g_cdd *cdd* !i = @dateval(%endhist) !j = @datepart(!i,"YYYY") %endex = @str(!j) pagesave(t=excel, mode=update, attr) c:\warem32\mmr_st_pgcr.xlsx range = "Table6 HDDMonthly!a6" @freq m @smpl 2005 %endex @keep hddUS hddplnoea hddplneap hddplnenn hddplneat hddplnene hddplmdct hddplmcmw hddplmclo hddplmcpl hddnagguco hddnagsoea hddplseat hddplsesc hddplwest hddplwtca hddplwtpn hddplwtds hddplromo hddcan hddnagcana hddnagcane hddnagcanw dnhddUS dnhddplnoea dnhddplneap dnhddplnenn dnhddplneat dnhddplnene dnhddplmdct dnhddplmcmw dnhddplmclo dnhddplmcpl dnhddnagguco dnhddnagsoea dnhddplseat dnhddplsesc dnhddplwest dnhddplwtca dnhddplwtpn dnhddplwtds dnhddplromo dnhddcan dnhddnagcana dnhddnagcane dnhddnagcanw pagesave(t=excel, mode=update, attr) c:\warem32\mmr_st_pgcr.xlsx range = "Table5 CDDMonthly!a6" @freq m @smpl 2005 %endex @keep cddUS cddplnoea cddplneap cddplnenn cddplneat cddplnene cddplmdct cddplmcmw cddplmclo cddplmcpl cddnagguco cddnagsoea cddplseat cddplsesc cddplwest cddplwtca cddplwtpn cddplwtds cddplromo cddcan cddnagcana cddnagcane cddnagcanw dncddUS dncddplnoea dncddplneap dncddplnenn dncddplneat dncddplnene dncddplmdct dncddplmcmw dncddplmclo dncddplmcpl dncddnagguco dncddnagsoea dncddplseat dncddplsesc dncddplwest dncddplwtca dncddplwtpn dncddplwtds dncddplromo dncddcan dncddnagcana dncddnagcane dncddnagcanw delete * '********Annual export data '****US NGB DATA pagecreate untitled a 2005 %ED pagerename untitled AnnExportXLSX %vars = "sprodus slngsous scanimpus stus dresus dcomus dindus dpowus dvehus dplpfus ddtus dnmxexus dlngexus dtexus dtus uaus" '"dresus dcomus dindus dpowus dvehus dplpfus dinjus ddtius ddtus dnmxexus dlngexus dtius dtus sprodus slngsous scanimpus swdus stwus stus uaus" for %x {%vars} copy Annualngbdata\{%x} AnnExportXLSX\{%x} next pagecreate untitled a 2005 %ED pagerename untitled AnnExportACCESS for %x {%vars} copy Annualngbdata\{%x} AnnExportACCESS\{%x} next '******XLSX EXPORT pageselect AnnExportXLSX pagesave(t=excel, mode=update, attr) c:\warem32\mmr_st_pgcr.xlsx range="Table9 USBalancesAnnual!a6" @freq a 2005 %ED @keep sprodus slngsous scanimpus stus dresus dcomus dindus dpowus dvehus dplpfus ddtus dnmxexus dlngexus dtexus dtus uaus delete *us '****CAN NGB DATA %vars = "sprodcan slngsocan stcan drescan dcomcan dindcan dpowcan dvehcan dplpfcan ddtcan dnplexcan dlngexcan dtcan uacan" for %x {%vars} copy Annualngbdata\{%x} AnnExportXLSX\{%x} next for %x {%vars} copy Annualngbdata\{%x} AnnExportACCESS\{%x} next '*****Export pageselect AnnExportXLSX pagesave(t=excel, mode=update, attr) c:\warem32\mmr_st_pgcr.xlsx range="Table10 CanBalancesAnnual!a6" @freq m 2005 %ED @keep sprodcan slngsocan stcan drescan dcomcan dindcan dpowcan dvehcan dplpfcan ddtcan dnplexcan dlngexcan dtcan uacan delete *can '******Annual price data pageselect pricesannual group g_basis basis* pn* sm* ss* pr* %vars = g_basis.@members for %z xlsx access for %x {%vars} copy pricesannual\{%x} annexport{%z}\{%x} copy econa\deflatoryrus annexport{%z}\deflatoryrus copy econa\rxcan annexport{%z}\rxcan copy econa\deflatoryrcan annexport{%z}\deflatoryrcan next next delete g_basis pageselect annexportXLSX group g_all basis* sm* ss* pagesave(t=excel, mode=update, attr) c:\warem32\mmr_st_pgcr.xlsx range="Table8 NAGasBasisAnnual!a6" @freq m 2005 %ED @keep pnhh prhh pnaeco pnaecocgj praeco praecocgj basisaeco basisagua basisappa basisbost basischey basischic basiscig basiscgml basisdawn basisdspt basiseatx basisflz3 basishsc basiskaty basisking basismali basismdct basismi basisnyor basisopal basispgec basisrex3 basissanj basissoca basissotx basisstan basissuma basistcm3 basisleid basistcz4 basistcz5 basisvent basiswaha basiswcs2 smchicaeco smchiceatx smchicmdct smchicvent smdawnaeco smdawnmi smflz3hh smmaliaeco smmichic smmihh smmimdct smnyorappa smnyordawn smnyorhh smpgecmali smpgecsoca smsocaopal smtcz4eatx smtcz4flz3 smtcz4hh ssappachey sscheyopal sschicaeco sschiceatx sschicmdct ssdawnaeco sseatxmdct sseatxwaha sshheatx sskatyeatx sskatywaha sskingaeco ssmaliaeco ssmdctchey ssmdctwaha ssmiaeco ssmimdct ssopalsanj sssanjopal sssanjwaha sssocaopal sssocasanj ssstanopal sssumaaeco sssumawcs2 ssventaeco ssventmdct sswahamdct sswahasanj deflatoryrus deflatoryrcan rxcan 'pnhh prhh pnaeco pnaecocgj praeco praecocgj g_all deflatoryrus deflatoryrcan rxcan group g_all pn* pr* g_all.drop pnhh pnaeco pnaecocgj prhh praeco praecocgj pagesave(t=excel, mode=update, attr) c:\warem32\mmr_st_pgcr.xlsx range="Table7 NAGasPricesAnnual!a6" @freq m 2005 %ED @keep pnhh prhh pnaeco pnaecocgj praeco praecocgj pnagua pnappa pnbost pnchey pnchic pncig pncgml pndawn pndspt pneatx pnflz3 pnhsc pnkaty pnking pnmali pnmdct pnmi pnnyor pnopal pnpgec pnrex3 pnsanj pnsoca pnsotx pnstan pnsuma pntcm3 pnleid pntcz4 pntcz5 pnvent pnwaha pnwcs2 deflatoryrus deflatoryrcan rxcan 'pnhh prhh pnaeco pnaecocgj praeco praecocgj g_all deflatoryrus deflatoryrcan rxcan delete g_all basis* sm* ss* deflatoryrus deflatoryrcan rxcan '********Oil price and european gas priceoutlook export pageselect OilGas group g_all cpn* pnwti pnnbp pnbafa %vars = g_all.@members for %x {%vars} for %y XLSX ACCESS copy OilGas\{%x} annexport{%y}\{%x} next next delete g_all %vars = "gdprus deflatoryrus cgdprus" for %x {%vars} for %y XLSX ACCESS copy econa\{%x} annexport{%y}\{%x} next next pageselect annexportxlsx 'pagesave(t=excel, mode=update, attr) c:\warem32\mmr_st_pgcr.xlsx range="Table11 OilEuroGas!a6" @freq a 2005 %ED @keep PNWTI CPNWTI CPNDF2GuCo CPNDGuCo CPNDF2NYOR CPNDNYOR PNNBP PNBAFA GDPRUS CGDPRUS DEFLATORYRUS delete pn* cpn* *gdp* deflator* '********HDD CDD Annual Export !i = @dateval(%endhist) !j = @datepart(!i,"YYYY") !k = @datepart(!i,"mm") !jj = !j+1 if !k > 10 then %endex = @str(!jj) else %endex = @str(!j) endif smpl 2005 %endex pageselect ddannual for %x cdd hdd summer winter group g_{%x} *{%x}* %vars = g_{%x}.@members for %y {%vars} for %z XLSX ACCESS copy ddannual\{%y} annexport{%z}\{%y} next next next %endex = @str(!j) pageselect annexportxlsx 'pagesave(t=excel, mode=update, attr) c:\warem32\mmr_st_pgcr.xlsx range = "Table13 HDDAnnual!a6" @freq a @smpl 2005 %endex @keep hddUS hddplnoea hddplneap hddplnenn hddplneat hddplnene hddplmdct hddplmcmw hddplmclo hddplmcpl hddnagguco hddnagsoea hddplseat hddplsesc hddplwest hddplwtca hddplwtpn hddplwtds hddplromo hddcan hddnagcana hddnagcane hddnagcanw dnhddUS dnhddplnoea dnhddplneap dnhddplnenn dnhddplneat dnhddplnene dnhddplmdct dnhddplmcmw dnhddplmclo dnhddplmcpl dnhddnagguco dnhddnagsoea dnhddplseat dnhddplsesc dnhddplwest dnhddplwtca dnhddplwtpn dnhddplwtds dnhddplromo dnhddcan dnhddnagcana dnhddnagcane dnhddnagcanw winterUS winterplnoea winterplneap winterplnenn winterplneat winterplnene winterplmdct winterplmcmw winterplmclo winterplmcpl winternagguco winternagsoea winterplseat winterplsesc winterplwest winterplwtca winterplwtpn winterplwtds winterplromo wintercan winternagcana winternagcane winternagcanw dnwinterUS dnwinterplnoea dnwinterplneap dnwinterplnenn dnwinterplneat dnwinterplnene dnwinterplmdct dnwinterplmcmw dnwinterplmclo dnwinterplmcpl dnwinternagguco dnwinternagsoea dnwinterplseat dnwinterplsesc dnwinterplwest dnwinterplwtca dnwinterplwtpn dnwinterplwtds dnwinterplromo dnwintercan dnwinternagcana dnwinternagcane dnwinternagcanw 'pagesave(t=excel, mode=update, attr) c:\warem32\mmr_st_pgcr.xlsx range = "Table12 CDDAnnual!a6" @freq a @smpl 2005 %endex @keep cddUS cddplnoea cddplneap cddplnenn cddplneat cddplnene cddplmdct cddplmcmw cddplmclo cddplmcpl cddnagguco cddnagsoea cddplseat cddplsesc cddplwest cddplwtca cddplwtpn cddplwtds cddplromo cddcan cddnagcana cddnagcane cddnagcanw dncddUS dncddplnoea dncddplneap dncddplnenn dncddplneat dncddplnene dncddplmdct dncddplmcmw dncddplmclo dncddplmcpl dncddnagguco dncddnagsoea dncddplseat dncddplsesc dncddplwest dncddplwtca dncddplwtpn dncddplwtds dncddplromo dncddcan dncddnagcana dncddnagcane dncddnagcanw summerUS summerplnoea summerplneap summerplnenn summerplneat summerplnene summerplmdct summerplmcmw summerplmclo summerplmcpl summernagguco summernagsoea summerplseat summerplsesc summerplwest summerplwtca summerplwtpn summerplwtds summerplromo summercan summernagcana summernagcane summernagcanw dnsummerUS dnsummerplnoea dnsummerplneap dnsummerplnenn dnsummerplneat dnsummerplnene dnsummerplmdct dnsummerplmcmw dnsummerplmclo dnsummerplmcpl dnsummernagguco dnsummernagsoea dnsummerplseat dnsummerplsesc dnsummerplwest dnsummerplwtca dnsummerplwtpn dnsummerplwtds dnsummerplromo dnsummercan dnsummernagcana dnsummernagcane dnsummernagcanw 'delete * include y:\monthly\eviews\tables\seasonal_pricebasis_export_st '********Creating WF release cd c:\warem32\ !z=@dateval(%endhist) !n=@datepart(!z,"mm") !yy=@datepart(!z,"yyyy") if(!n = 12) then !m = 1 !y = !yy + 1 else !m=!n+1 !y = !yy endif if(!m < 10) then %mth = "0" + @str(!m) else %mth = @str(!m) endif %yr = @str(!y) %vintage = %mth + %yr %name = "MMR" + %vintage +"_ST.wf1" !j = @fileexist(%name) if !j = 1 then shell del /f c:\warem32\{%name} endif wfcreate MMR{%vintage}_ST.wf1 m 2005 %ED pagerename untitled Monthly copy MMRData::mthexportaccess\* MMR{%vintage}_ST::Monthly\* delete g_* wfselect MMR{%vintage}_ST pagecreate untitled a 2005 %ED pagerename untitled Annual Copy MMRData::annexportaccess\* MMR{%vintage}_ST::annual\* delete g_* wfsave MMR{%vintage}_ST close MMRData wfselect MMR{%vintage}_ST pagecreate untilted u 1 1000 pagerename untitled mnemonic_definitions pageselect mnemonic_definitions import y:\monthly\eviews\tables\mmrdata.xlsx range="coverage" wfsave MMR{%vintage}_ST wfsave MMRDATA_Current wfclose MMRData_current wfopen mmr{%vintage}_st '-----------storing in EDB 'Annual Bank %name = "MMR" + %vintage + "_A_ST" + ".edb" !j = @fileexist(%name) if !j = 1 then dbdelete %name endif dbcreate %name pageselect annual group g_all * g_all.drop resid store g_all close %name.edb %name = "MMR" + %vintage + "_M_ST" + ".edb" !j = @fileexist(%name) if !j = 1 then dbdelete %name endif dbcreate %name pageselect monthly group g_all * g_all.drop resid store g_all close %name.edb ''storing in access database 'wfselect mmr{%vintage}_ST 'pageselect monthly 'group g_all * 'g_all.drop resid 'pagesave(t=access, mode=overwrite) c:\warem32\MMR{%vintage}_ST_M.accdb monthly @freq m @smpl 2005 %ED @keep g_all ' '' annual db 'pageselect annual 'group g_weather cdd* hdd* dn* summer* winter* 'pagesave(t=access, mode=overwrite) c:\warem32\MMR{%vintage}_ST_A.accdb weather @freq m @smpl 2005 %ED @keep g_weather ' 'group g_price pn* pr* basis* sm* ss* deflatoryr* rxcan 'pagesave(t=access, mode=overwrite) c:\warem32\MMR{%vintage}_ST_A.accdb price @freq m @smpl 2005 %ED @keep g_price ' 'group g_balances dres* dcom* dind* dpow* dveh* dplpf* ddt* dnmxex* dlng* dt* sprod* slngso* scanimp* st* ua* dnplex* 'pagesave(t=access, mode=overwrite) c:\warem32\MMR{%vintage}_ST_A.accdb balances @freq m @smpl 2005 %ED @keep g_balances ' 'group g_oileurogas pnwti cpn* pnnbp pnbafa gdprus cgdprus deflatoryrus 'pagesave(t=access, mode=overwrite) c:\warem32\MMR{%vintage}_ST_A.accdb oileurogas @freq m @smpl 2005 %ED @keep g_oileurogas pageselect monthly delete g_all pageselect annual delete g_all shell copy c:\warem32\mmrdata_current.wf1 Y:\monthly\eviews\workfiles\mmrdata_current.wf1 %name = "MMR"+%vintage+"_st.wf1" %d = @date !dd = @dateval(%d) %mn = @datestr(!dd,"month ") !yy = @datepart(!dd,"yyyy") !mm = @datepart(!dd,"mm") %y = @str(!yy) %m = @str(!mm) %dir = "Y:\monthly\eviews\workfiles\"+%y+"\" %path = %dir+%name %dir1 = "c:\warem32\" %path1 = %dir1+%name %path = @addquotes(%path) %path1 = @addquotes(%path1) wfclose shell copy %path1 %path close @wf include Y:\monthly\eviews\tables\mmrdata_st_ple