%% Data Set Construction
% This file presents a 100% code method to import data. In practice it is often 
% simpler to use the import wizard (right click on a file in Current Folder, Import 
% Data...) when importing a few files.
%% Notes
% This is a 100% code method to accomplish this task. The data import wizard 
% (right click in Current Folder) is much simpler than doing everything using 
% code.
%% Setup
% Clear and reset the workspace and load required data

% Clean up everything
clear all
close all
clc
%% Index data
% The dates in the csv have been converted to YYYYDDMM using a custom format 
% in Excel (yyyymmdd).

% Skip the first row
SP500 = csvread('SP500.csv',1);
% Data is upside down.  Earliest date should be first.
SP500 = flipud(SP500);
% Convert yyyymmdd to MATLAB date format
SP500_mldate = c2mdate(SP500(:,1));
% Get close price
SP500_close = SP500(:,7);
% Get yyyymmdd dates
SP500_yyyymmdd = SP500(:,1);
% Assemble 
SP500_daily = [SP500_yyyymmdd SP500_mldate SP500_close];

% Repetition of previous code, only for FTSE
FTSE100 = csvread('FTSE100.csv',1);
FTSE100 = flipud(FTSE100);
FTSE100_mldate = c2mdate(FTSE100(:,1));
FTSE100_close = FTSE100(:,7);
FTSE100_yyyymmdd = FTSE100(:,1);
FTSE100_daily = [FTSE100_yyyymmdd FTSE100_mldate FTSE100_close];
%% Weekly aggregation
% Weekly aggregation uses Tuesday closing prices since Tuesdays are likely to 
% be trading days.

% Get earliest and latest date
x = SP500_daily;
min_date = min(x(:,2));
max_date = max(x(:,2));
% List of all dates between earliest and latest
all_dates = min_date:max_date;
% Uses US dates so that Tuesday is 3, Sunday is 1
tuesdays = weekday(all_dates, 'en_US')==3;
tuesdays = all_dates(tuesdays);

% Simple but slow method
weekly_data = zeros(length(tuesdays),3);
for i=1:length(tuesdays)
    % Find last date <= current Tuesday
    loc = find(x(:,2)<=tuesdays(i), 1, 'last' );
    % Store data
    weekly_data(i,:) = x(loc,:);
end
SP500_weekly = weekly_data;

% Repeat for FTSE, a function would be better
x = FTSE100_daily;
min_date = min(x(:,2));
max_date = max(x(:,2));
all_dates = min_date:max_date;
% Assumes US dates so that Tuesday is 3, Sunday is 1
tuesdays = weekday(all_dates)==3;
tuesdays = all_dates(tuesdays);

% Simple but slow method
weekly_data = zeros(length(tuesdays),3);
for i=1:length(tuesdays)
loc = find(x(:,2)<=tuesdays(i), 1, 'last' );
weekly_data(i,:) = x(loc,:);
end
FTSE100_weekly = weekly_data;
%% Monthly aggregation
% This code uses diff and logical indexing to find the locations where the month 
% (number) changes. This is the last day of the month.

% Use diff on day to find last day in month
x = SP500_daily;
dates = x(:,1);
year = floor(dates/10000);
month = floor((dates - 10000*year)/100);
SP500_monthly = x(diff(month)~=0,:);

x = FTSE100_daily;
dates = x(:,1);
year = floor(dates/10000);
month = floor((dates - 10000*year)/100);
FTSE100_monthly = x(diff(month)~=0,:);

% Or write a function 
price = SP500_daily(:,3);
date = SP500_daily(:,1);
[SP500_monthly_price,SP500_monthly_date] = month_end_prices(price,date);

save SP_FTSE *_daily *_weekly *_monthly
%% Fama-French Data
% All files were converted to Excel (xlsx) files. Only the top panel was in 
% both files.

FF_factors_monthly = xlsread('F-F_Research_Data_Factors.xlsx');
FF_factors_daily = xlsread('F-F_Research_Data_Factors_daily.xlsx');

FF_portfolios_monthly = xlsread('25_Portfolios_5x5.xlsx');
FF_portfolios_daily = xlsread('25_Portfolios_5x5_daily.xlsx');
save FF_data FF*
%% Foreign Exchange Rates
% This block shows some advanced features:
% 
% * Cell arrays to hold string data
% * Loops over files names
% * textscan to read data into a cell array
% * str2double to convert strings to numbers
% * eval to rename arrays using string
% 
% It is probably simpler to convert the data to Excel and import the Excel.

files = {'DEXJPUS.txt','DEXUSAL.txt','DEXUSEU.txt','DEXUSUK.txt'};
fxrates = {'JPYUSD','AUDUSD','EURUSD','GBPUSD'};
for i = 1:length(files)
    % Open the file for reading
    fid = fopen(files{i},'rt');
    % Textscan to read dates and values
    data = textscan(fid,'%s %s','ReturnOnError',false);
    % Separate the two
    dates = data{1};
    values = data{2};
    % Initialize a holder using nan, which is easy to remove later
    temp = nan(length(dates),2);
    % Loop over data
    for j=1:length(dates)
        if sum(dates{j}=='-')==2 % date
            % Convert date to yyyymmdd, note skipping - character
            temp(j,1) = str2double(dates{j}([1:4 6:7 9:10]));
            % Convert values
            temp(j,2) = str2double(values{j});
        end
    end
    % Remove any rows with nan
    temp = temp(~any(isnan(temp),2),:);
    temp = [c2mdate(temp(:,1)) temp]; %#ok<AGROW>
    % Rename the variable using a string expression
    eval([fxrates{i} ' = temp;'])
    % Save the data
    save(fxrates{i},fxrates{i})
    % Close the file
    fclose(fid);
end
%% Importing using |readtable|
% |readtable| is a more modern method to read data.  Rather than import a numeric 
% matrix, it can handle mixed data and will attempt to assign series-appropriate 
% data types to each column.  We can repeat the import of the S&P 500 using readtable 
% to see the differences.

SP500_table = readtable('SP500.csv')
SP500_table.Properties
%% 
% Reading in the data to a table reads variables names (and corrects ones 
% which are not valid MATLAB variable names).  The Properties attribute provides 
% some information about the table. 
% 
% Variables can be accessed using dot notation and the variable name.  

disp('Dot access')
SP500_table.Open
%% 
% The familiar slice notation is also usable with tables

disp('Slice')
SP500_table(1:5,1:3)
%% 
% Data can be saved in the same manner.

save SP500_table SP500_table
%% 
% Finally, tables can be converted to matrices using table2array

SP500_mat = table2array(SP500_table)
SP500_mat(1:5,1:3)
%% Reading in formatted dates
% |readtable| can import formatted dates such as 1990-01-01 from Excel or CSV.  
% This will produce a table with the dates as strings.  These can be converted 
% into datetimes using the function datetime. This avoids much of the pain of 
% working with either MATLAB dates of numeric dates of the form YYYYMMDD.
% 
% Note the difference in the tables -- the string version has '2013-10-25' 
% while the datetime version has just 2013-10-25.  One can use |isdatetime| to 
% test if a column is in datetime format. 

SP500_table = readtable('SP500_excel_dates.xlsx')
disp('Before')
isdatetime(SP500_table.Date)
SP500_table.Date = datetime(SP500_table.Date)
disp('After')
isdatetime(SP500_table.Date)
%% 
%