Measure Transition Risk for Loan Portfolios Based on Climate Scenarios with Paths
This example shows the effect of transition risk on portfolios of loans from two banks under two different climate scenarios. In this example, each climate scenario uses 400 simulations and the visualizations reflect a distribution of portfolio outcomes.
Download and Import Climate Data
This example uses climate scenario data from the MIT Joint Program on the Science and Policy of Global Change. For details about this freely available data, see the MIT data repository.
Download the raw data from the MathWorks support files area.
filename = matlab.internal.examples.downloadSupportFile("risk","MITUncertaintyData.zip");
Make a folder in your current directory for the climate data.
if not(isfolder("MITUncertaintyData")) mkdir("MITUncertaintyData") end
Unzip the downloaded files into the created folder.
MITDataFolder = fullfile(pwd,"MITUncertaintyData");
unzip(filename,MITDataFolder)The data set has three climate scenarios. The Scenario_Descriptions.docx file provides a description of the scenarios.
scenarios = ["1.5C","2C","Ref"];
Read the climate data into MATLAB® by using the helper function importClimateData in a for–loop.
energies = ["Coal" "Gas" "Oil" "Nuclear" "Hydro" "Biomass" "Renewables"]; regions = ["GLB" "USA" "CAN" "MEX" "JPN" "ANZ" "EUR" "ROE" "RUS" "ASI" "CHN" "IND" "BRA" "AFR" "MES" "LAM" "REA" "KOR" "IDZ"]; climateDataDictionary = dictionary; for scenario = scenarios for energy = energies for region = regions key = strcat(scenario, energy, region); climateDataDictionary{key} = importClimateData(scenario, energy, region); end end end
Stack the scenarios into a tall data set.
vals = values(climateDataDictionary);
allClimateData = vertcat(vals{:});
head(allClimateData) Simulation Year Energy Use Scenario Region Energy
__________ ____ __________ ________ ______ ______
1 2020 151.31 1.5C GLB Coal
1 2025 137.82 1.5C GLB Coal
1 2030 135.75 1.5C GLB Coal
1 2035 14.962 1.5C GLB Coal
1 2040 18.553 1.5C GLB Coal
1 2045 33.185 1.5C GLB Coal
1 2050 42.375 1.5C GLB Coal
1 2055 40.205 1.5C GLB Coal
Process Climate Data
The tall data set, allClimateData, has one column for energy use. Unstack the data so that the energy use for each climate scenario has a separate column.
regionEnergyClimateData = unstack(allClimateData, "Energy Use", "Scenario", "GroupingVariables", ["Simulation", "Year", "Region", "Energy"], "NewDataVariableNames", ["EnergyUse1_5C", "EnergyUse2C", "EnergyUseRef"]); regionEnergyClimateData = regionEnergyClimateData(:, ["Region", "Energy", "Year", "Simulation", "EnergyUseRef", "EnergyUse1_5C", "EnergyUse2C"]); head(regionEnergyClimateData)
Region Energy Year Simulation EnergyUseRef EnergyUse1_5C EnergyUse2C
______ ______ ____ __________ ____________ _____________ ___________
GLB Coal 2020 1 156.3 151.31 151.31
GLB Coal 2025 1 158.09 137.82 137.82
GLB Coal 2030 1 159.73 135.75 135.75
GLB Coal 2035 1 167.23 14.962 97.463
GLB Coal 2040 1 158.86 18.553 70.469
GLB Coal 2045 1 159.5 33.185 45.762
GLB Coal 2050 1 161.88 42.375 56.019
GLB Coal 2055 1 162.6 40.205 70.153
Create rows for the total by simulation, year, and region for each scenario. You need the totals to compute market share and shocks.
totalData = groupsummary(regionEnergyClimateData, ... ["Simulation", "Year", "Region"], ... "sum",["EnergyUse1_5C", "EnergyUse2C", "EnergyUseRef"]); totalData = removevars(totalData,"GroupCount"); totalData = renamevars(totalData, ... ["sum_EnergyUse1_5C", "sum_EnergyUse2C", "sum_EnergyUseRef"], ... ["EnergyUse1_5C", "EnergyUse2C", "EnergyUseRef"]); totalData.Energy = repmat("Total",[height(totalData) 1]); totalData.Energy = categorical(totalData.Energy); regionEnergyClimateData = [regionEnergyClimateData; totalData]; regionEnergyClimateData = movevars(regionEnergyClimateData, ... ["Region", "Energy"],"Before","Simulation"); regionEnergyClimateData = sortrows(regionEnergyClimateData, ... ["Region","Energy","Simulation","Year"]);
Join the total data as a column.
totalDataJoin = removevars(totalData,"Energy"); totalDataJoin = renamevars(totalDataJoin,["EnergyUse1_5C" "EnergyUse2C" "EnergyUseRef"], ... ["TotalEnergyUse1_5C" "TotalEnergyUse2C" "TotalEnergyUseRef"]); regionEnergyClimateData = join(regionEnergyClimateData,totalDataJoin);
Define the market share shock. For more details on this definition, see Profitability and Shocks on Market Share.
Let denote the market share at time of sector in the region in scenario . Define similarly. Then, the market share shock is defined as
Compute the market share and shocks.
% Compute in chunks c = cell(1,400); for i = 1:400 dataSlice = regionEnergyClimateData(regionEnergyClimateData.Simulation == i,:); % Market share is forced to be nonzero. dataSlice.MarketShareRef = max(dataSlice.EnergyUseRef ./ dataSlice.TotalEnergyUseRef, 1e-6); dataSlice.MarketShare1_5C = max(dataSlice.EnergyUse1_5C ./ dataSlice.TotalEnergyUse1_5C, 1e-6); dataSlice.MarketShare2C = max(dataSlice.EnergyUse2C ./ dataSlice.TotalEnergyUse2C, 1e-6); % Compute the scenario shocks. dataSlice.Shock1_5C = (dataSlice.MarketShare1_5C - dataSlice.MarketShareRef) ./ dataSlice.MarketShareRef; dataSlice.Shock2C = (dataSlice.MarketShare2C - dataSlice.MarketShareRef) ./ dataSlice.MarketShareRef; % Cap the shock so the maximum positive shock has the same magnitude as % the maximum negative shock dataSlice.Shock1_5CCap = min(dataSlice.Shock1_5C, 1); dataSlice.Shock2CCap = min(dataSlice.Shock2C, 1); c{1,i} = dataSlice; end regionEnergyClimateData = vertcat(c{:}); head(regionEnergyClimateData)
Year Region Energy Simulation EnergyUseRef EnergyUse1_5C EnergyUse2C TotalEnergyUse1_5C TotalEnergyUse2C TotalEnergyUseRef MarketShareRef MarketShare1_5C MarketShare2C Shock1_5C Shock2C Shock1_5CCap Shock2CCap
____ ______ ______ __________ ____________ _____________ ___________ __________________ ________________ _________________ ______________ _______________ _____________ __________ __________ ____________ __________
2020 GLB Coal 1 156.3 151.31 151.31 562.85 562.85 577.26 0.27077 0.26882 0.26882 -0.0071761 -0.0071761 -0.0071761 -0.0071761
2025 GLB Coal 1 158.09 137.82 137.82 571.77 571.77 613.93 0.25751 0.24104 0.24104 -0.063951 -0.063951 -0.063951 -0.063951
2030 GLB Coal 1 159.73 135.75 135.75 584.18 584.18 636.23 0.25106 0.23237 0.23237 -0.074431 -0.074431 -0.074431 -0.074431
2035 GLB Coal 1 167.23 14.962 97.463 292.45 546.08 690.27 0.24227 0.05116 0.17848 -0.78883 -0.2633 -0.78883 -0.2633
2040 GLB Coal 1 158.86 18.553 70.469 285.89 521.59 719.77 0.22071 0.064898 0.1351 -0.70596 -0.38786 -0.70596 -0.38786
2045 GLB Coal 1 159.5 33.185 45.762 287.79 500.71 753.71 0.21162 0.11531 0.091395 -0.45511 -0.56811 -0.45511 -0.56811
2050 GLB Coal 1 161.88 42.375 56.019 297.72 455.21 788.06 0.20542 0.14233 0.12306 -0.30711 -0.40091 -0.30711 -0.40091
2055 GLB Coal 1 162.6 40.205 70.153 298.28 423.58 813.78 0.19981 0.13479 0.16562 -0.32543 -0.17114 -0.32543 -0.17114
Process Portfolio Data
To process the portfolio data, you use a simulation that is similar to the one in Measure Transition Risk for Loan Portfolios with Respect to Climate Scenarios, but this simulation has more granular regions and additional simulated loan-level data.
First, load the data.
load("ClimateLoanPortfolioDataGranular.mat") ClimateLoanPortfolioDataGranular.BorrowerSector = renamecats(ClimateLoanPortfolioDataGranular.BorrowerSector,["Bioenergy", "Renewables (wind&solar)"],["Biomass","Renewables"]);
Generate histograms to show the distribution of loans by region and sector for the two simulated banks.
LoanPortfolioDataBank1 = ClimateLoanPortfolioDataGranular(ClimateLoanPortfolioDataGranular.Bank=="Bank1",:); LoanPortfolioDataBank2 = ClimateLoanPortfolioDataGranular(ClimateLoanPortfolioDataGranular.Bank=="Bank2",:); [CountsRegion1, BinsRegion1] = histcounts(LoanPortfolioDataBank1.GranularBorrowerRegion); [CountsSector1, BinsSector1] = histcounts(LoanPortfolioDataBank1.BorrowerSector); [CountsRegion2, BinsRegion2] = histcounts(LoanPortfolioDataBank2.GranularBorrowerRegion); [CountsSector2, BinsSector2] = histcounts(LoanPortfolioDataBank2.BorrowerSector); figure tiledlayout(2,1) nexttile b1 = barh(categorical(BinsRegion1), [CountsRegion1; CountsRegion2], "FaceColor", "flat"); xlabel("Loans") ylabel("Regions") title("Distribution of Regions") legend(["Bank 1", "Bank 2"],"Location","best") b1(1).CData = [0.633, 0.078, 0.243]; b1(2).CData = [0.467, 0.675, 0.188]; fig = gcf; currentPosition = get(fig, 'Position'); newHeight = currentPosition(4) * 1.5; set(fig, 'Position', [currentPosition(1), currentPosition(2), currentPosition(3), newHeight]); nexttile b2 = barh(categorical(BinsSector1), [CountsSector1; CountsSector2], "FaceColor", "flat"); xlabel("Loans") ylabel("Sectors") title("Distribution of Sectors") legend(["Bank 1", "Bank 2"],"Location","best") b2(1).CData = [0.633, 0.078, 0.243]; b2(2).CData = [0.467, 0.675, 0.188];
Bank 1 is heavily invested in oil fossil fuel projects, whereas Bank 2 is heavily invested in hydro green energy products. Regarding the geographical distribution of projects, both banks are similar, with a larger number of projects in developing regions like China, India, and Africa. Because Bank 2 requires a faster transition away from fossil fuels, it is likely to outperform Bank 1 more quickly in the 1.5C scenario than in the 2C scenario.
The portfolio represents a snapshot taken on December 31, 2020. This implies a static balance sheet during the time horizon, which might not be realistic. You can use a dynamic balance sheet in this step to analyze different portfolio evolution scenarios. Create a data set that represents the same portfolio with snapshots at five-year intervals from 2020 to 2095.
portfolioSnapshots = dictionary; for year = 2020:5:2095 value = ClimateLoanPortfolioDataGranular; value.Snapshot = categorical(repmat(year,height(ClimateLoanPortfolioDataGranular),1)); portfolioSnapshots{year} = value; end portfolioSnapshots = vertcat(portfolioSnapshots.values{:});
Compute Change in Valuation
Create a large data set that contains each loan and snapshot with the 400 shocks by performing an outer join between the portfolio snapshots and climate data.
First, prepare the climate data for joining, then join the data sets.
regionEnergyClimateDataJoin = table(regionEnergyClimateData.Energy, ... regionEnergyClimateData.Region, ... regionEnergyClimateData.Year, ... regionEnergyClimateData.Simulation, ... regionEnergyClimateData.Shock1_5CCap, ... regionEnergyClimateData.Shock2CCap, ... VariableNames = ... ["BorrowerSector", ... "GranularBorrowerRegion", ... "Snapshot", ... "Simulation", ... "Shock1_5CCap", ... "Shock2CCap"]); portfolioSnapshotsClimateData = outerjoin(portfolioSnapshots,regionEnergyClimateDataJoin,"MergeKeys",true,"Type","left");
Calculate the loan level changes in the probability of default (PD) and valuation by using the following equations derived in Borrower's Default Conditions and Climate Policy Shocks
.
Assume that , , and that controls the size of the exogenous shock . Because the exogenous shock is independent of climate shock, you can chose it independently of the simulation. It suffices to let be the maximum over each loan ID and snapshot pair of the quantity derived in Profitability and Shocks on Market Share.
Next, compute the observation level .
portfolioSnapshotsClimateData.deltajTemp1_5C = 2 .* portfolioSnapshotsClimateData.BookValue .* (1 + abs(portfolioSnapshotsClimateData.Shock1_5CCap)); portfolioSnapshotsClimateData.deltajTemp2C = 2 .* portfolioSnapshotsClimateData.BookValue .* (1 + abs(portfolioSnapshotsClimateData.Shock2CCap));
Find the maximum over each loan ID and snapshot pair, which is independent of the scenario and simulation.
deltaTableTemp = grpstats(portfolioSnapshotsClimateData, ... ["LoanID", "Snapshot"], "max", ... "DataVars", ["deltajTemp1_5C", "deltajTemp2C"]); deltaTableJoin = table; deltaTableJoin.LoanID = deltaTableTemp.LoanID; deltaTableJoin.Snapshot = deltaTableTemp.Snapshot; deltaTableJoin.deltaj1_5C = deltaTableTemp.max_deltajTemp1_5C; deltaTableJoin.deltaj2C = deltaTableTemp.max_deltajTemp2C;
Join the values back to the main data set.
portfolioSnapshotsClimateData = outerjoin(portfolioSnapshotsClimateData, ... deltaTableJoin, "MergeKeys", true, "Type", "left"); portfolioSnapshotsClimateData.pdChange1_5C = -portfolioSnapshotsClimateData.Shock1_5CCap .* portfolioSnapshotsClimateData.BookValue ./ portfolioSnapshotsClimateData.deltajTemp1_5C; portfolioSnapshotsClimateData.pdChange2C = -portfolioSnapshotsClimateData.Shock2CCap .* portfolioSnapshotsClimateData.BookValue ./ portfolioSnapshotsClimateData.deltajTemp2C; portfolioSnapshotsClimateData.valChange1_5C = -portfolioSnapshotsClimateData.FaceValue .* portfolioSnapshotsClimateData.pdChange1_5C; portfolioSnapshotsClimateData.valChange2C = -portfolioSnapshotsClimateData.FaceValue .* portfolioSnapshotsClimateData.pdChange2C;
Aggregate the valuation changes by bank, snapshot, and simulation.
valChangeSummary = groupsummary(portfolioSnapshotsClimateData, ... ["Bank","Snapshot","Simulation"], "sum", ... ["FaceValue","valChange1_5C","valChange2C"]); valChangeSummary.percentChangeVal1_5C = 100 * valChangeSummary.sum_valChange1_5C ./ valChangeSummary.sum_FaceValue; valChangeSummary.percentChangeVal2C = 100 * valChangeSummary.sum_valChange2C ./ valChangeSummary.sum_FaceValue; valChangeSummary = removevars(valChangeSummary, ... ["GroupCount","sum_FaceValue","sum_valChange1_5C","sum_valChange2C"]);
Calculate percentiles for the valuation changes.
valChangePrctiles = groupsummary(valChangeSummary, ... ["Bank","Snapshot"], ... {@(y) prctile(y,5), @(y) prctile(y,50), @(y) prctile(y,95)}); % Rename the percentile variables valChangePrctiles = renamevars(valChangePrctiles, ... ["fun1_percentChangeVal1_5C", ... "fun2_percentChangeVal1_5C", ... "fun3_percentChangeVal1_5C", ... "fun1_percentChangeVal2C", ... "fun2_percentChangeVal2C", ... "fun3_percentChangeVal2C"], ... ["percentChangeVal1_5C_5", ... "percentChangeVal1_5C_50", ... "percentChangeVal1_5C_95", ... "percentChangeVal2C_5", ... "percentChangeVal2C_50", ... "percentChangeVal2C_95"]); valChangePrctiles = removevars(valChangePrctiles, ["fun1_Simulation", "fun2_Simulation", "fun3_Simulation"]);
Visualize Results
Create a histogram of the distribution of changes in valuation by year. In the 1.5C scenario, the greener Bank 2 distribution separates from Bank 1 much faster than in the 2C scenario.
scenario ="1.5C"; scenarioDataName = matlab.lang.makeValidName("percentChangeVal" + scenario); figure; t = tiledlayout(5,3); for year = 2025:5:2095 bank1Data = valChangeSummary(valChangeSummary.Snapshot == string(year) & valChangeSummary.Bank == "Bank1",:); bank2Data = valChangeSummary(valChangeSummary.Snapshot == string(year) & valChangeSummary.Bank == "Bank2",:); ax = nexttile; h1 = histogram(ax, bank1Data.(scenarioDataName)); hold(ax, "on"); h2 = histogram(ax, bank2Data.(scenarioDataName)); h1.BinWidth = .5; h2.BinWidth = .5; titleString = string(year); title(ax, titleString); xlim(ax, [-20,20]); % Define colors for Bank 1 and Bank 2 colorBank1 = [0.633, 0.078, 0.243]; colorBank2 = [0.467, 0.675, 0.188]; h1.FaceColor = colorBank1; h2.FaceColor = colorBank2; end leg = legend(["Bank 1", "Bank 2"]); leg.Layout.Tile = "south"; title(t,"Change in Portfolio Value " + scenario + " (%)");
The following plots show the change in portfolio value by percentile in each snapshot. The losses for the Bank 1 are generally highest near the end of the horizon. The gains for Bank 2 are closer to the middle of the horizon. You can interpret the percentile values as a type of value at risk (VaR).
valChangePrctilesUnstack = unstack(valChangePrctiles, ... ["percentChangeVal1_5C_5","percentChangeVal1_5C_50","percentChangeVal1_5C_95","percentChangeVal2C_5","percentChangeVal2C_50","percentChangeVal2C_95"],"Bank"); x = valChangePrctilesUnstack.Snapshot; scenario ="1.5C"; y = table2array(valChangePrctilesUnstack(:,contains(valChangePrctilesUnstack.Properties.VariableNames, matlab.lang.makeValidName("percentChangeVal" + scenario)))); figure; % Plot Bank 1 lines hold on; plot(x, y(:,1), "-", 'LineWidth', 1.5, "Color", colorBank1, "DisplayName", "Bank 1 - 5th percentile"); plot(x, y(:,3), "--", 'LineWidth', 1.5, "Color", colorBank1, "DisplayName", "Bank 1 - 50th percentile"); plot(x, y(:,5), ":", 'LineWidth', 1.5, "Color", colorBank1, "DisplayName", "Bank 1 - 95th percentile"); % Plot Bank 2 lines plot(x, y(:,2), "-", 'LineWidth', 1.5, "Color", colorBank2, "DisplayName", "Bank 2 - 5th percentile"); plot(x, y(:,4), "--", 'LineWidth', 1.5, "Color", colorBank2, "DisplayName", "Bank 2 - 50th percentile"); plot(x, y(:,6), ":", 'LineWidth', 1.5, "Color", colorBank2, "DisplayName", "Bank 2 - 95th percentile"); legend("Location","bestoutside"); xlabel("Snapshot Year"); title("Change in Portfolio Value " + scenario + " (%)", "FontWeight", "normal"); hold off;
The box and whisker plots offer an alternative visualization. The Bank 1 pathways outperform Bank 2 in the short term, especially in the 2C scenario. The separation of distributions becomes clearer further into the future, and the separation occurs more quickly in the 1.5C scenario.
scenario ="1.5C"; scenarioDataName = matlab.lang.makeValidName("percentChangeVal" + scenario); figure; boxchart(valChangeSummary.Snapshot, valChangeSummary.(scenarioDataName), "GroupByColor", valChangeSummary.Bank); colororder([colorBank1; colorBank2]); xlabel("Snapshot Year"); title("Change in Portfolio Value " + scenario + " (%)", "FontWeight", "normal"); legend(["Bank 1", "Bank 2"]);
Conclusion
The methodology used in this example provides a way to calculate the impact of climate scenarios on an obligor's PD. This example uses climate scenarios that have 400 simulations, which allows you to visualize a distribution of portfolio outcomes. The portfolio that is invested in green technologies performs better in the scenarios requiring a faster transition away from fossil fuels.
Methodology
Background
Monasterolo et al provide a methodology for performing a portfolio valuation that accounts for climate scenarios [1]. The methodology first computes a market share shock for different energy sectors based on the scenarios, and then converts this into a shock for the PD of each investment. By aggregating the shocks to the PD, you can calculate the change in value of the portfolio. For more information about this methodology, see [1].
Consider a financial actor endowed with a portfolio of investments through loan contracts. Each investment is represented as a distinct borrower, . Consider three time variables:
Let denote the time at which the portfolio valuation is taken.
Let denote the time at which a climate policy shock potentially occurs.
Let denote the maturity of borrower 's obligation.
In particular, for each borrower ,
.
Let denote the valuation of obligation at time . Then, the valuation of the portfolio at time , , is given by
Assume that the valuation of obligation is based on the expected value of the loan. For each obligor , consider the following:
Let denote the face value of the loan, already including the time-discounting factor.
Let be the probability that the borrower defaults at time given the information available at time .
Let denote the recovery rate of the loan contract.
Then, assume
Climate Policy Shocks
At time , the occurrence of a climate policy shock implies that the economy switches from a business-as-usual scenario characterized by no climate policy, , to a scenario, , where the market shares of some economic sectors are affected. Assume that this transition modifies the PD of the borrower through changes in the market share of the economic sector of . Let denote the change in default probability for obligor when going from scenario to scenario . The change in default probability implies a proportional change in the expected value of the loan:
Next, assume below.
Borrower's Default Conditions
Let denote the total assets of borrower at time in scenario . Let be the shock occurring at time associated with the shift from scenario to scenario . Let denote an exogenous stochastic shock hitting the asset side of the borrower at the maturity date . Then,
Assume that the borrower defaults at time if its net worth, defined as assets minus liabilities, at maturity becomes negative as a result of the two shocks. Let denote the net worth at time in scenario . Note that is independent of the climate scenarios. Let denote the value of obligor 's liabilities, which is assumed to be independent of time and the climate scenarios. Then, the default condition becomes
You can rewrite the default condition as
When , there is no policy shock:
Let be the probability density function of the idiosyncratic shock . Then,
which allows you to express the difference in probability as
Profitability and Shocks on Market Share
Assume that the policy shock impacts the obligor's balance sheet, and thus the expected value of the loan, via a change in the market share of the obligor's economic sector. Let denote the market share at time of sector in the region in scenario . Define similarly. Then the market share shock is defined as
Let denote the elasticity of profitability with respect to market share. A single value is chosen for all obligors. Assume that the relative change in net worth due to the change in scenarios is proportional to the relative market share shock:
Assume that is uniformly distributed with support width and mean . For sufficiently large, you can express the difference in probability as
Then,
The change in PD has been linked to the climate scenarios. However, you need to choose and appropriately. Assume that which you should view as a limitation because could be chosen at the obligor level to reflect empirical data.
Before addressing in more detail, consider the previous equation:
.
The left hand side must be in the range [1,1]. However, the range in practice is actually much smaller. The climate scenarios in this example express a wide range of outcomes, which leads to very large shocks on the right side of the equation. As such, choose a sufficiently large to move those shocks back into the expected range. You cap the shocks to keep them in a more reasonable range. In practice, an institution implementing this methodology decides what shock levels are consistent with their expectations and data.
Next, identify bounds for . To compute as above, you need the interval of integration to be contained in the support of the function , which has been assumed to be . Since you have no view on the direction of the exogenous shock , assume that . It suffices for to be at least as large as The latter term is the absolute value of the shock times the starting book value . It suffices to choose such that
In practice, choose to be equal to the right hand side.
Note that implicitly depends on and and you can choose uniformly across climate scenarios. As reflects the size of the exogenous shocks, ensure that does not depend on the choice of a particular climate scenario.
Finally, note that the quantity appearing in the expression for does not depend on when you choose as above.
Local Functions
importClimateData
The following helper function reads in the downloaded Excel® climate data into a table based on the scenario, energy type, and region.
function climateData = importClimateData(scenario, energy, region) % Construct the path to the scenario folder scenarioFolder = fullfile(pwd, "MITUncertaintyData", scenario); % Find the file with the energy use data filePattern = fullfile(scenarioFolder, "*energy_use_" + energy + "*"); fileStruct = dir(filePattern); file = fileStruct.name; filePath = fullfile(scenarioFolder, file); % Get the sheet names and find the index of the sheet that contains the region sheets = sheetnames(filePath); sheetIdx = contains(sheets, region); sheet = sheets(sheetIdx); % Set up the Import Options and import the data opts = spreadsheetImportOptions("NumVariables", 21); % Specify sheet and range opts.Sheet = sheet; opts.DataRange = "A2:U401"; % Specify column names and types opts.VariableNames = ["Var1", "Var2", "Var3", "VarName4", "VarName5", "VarName6", "VarName7", "VarName8", "VarName9", "VarName10", "VarName11", "VarName12", "VarName13", "VarName14", "VarName15", "VarName16", "VarName17", "VarName18", "VarName19", "VarName20", "VarName21"]; opts.SelectedVariableNames = ["VarName4", "VarName5", "VarName6", "VarName7", "VarName8", "VarName9", "VarName10", "VarName11", "VarName12", "VarName13", "VarName14", "VarName15", "VarName16", "VarName17", "VarName18", "VarName19", "VarName20", "VarName21"]; opts.VariableTypes = ["char", "char", "char", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double", "double"]; % Specify variable properties opts = setvaropts(opts, ["Var1", "Var2", "Var3"], "WhitespaceRule", "preserve"); opts = setvaropts(opts, ["Var1", "Var2", "Var3"], "EmptyFieldRule", "auto"); opts = setvaropts(opts, ["VarName4", "VarName5", "VarName6", "VarName7", "VarName8", "VarName9", "VarName10", "VarName11", "VarName12", "VarName13", "VarName14", "VarName15", "VarName16", "VarName17", "VarName18", "VarName19", "VarName20", "VarName21"], "FillValue", 0); % Import the data climateData = readtable(filePath, opts, "UseExcel", false); clear opts % Reshape and format the data climateData = stack(climateData,2:18,"NewDataVariableName","Energy Use","IndexVariableName","Year"); climateData.Year = renamecats(climateData.Year,["VarName5","VarName6","VarName7","VarName8","VarName9","VarName10","VarName11","VarName12","VarName13","VarName14","VarName15","VarName16","VarName17","VarName18","VarName19","VarName20","VarName21"], ... ["2020", "2025", "2030", "2035", "2040", "2045", "2050", "2055", "2060", "2065", "2070", "2075", "2080", "2085", "2090", "2095", "2100"]); dataHeight = height(climateData); climateData = renamevars(climateData,"VarName4","Simulation"); climateData.Scenario = categorical(repmat(scenario,[dataHeight 1])); climateData.Region = categorical(repmat(region,[dataHeight 1])); climateData.Energy = categorical(repmat(energy,[dataHeight 1])); end
References
[1] Monasterolo, I., Zheng, Jiani I., and S. Battiston. "Climate Transition Risk and Development Finance: A Carbon Risk Assessment of China's Overseas Energy Portfolios." China and World Economy. 26, 6(116–142), 2018. Available at: https://onlinelibrary.wiley.com/doi/epdf/10.1111/cwe.12264.
