import openpyxl, json

path = '/tmp/RefSource_sync.xlsx'
wb = openpyxl.load_workbook(path, read_only=True, data_only=True)
print(wb.sheetnames)
ws = wb['AllFund']
data = {}
for row in ws.iter_rows(min_row=10, values_only=True):
    fund = row[1]
    debtor = row[5]
    amt = row[20]
    if not debtor or not fund:
        continue
    ds = str(debtor).strip()
    if ds in ("Debtor's name", '', '-'):
        continue
    k = ds.lower()
    f = str(fund).strip()
    if k not in data:
        data[k] = {}
    try:
        data[k][f] = data[k].get(f, 0.0) + float(amt or 0)
    except Exception:
        pass
wb.close()

companies = ['Lam Research', 'AstraZeneca', 'GE HealthCare', 'GEHC', 'Dover', 'Nike', 'Guardian Industries',
             'AbbVie', 'Nxera Pharma', 'NTT', 'Nippon Telegraph', 'Iberdrola', 'Intel', 'Amazon', 'Agilent',
             'FamilyMart', 'NVIDIA', 'Japan Radio', 'Panasonic', 'Adways', 'ITOCHU', 'Nisshinbo',
             'Siemens', 'Novo Nordisk', 'Samsung']

results = {}
for c in companies:
    key = c.strip().lower()
    funds = data.get(key)
    if not funds and len(key) >= 6:
        for k, v in data.items():
            if key[:8] in k or k[:8] in key:
                funds = v
                break
    results[c] = funds

print(json.dumps(results, indent=2, default=str))