Jinsi ya kukokotoa dividend yield katika Google Sheets

Kukokotoa dividend yield katika Google Sheets

Ili kukokotoa dividend yield katika Google Sheets, ni lazima ujenge namba hiyo mwenyewe. =GOOGLEFINANCE("AAPL","yield") hurejesha bei, eps, pe, market cap na orodha ndefu ya nyanja nyingine za bei, lakini dividend yield si mojawapo: #N/A hurejesha price, na hakuna tahajia nyingine ya neno hilo inayofanya kazi. Mbinu mbili mbadala zinafaa. Kokotoa yield ya nyuma (trailing yield) kutoka kwenye safu ya dividend unayoiweka kwenye sheet yako, au andika kiwango cha mbele (forward rate) kilichotangazwa kwenye seli na ugawanye.

Kwa nini GOOGLEFINANCE haina sifa ya dividend yield

Sifa za utendaji huu ziko katika familia mbili. Kuna sehemu za bei za moja kwa moja (live quote), zinazopatikana kwa hoja mbili: price, volume, pe, eps, marketcap, high52, changepct na nyingine kumi na mbili hivi. Kisha kuna sehemu za bei za kihistoria, zinazopatikana kwa tarehe au masafa ya tarehe: open, high, low, close, volume. Rekodi za dividend hazipo katika familia yoyote kati ya hizo. Hakuna sifa inayorejesha malipo au historia ya malipo, jambo ambalo linafanya namba ya juu ya yield (numerator) itoke nje ya utendaji huu.

Dividend yield ni jumla ya dividends za kila mwaka kwa kila hisa ikigawanywa kwa bei ya kila hisa, ikionyeshwa kama asilimia. Nusu ya bei ya hesabu hiyo ni seli moja. Nusu ya dividend ndiyo inayohitaji kazi. Ikiwa uwiano huu wenyewe ni mpya kwako, kile ambacho dividend yield hupima kihalisi kinaelezea hilo kabla ya mbinu zozote za lahajedwali, na jinsi ya kukokotoa dividend yield inaelezea hesabu hiyo hatua kwa hatua.

Hizi hapa ni data ghafi: kila malipo ambayo mlipaji mmoja wa kaya alifanya kwa takriban miaka mitatu iliyopita. Kila mstari ni ex-dividend date, tarehe ambayo mnunuzi wa hisa hapokei malipo yanayokuja, ikifuatiwa na kiasi cha fedha kilicholipwa kwa kila hisa.

SELECT
    toString(ex_dividend_date)                     AS ex_date,
    formatDateTime(ex_dividend_date, '%b %e, %Y')  AS ex_date_label,
    round(toFloat64(payment), 4)                   AS cash_amount,
    round(toFloat64(payment) * 4, 4)               AS annualized_run_rate
FROM
(
    SELECT
        ex_dividend_date,
        max(cash_amount) AS payment
    FROM global_markets.stocks_dividends
    WHERE ticker = 'KO'
      AND ex_dividend_date >= today() - 1120
      AND ex_dividend_date <= today()
    GROUP BY ex_dividend_date
)
ORDER BY ex_dividend_date

Coca-Cola ililipa $0.46 kwa kila hisa mnamo Sep 14, 2023 na $0.53 mnamo Jun 15, 2026, ikiwa na malipo 12 katika kipindi hicho. Mstari wa pili kwenye chati ni kila malipo yakizidishwa kwa nne: kiwango cha kila mwaka kinachotarajiwa ikiwa malipo ya robo hiyo yangejirudia mwaka mzima. Hupanda mara moja kwa mwaka na kubaki thabiti katikati ya vipindi hivyo. Kupanda huko ndiko ambako yields nyingi za lahajedwali hukosewa. Rekodi kamili inapatikana katika historia ya dividend ya Coca-Cola.

Njia mbili za kupata dividend yield katika Google Sheets

Njia ya kwanza ni trailing yield inayotokana na safu yako mwenyewe. Weka ex-dividend dates kwenye safu A na kiasi cha fedha kwa kila hisa kwenye safu B, mstari mmoja kwa kila malipo, kisha:

• Jumla ya miezi kumi na miwili iliyopita: =SUMIFS($B$2:$B,$A$2:$A,">="&EDATE(TODAY(),-12),$A$2:$A,"<="&TODAY())
• Bei ya sasa: =GOOGLEFINANCE("KO","price")
• Trailing yield, huku seli ikiwa imepangiliwa kama asilimia: =SUMIFS($B$2:$B,$A$2:$A,">="&EDATE(TODAY(),-12),$A$2:$A,"<="&TODAY())/GOOGLEFINANCE("KO","price")
• Idadi ya malipo katika kipindi hicho, kama uhakiki: =COUNTIFS($A$2:$A,">="&EDATE(TODAY(),-12),$A$2:$A,"<="&TODAY())

EDATE(TODAY(),-12) ni siku ileile ya kalenda miezi kumi na miwili iliyopita, hivyo kipindi hicho hujisogeza mbele chenyewe. Pangilia seli ya yield kama asilimia badala ya kuzidisha kwa mia moja ndani ya fomula. Laha inayofanya yote mawili huonyesha asilimia mia mbili tisini kwa asilimia mbili nukta tisa.

Njia ya pili ni forward yield kutoka kwa kiwango kilichotangazwa. Weka kiwango cha hivi karibuni kilichotangazwa kwa kila hisa kwenye D2 na idadi ya malipo kwa mwaka kwenye E2:

• Forward yield: =D2*E2/GOOGLEFINANCE("KO","price")

Hakuna kinachofanya D2 kijiendeshe chenyewe. Bodi hutangaza kiwango, na wewe hukisoma kutoka kwenye tangazo na kukiandika. Hiyo ndiyo toleo la kweli la forward yield, na chaguo kati ya namba hizo mbili za juu ndilo somo zima la trailing dhidi ya forward dividend yield.

Vidokezo viwili vidogo kuhusu utendaji huu. Nukuu za bei huchelewa kwa hadi dakika ishirini, na muda wa kuchelewa kwa seli yoyote unaweza kusomeka kwa =GOOGLEFINANCE("KO","datadelay"). Na utafutaji wa kihistoria hurejesha safu ya mbili kwa mbili badala ya namba moja, hivyo iweke kwenye kanga: =INDEX(GOOGLEFINANCE("KO","close",DATE(2026,6,30)),2,2) hutoa bei ya kufunga pekee.

Hatua ya kuweka kwenye msingi wa mwaka, na kosa linalofichwa nayo

Mfumo ulioharibika zaidi katika jedwali la dividend ni ule unaozidisha malipo ya hivi karibuni mara nne. Hufanya kazi vizuri hadi pale kampuni inapoongeza malipo. Baada ya ongezeko, malipo matatu kati ya manne ndani ya mwaka uliopita yalifanywa kwa kiwango cha zamani, na kuzidisha malipo mapya mara nne kunazidisha kile ambacho mwanahisa alipokea kihalisi.

Jopo hapa chini linapima pengo hilo kwa walipaji wakubwa wanane: kile ambacho kila jina kililipa kwa kila hisa katika kipindi cha miezi kumi na miwili iliyopita, kando ya mara nne ya malipo yake ya hivi karibuni.

SELECT
    ticker,
    round(toFloat64(sum(cash_amount)), 4)                          AS paid_last_12m,
    round(toFloat64(argMax(cash_amount, ex_dividend_date)) * 4, 4) AS latest_x4,
    round((toFloat64(argMax(cash_amount, ex_dividend_date)) * 4
           / toFloat64(sum(cash_amount)) - 1) * 100, 2)            AS gap_pct,
    count()                                                        AS payment_count
FROM
(
    SELECT
        ticker,
        ex_dividend_date,
        max(cash_amount) AS cash_amount
    FROM global_markets.stocks_dividends
    WHERE ticker IN ('KO', 'JNJ', 'PG', 'AAPL', 'MSFT', 'CVX', 'ABBV', 'IBM')
      AND ex_dividend_date >  today() - 365
      AND ex_dividend_date <= today()
    GROUP BY ticker, ex_dividend_date
)
GROUP BY ticker
ORDER BY gap_pct DESC

Kwa kupangwa kulingana na pengo, JNJ inashika nafasi ya kwanza. Ilifanya malipo 4 yenye jumla ya $5.24 kwa kila hisa katika kipindi hicho, wakati mara nne ya malipo yake ya hivi karibuni inafikia $5.36, ikiwa ni 2.29% zaidi. Kwenye hisa inayotoa yield karibu na 3%, kosa la ukubwa huo hubadilisha takwimu iliyochapishwa kwa sehemu ya kumi ya asilimia au zaidi, ambayo inatosha kubadilisha mpangilio wa orodha iliyopangwa.

Safu ya idadi ya malipo ndiyo hoja ya pili ya kutumia SUMIFS badala ya kuzidisha. Dirisha la miezi kumi na miwili inayozunguka halishiki kila wakati malipo manne kamili ya kila robo mwaka. Tarehe za ex-dividend hubadilika kwa siku chache kila mwaka, na dirisha linaweza kunasa malipo matatu au matano. COUNTIFS inakuambia ni kipi kilichotokea kabla hujaamini jumla hiyo.

Dividends maalum si sehemu ya mapato ya kawaida

Kosa lingine la kawaida ni kuchukulia malipo ya mara moja kama sehemu ya ratiba ya kawaida. Kampuni inapotoa ziada ya fedha taslimu kupitia malipo moja makubwa, kanuni ya SUMIFS huiingiza kwenye jumla ya miezi kumi na miwili iliyopita, na seli ya yield hupanda ghafla. Baada ya miezi kumi na miwili, kiwango hicho hushuka kimya kimya.

Malipo haya si nadra. Jopo hili huhesabu kila malipo katika soko la Marekani ambayo kalenda ya dividend imeyawekea alama kama ya mara moja badala ya sehemu ya ratiba inayojirudia, mwezi kwa mwezi.

SELECT
    toString(month_start)                           AS month,
    formatDateTime(month_start, '%b %Y')            AS month_label,
    countIf(freq = '0')                             AS one_time_payments,
    round(countIf(freq = '0') / count() * 100, 2)   AS one_time_share_pct
FROM
(
    SELECT
        toStartOfMonth(ex_dividend_date)        AS month_start,
        ticker,
        ex_dividend_date,
        ifNull(toString(any(frequency)), 'na')  AS freq
    FROM global_markets.stocks_dividends
    WHERE ex_dividend_date >= toStartOfMonth(today() - 730)
      AND ex_dividend_date <  toStartOfMonth(today())
    GROUP BY month_start, ticker, ex_dividend_date
)
GROUP BY month_start
ORDER BY month

Katika Jul 2026, mwezi kamili wa hivi karibuni, 111 ya malipo yalikuwa na alama ya mara moja, sawa na 2.91% ya yote yaliyofika ex-dividend mwezi huo. Jumla ya miezi kumi na miwili inayotiririka itakutana na mojawapo ya malipo haya mapema au baadaye.

Suluhisho ni safu ya alama (flag column). Weka regular au special kwenye safu C kando ya kila malipo, kisha uiongeze kama kigezo:

• =SUMIFS($B$2:$B,$A$2:$A,">="&EDATE(TODAY(),-12),$A$2:$A,"<="&TODAY(),$C$2:$C,"regular")

Hifadhi safu za malipo maalum kwenye jedwali. Ni fedha halisi na ni lazima ziwepo kwenye rekodi ya kile kilichopokelewa. Hata hivyo, hazipaswi kuwemo kwenye namba inayokusudiwa kuonyesha kiwango cha mapato ya kudumu.

Matokeo ya mwisho ya seli

Yield ya nyuma (trailing yield) iliyoundwa kwa njia hii ina sehemu mbili zinazobadilika, na husogea kwa nyakati tofauti. Jumla ya dividend hubadilika mara chache kwa mwaka, pale malipo yanapoingia au kutoka kwenye kipindi husika au ongezeko linapotokea. Bei hubadilika kila kikao cha soko. Paneli iliyo hapa chini inaendesha hesabu iliyokamilika mwezi kwa mwezi kwa kipindi cha miaka miwili iliyopita kwa mlipaji yuleyule.

WITH
    month_close AS
    (
        SELECT
            toLastDayOfMonth(date) AS month_end,
            argMax(close, date)    AS close_px
        FROM global_markets.stocks_daily_aggs
        WHERE ticker = 'KO'
          AND date >= toStartOfMonth(today() - 730)
          AND date <  toStartOfMonth(today())
        GROUP BY month_end
    ),
    payouts AS
    (
        SELECT
            ex_dividend_date,
            max(cash_amount) AS cash_amount
        FROM global_markets.stocks_dividends
        WHERE ticker = 'KO'
          AND ex_dividend_date >= today() - 1160
          AND ex_dividend_date <= today()
        GROUP BY ex_dividend_date
    )
SELECT
    toString(month_end)                       AS month,
    formatDateTime(month_end, '%b %Y')        AS month_label,
    round(ttm, 4)                             AS ttm_dividends,
    round(ttm / close_px * 100, 2)            AS trailing_yield_pct
FROM
(
    SELECT
        m.month_end                                   AS month_end,
        toFloat64(any(m.close_px))                    AS close_px,
        toFloat64(sumIf(p.cash_amount,
            (p.ex_dividend_date >  subtractYears(m.month_end, 1))
            AND (p.ex_dividend_date <= m.month_end))) AS ttm
    FROM month_close AS m
    CROSS JOIN payouts AS p
    GROUP BY m.month_end
)
ORDER BY month

Mstari wa dividend hupanda kwa hatua, kisha hubaki tuli. Mstari wa yield husogea kila mwezi katika kipindi hicho hicho. Katika Aug 2024 jumla ya nyuma ilikuwa $1.89 kwa kila hisa dhidi ya yield ya 2.61%; kufikia Jul 2026 jumla ilikuwa $2.08 na yield ilisomeka 2.37%. Seli ya yield inayobadilika wakati hakuna aliyegusa safu ya dividend inafanya kile ambacho trailing yield hufanya: denominator imebadilika.

Ticker moja ikishafanya kazi, nakili kizuizi hicho chini kwa kila hisa unayoshikilia na uzingatie matokeo kwa thamani ya soko badala ya kupata wastani wa yields. Hatua hiyo ni portfolio weighted dividend yield.

Maswali Yanayoulizwa Mara kwa Mara

Je, GOOGLEFINANCE ina sifa ya dividend yield?

Hapana. Kitendaji hiki kinashughulikia nyanja za bei za moja kwa moja na bei za kihistoria, na hakuna hata moja kati ya hizo inayohusu dividend au yield. =GOOGLEFINANCE("KO","yield") inarejesha #N/A. Yield kwenye jedwali hujengwa kutokana na takwimu ya dividend unayotoa wewe na bei inayorejeshwa na kitendaji hicho.

Ninawezaje kukokotoa trailing dividend yield katika Google Sheets?

Weka tarehe za ex-dividend katika safu moja na kiasi cha fedha kwa kila hisa katika safu inayofuata, jumlisha miezi kumi na miwili iliyopita kwa kutumia =SUMIFS($B$2:$B,$A$2:$A,">="&EDATE(TODAY(),-12),$A$2:$A,"<="&TODAY()), gawanya jumla hiyo kwa =GOOGLEFINANCE("KO","price"), na ubadilishe umbizo la seli ya matokeo kuwa asilimia.

Kwa nini dividend yield yangu inatofautiana na ile iliyo kwenye ukurasa wa wakala wangu?

Mara nyingi tatizo ni namba ya juu (numerator). Ukurasa unaonukuu forward yield hufanya wastani wa mwaka mzima kwa kutumia kiwango cha sasa kilichotangazwa, wakati SUMIFS ya miezi kumi na miwili inarejesha takwimu ya trailing ambayo bado inajumuisha malipo yaliyofanywa kwa kiwango cha zamani. Dividend ya kipekee (special dividend) ndani ya kipindi hicho huongeza pengo hilo zaidi.

Ninawezaje kuondoa special dividend kwenye yield?

Ongeza safu inayotambulisha kila malipo kama ya kawaida au ya kipekee, kisha pitisha safu hiyo kama kigezo cha ziada katika SUMIFS. Malipo ya kipekee yanabaki kwenye jedwali lakini yanatengwa na yield.

Je, Google Sheets inaweza kuvuta historia ya dividend kiotomatiki?

Si kupitia GOOGLEFINANCE. Safu ya dividend hudumishwa kwa mkono au kubandikwa kutoka chanzo kinachochapisha rekodi za malipo. Hiyo ndiyo hatua moja ya mikono katika mbinu zote mbili kwenye ukurasa huu.

Kila paneli hapa inakuja na SQL iliyoizalisha, hivyo fungua moja ili kuona hasa jinsi jumla ilivyohesabiwa. Maswali hayo hayo yanaweza kuulizwa kwa Kiingereza rahisi kwenye terminal ya Strasmore.