%% This file helps to construct macro variarable used to correlate with 
%  interest rate trend

%%  United States  1971Q4 - 2022Q1
clc
clear
close all

% (1a) Real GDP growth per capita
% real GDP index from BEA 1.1.3 line 1
% population from UN websie, in millions
mydata = readtable("data/data.xlsx",'Sheet','US');
t0 = find(mydata.Date=='01-Mar-1960');
t1 = find(mydata.Date=='01-Dec-2022');
rgdp = mydata.rgdp(t0:t1);
pop = mydata.population(t0:t1);
us_rgdp = diff(log(rgdp./pop))*100;
xdate = datetime("1960-06-01") + calmonths(0:3:750)';

% (1b) Growth of real consumption per capita 
% real consumption indexfrom BEA 1.1.3 line 2
consump = mydata.consumption(t0:t1);
us_consump = diff(log(consump./pop))*100;

% (11a) \pi_gdp
% gdp deflator from OECD,FRED
GDPdeflator = readtable("data/US_GDPdeflator.csv");
t0 = find(GDPdeflator.DATE=='1960-01-01');
t1 = find(GDPdeflator.DATE=='2022-10-01');
us_pigdp = diff(log(GDPdeflator.USAGDPDEFQISMEI(t0:t1)))*400;

% Market Return
% s&p 500 price and dividends from Robert Shiller's website
stockdata = readtable("data/US_stock.csv");
stockdata_quart = nan(size(stockdata,1)/3,2);  % convert monthly data to quarterly
for i = 3:3:size(stockdata,1)
    stockdata_quart(i/3,1) = stockdata{i,2};
    stockdata_quart(i/3,2) = sum(stockdata{i-2:i,3});
end
real_stock = 100*stockdata_quart./repmat(GDPdeflator{:,2},1,2);  % deflate prices and dividends by gdp deflator  
us_mkt_return = nan(size(real_stock,1)-1,1);   % annualized return from stock portfolio including dividends
for i = 2:size(real_stock,1)
    us_mkt_return(i-1) = (real_stock(i,1) - real_stock(i-1,1) + real_stock(i,2))/real_stock(i-1,1)*400;
end

% (2a,2b,2c,3a,3b) variance-covariance of \pi_GDP, GDP growth, market return
uspigdp = Con_Var([us_pigdp us_rgdp], 20);  % Pi and GDP growth
uspimkt = Con_Var([us_pigdp us_mkt_return], 20);  % Pi and market return
us_var_gdp = uspigdp(:,2);
us_var_mkt = uspimkt(:,2);
us_var_pi = (uspigdp(:,1) + uspimkt(:,1))/2;
us_cov_pigdp = uspigdp(:,3);
us_cov_pimkt = uspimkt(:,3);
xdate2 = xdate(21:end);

% (4a) labor hours
% growth of weekly work hours, BLS
Labor_hours = readtable("data/US_workhours.csv");
us_labor = diff(log(Labor_hours.Value(1:end-1)))*400;
xdate3 = xdate(41:end);

% (4b) Investmet Growth
% Date from OECD; quarterly growth
Investgrowth = readtable('data/US_investGrowth.csv');
us_invest = Investgrowth(18:268,"Value");

% (6b) growth of debt/GDP
% debt is total outstanding general government debt in USD, BIS
% GDP is nominal 
GDPdata = readtable('data/US_GDP.csv');
Debtdata = readtable("data/US_GovernDebt.csv");
us_gdp = str2double(GDPdata{33:end,3});
us_debt = Debtdata{33:end,2};
us_debtgrowth = diff(log(us_debt./us_gdp))*400;
xdate4 = xdate(1:end-1);

% (7) current account/gdp
% ratio from OECD
Curr = readtable("data/US_CurrentAccount.csv");
us_curr = Curr(2:end,"Value");

% (8) Relative price of investment to consumption
% BEA 1.1.3 line 7 and line 2
BEA = readtable("data/US_BEA114.csv");
bea_pce = BEA{4,56:306}';
bea_ppi = BEA{9,56:306}';
us_ic = bea_ppi./bea_pce;

% (10) yield spread
% Mody's BAA yield - 10y Treasury yield, FRED
Spread = readtable("data/US_BAA10Y.csv");
us_spread = Spread.BAA10Y(1:end-1);
xdate5 = xdate(104:end);

% (12a) M1 Growth
% U.S. M1, OECD
M1 = readtable("data/US_M1.csv");
M1 = M1(5:end,:);
us_m1 = diff(log(M1{:,"Value"}))*400;

% (12b) M3 Growth, parallel to data for UK and Germany
% OECD data
M3 = readtable("data/US_M3.csv");
M3 = M3(5:end,:);
us_m3 = diff(log(M3{:,"Value"}))*400;

% Combine variables
USvars = table(xdate);
USvars.rgdp = us_rgdp; 
USvars.consump = us_consump;
USvars.VarGDP = nan(251,1); USvars.VarGDP(21:end) = us_var_gdp;
USvars.VarPi = nan(251,1); USvars.VarPi(21:end) = us_var_pi;
USvars.VarMkt = nan(251,1); USvars.VarMkt(21:end) = us_var_mkt;
USvars.CovGDP = nan(251,1); USvars.CovGDP(21:end) = us_cov_pigdp;
USvars.CovMkt = nan(251,1); USvars.CovMkt(21:end) = us_cov_pimkt;
USvars.Labor = nan(251,1); USvars.Labor(41:end) = us_labor;
USvars.Capital = table2array(us_invest);
USvars.Debt = nan(251,1); USvars.Debt(1:end-1) = us_debtgrowth;
USvars.Current = nan(251,1); USvars.Current = table2array(us_curr);
USvars.Price = nan(251,1); USvars.Price = us_ic;
USvars.Spread = nan(251,1); USvars.Spread(104:end) = us_spread;
USvars.M1 = nan(251,1); USvars.M1 = us_m1;
USvars.M3 = nan(251,1); USvars.M3 = us_m3;

save('USdata.mat','USvars');

%% United Kingdom  1983Q1 - 2022Q1

clc
clear
close all

% (1a) Growth of real GDP per capita 
% real GDP index from Eurostat;FRED
% population from UN websie, in millions
mydata = readtable("data/data.xlsx",'Sheet','UK');
t0 = find(mydata.Date=='01-Mar-1960');
t1 = find(mydata.Date=='01-Dec-2022');
rgdp = mydata.rgdp(t0:t1);
pop = mydata.population(t0:t1);
uk_rgdp = diff(log(rgdp./pop))*100;
xdate = datetime("1960-06-01") + calmonths(0:3:750)';

% (1b) Growth of real consumption per capita 
% Real Private Sector Final Consumption Expenditure, FRED, IMF
consumpdata = readtable("data/UK_consumption.csv");
Consumption = str2double(consumpdata{:,3});
uk_consump = diff(log(Consumption./mydata.population(193:304)))*400;
xdate2 = xdate(141:end);

% (11a) \pi_gdp
% gdp deflator from OECD, FRED
GDPdeflator = readtable("data/UK_GDPdeflator.csv");
uk_pigdp = diff(log(GDPdeflator.GBRGDPDEFQISMEI))*400;

% Market Return
% FT 100 index
stockdata = readtable("data/UK_stock.csv");
stockdata_quart = nan(size(stockdata,1)/3,3);  % convert monthly data to quarterly
for i = 3:3:size(stockdata,1)
    stockdata_quart(i/3,1:2) = stockdata{i,1:2};
    stockdata_quart(i/3,3) = str2double(stockdata{i,3});
    % stockdata_quart(i/3,2) = sum(stockdata{i-2:i,3});
end
real_stock = 100*stockdata_quart(:,3)./GDPdeflator{98:end,2};  % deflate prices by gdp deflator  
uk_mkt_return = nan(size(real_stock,1)-1,1);   % annualized return from stock portfolio
for i = 2:size(real_stock,1)
    uk_mkt_return(i-1) = (real_stock(i,1) - real_stock(i-1,1))/real_stock(i-1,1)*400;
end
% 1984Q3 - 2022Q4

% (2a,2b,2c,3a,3b) variance-covariance of \pi_GDP, GDP growth, market return
ukpigdp = Con_Var([uk_pigdp uk_rgdp], 20);  % Pi and GDP growth
ukpimkt = Con_Var([uk_pigdp(98:end) uk_mkt_return], 20);  % Pi and market return
uk_var_gdp = ukpigdp(:,2);
uk_var_mkt = ukpimkt(:,2);
uk_var_pi = ukpigdp(:,1);
uk_cov_pigdp = ukpigdp(:,3);
uk_cov_pimkt = ukpimkt(:,3);
xdate3 = xdate(21:end);
xdate4 = xdate(118:end);

% (4a) labor hours
% growth of weekly work hours, The Office for National Statistics
Labor_hours = readtable("data/UK_workhours.csv");
uk_labor = diff(log(Labor_hours.AverageActualWeeklyHoursOfWorkForFull_timeWorkers_seasonallyAdj(6:end)))*400;
xdate5 = xdate(130:end);

% (4b) Investmet Growth
% Date from OECD; quarterly growth
Investgrowth = readtable('data/UK_InvestGrowth.csv');
uk_invest = Investgrowth(19:end,"Value");

% (6b) growth of debt/GDP
% debt is total outstanding general government debt in USD, BIS
% GDP is nominal 
GDPdata = readtable('data/UK_GDP.csv');
Debtdata = readtable("data/UK_GovernDebt.csv");
uk_debtgrowth = diff(log(Debtdata.TDSAMRIAOGGGB ./ GDPdata.UKNGDP))*400;
xdate5 = xdate(13:end-1);

% (7) current account/gdp
% ratio from OECD
Curr = readtable("data/UK_CurrentAccount.csv");
uk_curr = Curr(22:end,"Value");

% (8) Relative price of investment to consumption
% Producer Prices Index: Investments Goods, FRED, OECD
% CPI, FRED, OEDC
PPI = readtable("data/UK_PPI.csv");
CPI = readtable("data/UK_CPI.csv");
cpi = str2double(CPI.Var3);
uk_ic = PPI.PITGVG01GBM661N ./ cpi(197:252);
xdate6 = xdate(196:end);

% (10) yield spread
% Mody's BAA yield - 10y Treasury yield, FRED, BOE
baa = readtable("data/DBAA.csv");
Y10 = readtable("data/UK_10Y.csv");
y10 = str2double(Y10.Var3);
uk_spread = baa.DBAA(1:148) - y10(105:end);
xdate7 = xdate(104:end);

% (12a) M1 Growth
% U.K. M1, OECD
M1 = readtable("data/UK_M1.csv");
uk_m1 = diff(log(M1{2:end,"Value"}))*400;
xdate8 = xdate(109:end);

% (12b) M1 Growth
% U.K. M3, OECD
M3 = readtable("data/UK_M3.csv");
uk_m3 = diff(log(M3{:,"Value"}))*400;


% Combine variables
UKvars = table(xdate);
UKvars.rgdp = uk_rgdp;
UKvars.csonsump = nan(251,1);
UKvars.csonsump(141:end) = uk_consump;
UKvars.VarGDP = nan(251,1); UKvars.VarGDP(21:end) = uk_var_gdp;
UKvars.VarPi = nan(251,1); UKvars.VarPi(21:end) = uk_var_pi;
UKvars.VarMkt = nan(251,1); UKvars.VarMkt(118:end) = uk_var_mkt;
UKvars.CovGDP = nan(251,1); UKvars.CovGDP(21:end) = uk_cov_pigdp;
UKvars.Covmkt = nan(251,1); UKvars.Covmkt(118:end) = uk_cov_pimkt;
UKvars.Labor = nan(251,1); UKvars.Labor(130:end) = uk_labor;
UKvars.Capital = table2array(uk_invest);
UKvars.Debt = nan(251,1); UKvars.Debt(13:end-1) = uk_debtgrowth;
UKvars.Current = table2array(uk_curr);
UKvars.Price = nan(251,1); UKvars.Price(196:end) = uk_ic;
UKvars.Spread = nan(251,1); UKvars.Spread(104:end) = uk_spread;
UKvars.M1 = nan(251,1); UKvars.M1(109:end) = uk_m1;
UKvars.M3 = nan(251,1); UKvars.M3(109:end) = uk_m3;

save('UKdata.mat','UKvars');

%% Germany 1972Q4 - 2022Q1

clc
clear
close all

% real GDP index from EuroStat;FRED
% population from UN websie, in millions
mydata = readtable("data/data.xlsx",'Sheet','GM');
t0 = find(mydata.Date=='01-Mar-1960');
t1 = find(mydata.Date=='01-Dec-2022');
rgdp = mydata.rgdp(t0:t1);
pop = mydata.population(t0:t1);
gm_rgdp = diff(log(rgdp./pop))*100;
xdate = datetime("1960-06-01") + calmonths(0:3:750)';

% (1b) Growth of real consumption per capita 
% Real Private Sector Final Consumption Expenditure, FRED, IMF
consumpdata = readtable("data/GM_consumption.csv");
Consumption = consumpdata{:,2};
gm_consump = diff(log(Consumption./mydata.population(177:304)))*400;
xdate2 = xdate(125:end);

% (11a) \pi_gdp
% gdp deflator from OECD, FRED
GDPdeflator = readtable("data/GM_GDPdeflator.csv");
gdpdeflator = str2double(GDPdeflator.Var3);
gm_pigdp = diff(log(gdpdeflator))*400;
xdate3 = xdate(41:end);


% Market Return
% DAX index
stockdata = readtable("data/GM_stock.csv");
stockdata_quart = nan(size(stockdata,1)/3,2);  % convert monthly data to quarterly
for i = 3:3:size(stockdata,1)
    stockdata_quart(i/3,1) = 100*year(stockdata{i,1})+month(stockdata{i,1});
    stockdata_quart(i/3,2) = stockdata{i,2};
    % stockdata_quart(i/3,2) = sum(stockdata{i-2:i,3});
end
real_stock = 100*stockdata_quart(:,2)./gdpdeflator;  % deflate prices by gdp deflator  
gm_mkt_return = nan(size(real_stock,1)-1,1);   % annualized return from stock portfolio
for i = 2:size(real_stock,1)
    gm_mkt_return(i-1) = (real_stock(i,1) - real_stock(i-1,1))/real_stock(i-1,1)*400;
end

% (2a,2b,2c,3a,3b) variance-covariance of \pi_GDP, GDP growth, market return
gmpigdp = Con_Var([gm_pigdp(85:end) gm_rgdp(125:end)], 20);  % Pi and GDP growth
gmpimkt = Con_Var([gm_pigdp gm_mkt_return], 20);  % Pi and market return
gm_var_gdp = gmpigdp(:,2);
gm_var_mkt = gmpimkt(:,2);
gm_var_pi = gmpimkt(:,1);
gm_cov_pigdp = gmpigdp(:,3);
gm_cov_pimkt =gmpimkt(:,3);
xdate4 = xdate(145:end);
xdate5 = xdate(61:end);

% (4a) labor hours
% No quarterly labor data for germany

% (4b) Investmet Growth
% Date from OECD; quarterly growth
Investgrowth = readtable('data/GM_InvestGrowth.csv');
gm_invest = Investgrowth(:,"Value");

% (6b) growth of debt/GDP
% debt is total outstanding general government debt in USD, BIS
% GDP is nominal 
GDPdata = readtable('data/GM_GDP.csv');
Debtdata = readtable("data/GM_GovernDebt.csv");
gm_debtgrowth = diff(log(Debtdata.TDSAMRIAOGGDE(6:end) ./ GDPdata.CPMNACSCAB1GQDE))*400;
xdate5 = xdate(125:end-1);  

% (7) current account/gdp
% ratio from OECD
Curr = readtable("data/GM_CurrentAccount.csv");
gm_curr = Curr(2:end,"Value");  % xdate2

% (8) Relative price of investment to consumption
% Producer Prices Index: Investments Goods, FRED, OECD
% CPI, FRED, OEDC
PPI = readtable("data/GM_PPI.csv");
CPI = readtable("data/GM_CPI.csv");
gm_ic = PPI.PITGVG02DEM661N ./ CPI.CPALTT01DEM659N(65:end);
xdate6 = xdate(64:end);

% (10) yield spread
% Mody's BAA yield - 10y Treasury yield, FRED, BOE
baa = readtable("data/DBAA.csv");
Y10 = readtable("data/GM_10Y.csv");
gm_spread = baa.DBAA(1:148) - Y10.IRLTLT01DEM156N(105:end-1);
xdate7 = xdate(104:end);

% (12a) M1 Growth
% German M1, OECD
M1 = readtable("data/GM_M1.csv");
gm_m1 = diff(log(M1.Value))*400;  % xdate3

% (12b) M1 Growth
% German M3, OECD
M3 = readtable("data/GM_M3.csv");
gm_m3 = diff(log(M3.Value))*400;


% Combine variables
GMvars = table(xdate);
GMvars.rgdp = gm_rgdp;
GMvars.csonsump = nan(251,1);
GMvars.csonsump(125:end) = gm_consump;
GMvars.VarGDP = nan(251,1); GMvars.VarGDP(145:end) = gm_var_gdp;
GMvars.VarPi = nan(251,1); GMvars.VarPi(61:end) = gm_var_pi;
GMvars.VarMkt = nan(251,1); GMvars.VarMkt(61:end) = gm_var_mkt;
GMvars.CovGDP = nan(251,1); GMvars.CovGDP(145:end) = gm_cov_pigdp;
GMvars.Covmkt = nan(251,1); GMvars.Covmkt(61:end) = gm_cov_pimkt;
% GMvars.Labor = nan(251,1); GMvars.Labor(130:end) = gm_labor;
GMvars.Capital = table2array(gm_invest);
GMvars.Debt = nan(251,1); GMvars.Debt(125:end-1) = gm_debtgrowth;
GMvars.Current = nan(251,1); GMvars.Current(125:end) = table2array(gm_curr);
GMvars.Price = nan(251,1); GMvars.Price(64:end) = gm_ic;
GMvars.Spread = nan(251,1); GMvars.Spread(104:end) = gm_spread;
GMvars.M1 = nan(251,1); GMvars.M1(41:end) = gm_m1;
GMvars.M3 = nan(251,1); GMvars.M3(41:end) = gm_m3;

save('GMdata.mat','GMvars');


%%  Collect annual date for key variables
% TFP growth; Labor hours growth; Top 10% income share; Yield spread

Yields_data = readtable("./data/annual/yields.xlsx");
Yields_data.usspread = Yields_data.DBAA - Yields_data.US;
Yields_data.ukspread = Yields_data.DBAA - Yields_data.UK;
Yields_data.gmspread = Yields_data.DBAA - Yields_data.GM;


































