%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
% 
% Create VGR time series of real
% Y, C, G, I, NEx, Depreciation rate, Capital stock, labour & Years Schooling
% for BRD 1960/1970-1989
%
% Daniel Fehrle
% June 2023,
% update 22.08.2023, indize ehancement for labor statistics
% update 04.06.2025, interest rate and CPI inflation
%
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%

clear;
net=1;
%%%%%%%%%%%%%%%%%%%%VGR from destatis Lange Reihen ab 1970
if net==0
    capital_1991=6353.64*1.95583; %Gross Volkswirtschaftliche Gesamtrechnungen Fachserie 18 Reihe S.29 Inlandsproduktsberechnung Revidierte Jahresergebnisse 2006, Mrd Euro 1991
else
    capital_1991=4078.08*1.95583; %Net Volkswirtschaftliche Gesamtrechnungen Fachserie 18 Reihe S.29 Inlandsproduktsberechnung Revidierte Jahresergebnisse 2006, Mrd Euro 1991
end

va_nom=readmatrix("inlandsprodukt-lange-reihen-xlsx-2180150(2).xlsx","Sheet","2.1","Range","B15:B36")*1.95583;
va_real=readmatrix("inlandsprodukt-lange-reihen-xlsx-2180150(2).xlsx","Sheet","2.2","Range","B15:B36");

vgr_nom=readmatrix("inlandsprodukt-lange-reihen-xlsx-2180150(2).xlsx","Sheet","3.1","Range","B15:L36")*1.95583;
vgr_real=readmatrix("inlandsprodukt-lange-reihen-xlsx-2180150(2).xlsx","Sheet","3.2","Range","B15:L36");

depr2gdp=readmatrix("inlandsprodukt-lange-reihen-xlsx-2180150(2).xlsx","Sheet","1.2 % des BIP","Range","G15:G36")/100;

all60t70=readmatrix("VGR_WEST_non70-90.xlsx","Sheet","NIPA + Labor","Range","B2:L12"); 

vgre_real=[all60t70(:,1) all60t70(:,3) +all60t70(:,4)+all60t70(:,5)+all60t70(:,6)... %GDP, DU
            all60t70(:,3)+all60t70(:,4) all60t70(:,3) all60t70(:,4)... %Cons
            all60t70(:,5)+all60t70(:,6) all60t70(:,5) all60t70(:,6)... % Inve
            NaN(size(all60t70,1),1) all60t70(:,7) all60t70(:,8)]; % Trade
                
vgre_real=vgre_real./vgre_real(end,:);

 
depr_nom=depr2gdp.*vgr_nom(:,1);

va_1989=va_nom(end-2).*va_real/va_real(end-2);
va_1989=[va_1989(1)*all60t70(1:end-1,2)/all60t70(end,2); va_1989];

vgr_1989=vgr_nom(end-2,:).*vgr_real./vgr_real(end-2,:);
vgr_1989(:,8)=vgr_1989(:,6)-vgr_1989(:,7); %Inventories
vgr_1989=[vgr_1989(1,:).*vgre_real(1:end-1,:)./vgre_real(end,:); vgr_1989];
vgr_1989(:,9)=vgr_1989(:,10)-vgr_1989(:,11); % Nex


%PI for investment
PI=vgr_nom(:,7)./vgr_1989(11:end,7);

%real depr.
depr_1989=depr_nom./PI;
depr_1989=[depr_1989(1).*all60t70(1:end-1,end-2)./all60t70(end,end-2); depr_1989];



%PIM for capital
capital_1991(size(depr_1989,1))=capital_1991;
capital_1989=capital_1991'./PI(end);

for i=1:31

    capital_1989(end-i)=capital_1989(end+1-i)-vgr_1989(end-i,7)+depr_1989(end-i); %capital beginning of the year
        
end


%%%%%%%%%%%%%Money markets

MM=readmatrix("Zins.xlsx","Range","B2:D32");
MM=MM/100+1;
MM=[(1960:1990)',MM(:,3),MM(:,1),MM(:,2)];
writematrix(MM,'mny_w.csv');

%%%%labor stats

lf=readmatrix("inlandsprodukt-lange-reihen-xlsx-2180150(2).xlsx","Sheet","1.13","Range","B15:B36");
lf=[all60t70(1:end-1,end-1)*lf(1)/all60t70(end,end-1); lf];
%lf=[all60t70(1:end-1,end-1); lf];
hours=readmatrix("inlandsprodukt-lange-reihen-xlsx-2180150(2).xlsx","Sheet","1.13","Range","D15:D36");

hours=[all60t70(1:end-1,end)*hours(1)/all60t70(end,end); hours];
%hours=[all60t70(1:end-1,end); hours];
hwrk=hours.*lf;

%%%%%human capital
tyr=readmatrix("VGR_WEST_non70-90.xlsx","Sheet","Human","Rang","B2:C32");
tyr=fillmissing(tyr,'spline');
tyr=[tyr; [NaN NaN]];
tyr=fillmissing(tyr,'linear');
tyr_pc=tyr;

%per capita

pop=readmatrix("inlandsprodukt-lange-reihen-xlsx-2180150(2).xlsx","Sheet","1.12","Range","B15:B36");
pop=[readmatrix("VGR_WEST_non70-90.xlsx","Sheet","NIPA + Labor","Range","M2:M11"); pop];
va_1989_pc=va_1989./pop;
vgr_1989_pc=vgr_1989./pop;
lf_pc=lf./pop;
hwrk_pc=hwrk./pop;
depr_1989_pc=depr_1989./pop;
capital_1989_pc=capital_1989./pop;
tyr=tyr.*pop;

%
names={'T','C','G','I','V','Ex','Im','Nex','GDP','VA','DU','Depr','Capital','LaborForce','H','YS25','YS15','pop'};
names_pc={'T','c','g','i','v','ex','im','nex','gdp','va','du','depr','capital','laborforce','h','ys25','ys15','pop'};
data=array2table([(1960:1991)' vgr_1989(:,[4 5 7 8 10 11 9 1]) va_1989 vgr_1989(:,2) depr_1989 capital_1989 lf hwrk tyr pop],'VariableNames',names); 
data_pc=array2table([(1960:1991)' vgr_1989_pc(:,[4 5 7 8 10 11 9 1]) va_1989_pc vgr_1989_pc(:,2) depr_1989_pc capital_1989_pc lf_pc hwrk_pc tyr_pc pop],'VariableNames',names_pc); 
if net==0
writetable(data,'abs.csv','WriteRowNames',true);
writetable(data_pc,'pc.csv','WriteRowNames',true);
else
writetable(data,'abs_net.csv','WriteRowNames',true);
writetable(data_pc,'pc_net.csv','WriteRowNames',true);
end

%%%%%%%%%%%%%% for comparison

vgr_BIP=[vgr_1989(:,[4 5 7]) vgr_1989(:,8)+vgr_1989(:,9)]./vgr_1989(:,1);
cap_BIP=capital_1989./vgr_1989(:,1);
depr_cap=depr_1989./capital_1989;
GDP_growth=[NaN; vgr_1989(2:end,1)./vgr_1989(1:end-1,1)];
pop_growth=[NaN; pop(2:end)./pop(1:end-1)];
names_BIP={'T','cy','gy','iy','nexy','yg', 'H', 'tyr', 'gpop'};
data_BIP=array2table([(1960:1991)' vgr_BIP GDP_growth hwrk_pc tyr_pc(:,2) pop_growth cap_BIP depr_cap]);
if net==0
writetable(data_BIP,'py.csv','WriteRowNames',false);
else
writetable(data_BIP,'py_net.csv','WriteRowNames',false);
end
