求助：如何对齐股票与基准日期构建回归矩阵X（Matlab/VBA）

你需要将不同列的日期数据对齐，把同一日期的公司价格和基准指数值放在同一行，缺失日期对应的单元格留空，方便后续回归分析计算alpha和beta。以下是Matlab和VBA两种实现方式：

Matlab 实现 #

步骤说明 #

1. 读取原始Excel数据，提取各列的日期与对应数值
2. 合并所有日期并去重排序
3. 匹配每个日期对应的数值，无匹配项留空
4. 输出规整后的数据到新Excel文件

代码实现 #

% 读取原始数据，替换为你的文件路径和工作表名
raw_data = readtable('price_data.xlsx', 'Sheet', 'Sheet1');

% 提取各列的日期与数值（根据实际列名调整）
uc_dates = raw_data.unicredit_date;
uc_vals = raw_data.unicredit_val;
int_dates = raw_data.INTESA_date;
int_vals = raw_data.INTESA_val;
ftsi_dates = raw_data.ftsi_mib_date;
ftsi_vals = raw_data.ftsi_mib_val;

% 合并所有日期，去重后降序排序
all_dates = unique([uc_dates; int_dates; ftsi_dates], 'stable');
all_dates = sort(all_dates, 'descend');

% 创建规整后的表格
aligned_data = table(all_dates, NaN(length(all_dates),1), NaN(length(all_dates),1), NaN(length(all_dates),1), ...
    'VariableNames', {'Date', 'unicredit', 'INTESA', 'ftsi_mib'});

% 匹配各列数据
[~, idx] = ismember(all_dates, uc_dates);
aligned_data.unicredit(idx~=0) = uc_vals(idx(idx~=0));

[~, idx] = ismember(all_dates, int_dates);
aligned_data.INTESA(idx~=0) = int_vals(idx(idx~=0));

[~, idx] = ismember(all_dates, ftsi_dates);
aligned_data.ftsi_mib(idx~=0) = ftsi_vals(idx(idx~=0));

% 将NaN替换为空字符串（可选，根据需求调整）
aligned_data.unicredit = replace(aligned_data.unicredit, NaN, '');
aligned_data.INTESA = replace(aligned_data.INTESA, NaN, '');
aligned_data.ftsi_mib = replace(aligned_data.ftsi_mib, NaN, '');

% 保存结果
writetable(aligned_data, 'aligned_price_data.xlsx', 'Sheet', 'AlignedData');

注意：如果原始表格中日期和数值是上下行结构（如示例），需要先拆分日期与数值列，可通过split或手动提取完成。

VBA 实现 #

步骤说明 #

1. 用字典存储每列的日期-数值键值对
2. 收集所有唯一日期并排序
3. 在新工作表中逐行写入对齐后的数据

代码实现 #

打开Excel，按Alt+F11进入VBA编辑器，插入新模块后粘贴以下代码：

Sub AlignPriceData()
    Dim wsSource As Worksheet, wsTarget As Worksheet
    Dim dictUC As Object, dictINT As Object, dictFTSE As Object
    Dim allDates As Collection
    Dim i As Integer, j As Integer
    Dim dateVal As String, priceVal As String
    
    ' 设置源工作表和目标工作表，替换为你的原始表名
    Set wsSource = ThisWorkbook.Sheets("Sheet1")
    Set wsTarget = ThisWorkbook.Sheets.Add
    wsTarget.Name = "AlignedData"
    
    ' 初始化字典和日期集合
    Set dictUC = CreateObject("Scripting.Dictionary")
    Set dictINT = CreateObject("Scripting.Dictionary")
    Set dictFTSE = CreateObject("Scripting.Dictionary")
    Set allDates = New Collection
    
    ' 读取Unicredit数据（假设日期在A列，数值在B列，从第2行开始）
    j = 2
    Do While wsSource.Cells(j, 1).Value <> ""
        dateVal = wsSource.Cells(j, 1).Value
        priceVal = wsSource.Cells(j, 2).Value
        dictUC(dateVal) = priceVal
        ' 去重添加日期到集合
        On Error Resume Next
        allDates.Add dateVal, Key:=dateVal
        On Error GoTo 0
        j = j + 1
    Loop
    
    ' 读取INTESA数据（假设日期在C列，数值在D列）
    j = 2
    Do While wsSource.Cells(j, 3).Value <> ""
        dateVal = wsSource.Cells(j, 3).Value
        priceVal = wsSource.Cells(j, 4).Value
        dictINT(dateVal) = priceVal
        On Error Resume Next
        allDates.Add dateVal, Key:=dateVal
        On Error GoTo 0
        j = j + 1
    Loop
    
    ' 读取FTSE MIB数据（假设日期在E列，数值在F列）
    j = 2
    Do While wsSource.Cells(j, 5).Value <> ""
        dateVal = wsSource.Cells(j, 5).Value
        priceVal = wsSource.Cells(j, 6).Value
        dictFTSE(dateVal) = priceVal
        On Error Resume Next
        allDates.Add dateVal, Key:=dateVal
        On Error GoTo 0
        j = j + 1
    Loop
    
    ' 日期降序排序
    Dim tempDate As String
    For i = 1 To allDates.Count - 1
        For j = i + 1 To allDates.Count
            If CDate(allDates(i)) < CDate(allDates(j)) Then
                tempDate = allDates(i)
                allDates.Remove i
                allDates.Add tempDate, Before:=j
            End If
        Next j
    Next i
    
    ' 写入表头
    wsTarget.Cells(1, 1).Value = "Date"
    wsTarget.Cells(1, 2).Value = "unicredit"
    wsTarget.Cells(1, 3).Value = "INTESA"
    wsTarget.Cells(1, 4).Value = "ftsi mib"
    
    ' 写入对齐后的数据
    For i = 1 To allDates.Count
        wsTarget.Cells(i + 1, 1).Value = allDates(i)
        ' 填充Unicredit数值
        If dictUC.Exists(allDates(i)) Then
            wsTarget.Cells(i + 1, 2).Value = dictUC(allDates(i))
        Else
            wsTarget.Cells(i + 1, 2).Value = ""
        End If
        ' 填充INTESA数值
        If dictINT.Exists(allDates(i)) Then
            wsTarget.Cells(i + 1, 3).Value = dictINT(allDates(i))
        Else
            wsTarget.Cells(i + 1, 3).Value = ""
        End If
        ' 填充FTSE MIB数值
        If dictFTSE.Exists(allDates(i)) Then
            wsTarget.Cells(i + 1, 4).Value = dictFTSE(allDates(i))
        Else
            wsTarget.Cells(i + 1, 4).Value = ""
        End If
    Next i
    
    ' 自动调整列宽
    wsTarget.Columns.AutoFit
End Sub

注意：根据你原始数据的列位置调整代码中的列号，若日期和数值为同一列上下行结构，需修改读取逻辑。

内容的提问来源于stack exchange，提问作者Luca