Aktieindex med dynamisk viktning

KursData

AntalAktier

Normalisering per år

Varje aktie börjar om på 100 första handelsdagen varje år oavsett kursnivå.

Dynamisk viktning

Kedjning

Formula Firewall

Uppstår när en fråga kombinerar Excel och Access direkt. Lösning: separata hjälpfrågor (fTickers, fKursdata) som inte laddas till Excel-bladet.

CInt() i Access SQL

Value.NativeQuery

Skickar SQL direkt till Access – drastiskt snabbare än Table.SelectRows på 400 000+ rader. Access datumfilter använder #datum# inte enkelfnuttar.

Dubbletter förstör viktningen

En dubblettrad ger dubbel market cap och felaktiga vikter. Kontrollera med:

SELECT Ticker, Datum, COUNT(*) FROM KursData
GROUP BY Ticker, Datum HAVING COUNT(*) > 1

Viktningens betydelse

Statiska 2025-vikter bakåt i tiden gav index ~1106. Dynamiska vikter gav ~709. Skillnaden är NVDA-effekten – statiska vikter överdrev NVDAs betydelse 2020 när kursen var $6.

let
    Kalla = Excel.CurrentWorkbook(){[Name="Tickers"]}[Content],
    Typad = Table.TransformColumnTypes(Kalla, {
        {"Ticker", type text},
        {"Bolag", type text},
        {"MarketCap", type number}
    })
in
    Typad

let
    Params     = Excel.CurrentWorkbook(){[Name="Parametrar"]}[Content],
    Startdatum = Table.SelectRows(Params, each [Etikett] = "Startdatum"){0}[Värde],
    Slutdatum  = Table.SelectRows(Params, each [Etikett] = "Slutdatum"){0}[Värde],
    StartText  = Date.ToText(DateTime.Date(Startdatum), "yyyy-MM-dd"),
    SlutText   = Date.ToText(DateTime.Date(Slutdatum),  "yyyy-MM-dd"),

    TickerList   = fTickers[Ticker],
    TickerFilter = "'" & Text.Combine(TickerList, "','") & "'",

    DB = Access.Database(
        File.Contents("C:\Users\joaci\Proton Drive\...\Aktiedata.accdb"),
        [CreateNavigationProperties=false]
    ),

    SQLKurs = "SELECT Ticker, Datum, Close FROM KursData
               WHERE Ticker IN (" & TickerFilter & ")
               AND Datum >= #" & StartText & "#
               AND Datum <= #" & SlutText & "#
               ORDER BY Datum, Ticker",
    Kursdata = Table.Buffer(Value.NativeQuery(DB, SQLKurs)),

    SQLAntal = "SELECT Ticker, Antal, Year(Datum) AS ar
                FROM AntalAktier
                WHERE Ticker IN (" & TickerFilter & ")
                ORDER BY Ticker",
    AntalAktier = Table.TransformColumnTypes(
        Table.Buffer(Value.NativeQuery(DB, SQLAntal)),
        {{"ar", type number}}
    ),

    SQLStart = "SELECT k.Ticker, CInt(Year(k.Datum)) AS ar, k.Close AS startpris
                FROM KursData k INNER JOIN (
                    SELECT Ticker, Year(Datum) AS ar, Min(Datum) AS forstadag
                    FROM KursData WHERE Ticker IN (" & TickerFilter & ")
                    AND Datum >= #" & StartText & "# AND Datum <= #" & SlutText & "#
                    GROUP BY Ticker, Year(Datum)
                ) f ON k.Ticker = f.Ticker AND Year(k.Datum) = f.ar AND k.Datum = f.forstadag",
    StartRensat = Table.Buffer(Value.NativeQuery(DB, SQLStart)),

    Resultat = [
        Kursdata    = Kursdata,
        AntalAktier = AntalAktier,
        StartRensat = StartRensat
    ]
in
    Resultat

let
    Kursdata    = fKursdata[Kursdata],
    AntalAktier = fKursdata[AntalAktier],
    StartRensat = fKursdata[StartRensat],

    MedAr        = Table.AddColumn(Kursdata, "ar", each Date.Year(DateTime.Date([Datum]))),
    MedAntal     = Table.NestedJoin(MedAr, {"Ticker", "ar"}, AntalAktier, {"Ticker", "ar"}, "antalrad", JoinKind.LeftOuter),
    MedAntalExp  = Table.ExpandTableColumn(MedAntal, "antalrad", {"Antal"}),

    MedMarketCap = Table.AddColumn(MedAntalExp, "marketcap", each
        if [Antal] = null then null else [Close] * [Antal]),

    TotalPerDag  = Table.Group(MedMarketCap, {"Datum"}, {{"total_mc", each List.Sum([marketcap]), type number}}),
    MedTotal     = Table.NestedJoin(MedMarketCap, "Datum", TotalPerDag, "Datum", "totalrad", JoinKind.LeftOuter),
    MedTotalExp  = Table.ExpandTableColumn(MedTotal, "totalrad", {"total_mc"}),

    MedVikt      = Table.AddColumn(MedTotalExp, "vikt", each
        if [total_mc] = null or [total_mc] = 0 then null else [marketcap] / [total_mc]),

    MedStartpris    = Table.NestedJoin(MedVikt, {"Ticker", "ar"}, StartRensat, {"Ticker", "ar"}, "startrad", JoinKind.LeftOuter),
    MedStartprisExp = Table.ExpandTableColumn(MedStartpris, "startrad", {"startpris"}),

    MedIndexkurs = Table.AddColumn(MedStartprisExp, "indexkurs", each
        if [startpris] = null or [startpris] = 0 then null
        else Number.Round([Close] / [startpris] * 100, 4)),

    MedBidrag    = Table.AddColumn(MedIndexkurs, "bidrag", each
        if [indexkurs] = null or [vikt] = null then null else [indexkurs] * [vikt]),

    IndexPerDag  = Table.Group(MedBidrag, {"Datum", "ar"}, {
        {"index_dag",    each List.Sum([bidrag]),  type number},
        {"antal_aktier", each Table.RowCount(_), type number}
    }),

    ArLista = List.Sort(List.Distinct(IndexPerDag[ar]), Order.Ascending),
    KedjaTabell = List.Accumulate(
        List.Skip(ArLista, 1),
        {[ar = ArLista{0}, faktor = 1.0]},
        (state, arval) =>
            let
                foregaendeAr     = List.Last(state)[ar],
                foregaendeFaktor = List.Last(state)[faktor],
                foregaendeRader  = Table.SelectRows(IndexPerDag, each [ar] = foregaendeAr),
                sistaDatum       = List.Max(List.Transform(foregaendeRader[Datum], each DateTime.Date(_))),
                sistaRad         = Table.SelectRows(foregaendeRader, each DateTime.Date([Datum]) = sistaDatum),
                sistaIndex       = sistaRad{0}[index_dag],
                detaArRader      = Table.SelectRows(IndexPerDag, each [ar] = arval),
                forstaDatum      = List.Min(List.Transform(detaArRader[Datum], each DateTime.Date(_))),
                forstaRad        = Table.SelectRows(detaArRader, each DateTime.Date([Datum]) = forstaDatum),
                forstaIndex      = forstaRad{0}[index_dag],
                nyFaktor         = foregaendeFaktor * sistaIndex / forstaIndex
            in
                state & {[ar = arval, faktor = nyFaktor]}
    ),
    KedjaTabellFinal = Table.FromRecords(KedjaTabell),

    MedFaktor    = Table.NestedJoin(IndexPerDag, "ar", KedjaTabellFinal, "ar", "faktorrad", JoinKind.LeftOuter),
    MedFaktorExp = Table.ExpandTableColumn(MedFaktor, "faktorrad", {"faktor"}),

    MedKedjatIndex = Table.AddColumn(MedFaktorExp, "index_kedjat", each
        if [index_dag] = null or [faktor] = null then null
        else Number.Round([index_dag] * [faktor], 2)),

    Slutlig  = Table.SelectColumns(MedKedjatIndex, {"Datum", "index_kedjat", "antal_aktier"}),
    Sorterad = Table.Sort(Slutlig, {{"Datum", Order.Ascending}}),
    Buffrad  = Table.Buffer(Sorterad)

in
    Buffrad