clc;
clear all;
close all;
% Read exle file
filename = 'C:\PhD in MCM\Dofasco project\MeOH\GAMS_MEOH\NPVwithtax3.xlsx';
Excel = actxserver('Excel.Application');
Workbooks = Excel.Workbooks;
Excel.Visible =1;

sheet = 9;
x1Range = 'D1:G6';  %NPV factors
x2Range = 'N1:R8';  %Location effects
x3Range = 'D1:H1';  %AF factor
x4Range = 'B1:K27';  %Methanol history price and PPP
x5Range = 'B1:K27';  %Electricity history price


[A D both] = xlsread(filename,sheet,x1Range);
[B C both] = xlsread(filename,sheet,x2Range);   % B read numbers; C read text. both means read both
network_station_coordinates = both;
E = xlsread (filename,10,x3Range);      % AF factors
F = xlsread(filename,11,x4Range);       %MeOH historical price and PPP
G = xlsread(filename,12,x5Range);       %Electricity history price and currency exchange rate

for j = 1:1:1  % AF factor
    
    for i =  1:1:2     % case study
%         figure (i+(j-1)*5)
%      
            xlswrite(filename,B(2,i),15,'G3');        %write carbon tax into Cashflow sheet
            xlswrite(filename,B(6,i),15,'G6');        %write electricity carbon intensity into Cashflow sheet
            xlswrite(filename,B(7,i),15,'G2');                  %write income tax into Cashflow sheet
            
        for k = 1:1:13  %historical data
            xlswrite(filename,F(1+2*(k-1),i),15,'G5');        %write MeOH price into Cashflow sheet Location MeOH price
            xlswrite(filename,G(1+2*(k-1),i),15,'G7');        %write Electricity price into Cashflow sheet
            xlswrite(filename,B(1,i),15,'G8');      %write PPP 2018 data into Cashflow sheet
            xlswrite(filename,B(5,i),15,'G9');      %write exchange rate 2018 into Cashflow sheet
           
            N2 = xlsread(filename,15,'N2');             %TCI paid by loan
            C5 = xlsread(filename,15,'C5');             %loan interest rate
            C6 = xlsread(filename,15,'C6');             % loan lifetime
            f = @(AL) N2*C5/100/(1-(1/(1+C5/100))^C6) -AL;           %use fzero function to find the annual loan payment
            C7 = fzero(f,12);                        %annual loan payment
            xlswrite(filename,C7,15,'C7');          %write annual loan payment in C7 Cashflow sheet
            
          
    %             
            
%______________________________________________________________________

% xlswrite(filename,A,sheet,xlRange) writes to the specified worksheet and range.
  
    if i == 1
        startingColumn = 'D';
        
    elseif i==2
        startingColumn = 'G';
%         nextColumn = 2 + 4*(i-1);
%         range = sprintf('%s%d',startingColumn,nextColumn);
%         xlswrite(filename,NPV,10,range);
    elseif i==3
        startingColumn = 'J';  
%         nextColumn = 2 + 4*(i-1);
%         range = sprintf('%s%d',startingColumn,nextColumn);
%         xlswrite(filename,NPV,10,range);
    elseif i==4
        startingColumn = 'M';  
%         nextColumn = 2 + 4*(i-1);
%         range = sprintf('%s%d',startingColumn,nextColumn);
%         xlswrite(filename,NPV,10,range);
    else 
        startingColumn = 'P';  
%         nextColumn = 2 + 4*(i-1);
%         range = sprintf('%s%d',startingColumn,nextColumn);
%         xlswrite(filename,NPV,10,range);
    end
        nextrownpv = 2 + k;
        meoh = sprintf('%s%d',startingColumn,nextrownpv);
        NPVm = xlsread(filename,15,'AF37');     %read NPV value from excel Cashflow sheet
        xlswrite(filename,round(NPVm),13,meoh); %write NPV value to Locationhistory_NPV
        CCA = xlsread(filename,15,'G11');       %read CCA value
            if CCA < 0

                xlswrite(filename,CCA,14,meoh);         %write to Locationhistory_CCA
            else 
                xlswrite(filename,NaN,14,meoh);            %if CCA>0, that's means either its not making positive NPV or CO2 emission is not reduced.
            end
        end
      
    end
end
