[MAC] How to install wget

Link: http://osxdaily.com/2012/05/22/install-wget-mac-os-x/


Note:

1. Install Xcode v4.5.1
2. Launch Xcode: Preferences... => Downloads => Install Command Line Tools
3. Launch Terminal and enter the following commands:

   curl -O http://ftp.gnu.org/gnu/wget/wget-1.14.tar.gz

   ((Check http://ftp.gnu.org/gnu/wget/ for the latest version))
   

The rest are the same. Enjoy it.

生活的變化

我選擇別人選擇的生活。
我選擇別人選擇的工作。

我選擇別人選擇的社會。

我選擇別人選擇的自由。


當情勢無法改變，我選擇別人選擇的順民。

我
去你媽的情勢，
幹你娘的順民，
懶覺毛的社會，
覽趴火的工作，
吃大便的社會。


等著看！生活也是有變化的，因為那是我的自由。

聖稜線計畫 (2012-11-08 ~ 13)

D1 池有山登山口→三叉營地→池有山→新達山屋

D2 新達山屋→品田山→新達山屋→霸南山屋舊址營地

D3 霸南山屋舊址營地→大霸尖山→小霸尖山→大霸尖山→中霸尖山→伊澤山→中霸尖山→大霸尖山→霸南山屋舊址營地

D4 霸南山屋舊址營地→巴紗拉雲山→布秀蘭山→素密達山→素密達山屋

D5 素密達山屋→雪山北峰→雪北山屋→凱蘭特崑山→北稜角→翠池山屋

D6 翠池山屋→雪山主峰→三六九山莊→雪山東峰→七卡山莊→雪山登山口

[FWD] SQL Server: SQL Server Delays Demystified

Link: http://technet.microsoft.com/en-us/magazine/hh781189.aspx


Our second sample script using the sys.dm_os_wait_stats DMV will help determine the resources on which SQL Server is spending the most time waiting...

Inside Microsoft SQL Server 2008: T-SQL Querying P137 的 query 是

...

AND wait_type NOT LIKE N'%SLEEP%'

AND wait_type NOT LIKE N'%IDLE%'

AND wait_type NOT LIKE N'%QUEUE%'
...

整理一下：

WITH Waits AS 

( 

SELECT 

wait_type, 

wait_time_ms / 1000. AS wait_time_s, 

100. * wait_time_ms / SUM(wait_time_ms) OVER () AS pct,

ROW_NUMBER() OVER (ORDER BY wait_time_ms DESC) AS rn

FROM sys.dm_os_wait_stats 

WHERE wait_type NOT IN 

(

'CLR_SEMAPHORE', 

'SQLTRACE_BUFFER_FLUSH', 

'REQUEST_FOR_DEADLOCK_SEARCH', 

'XE_TIMER_EVENT', 

'BROKER_TO_FLUSH', 

'BROKER_TASK_STOP', 

'CLR_MANUAL_EVENT', 

'CLR_AUTO_EVENT', 

'XE_DISPATCHER_WAIT', 

'XE_DISPATCHER_JOIN'

    ) 

    AND wait_type NOT LIKE N'%SLEEP%'

AND wait_type NOT LIKE N'%IDLE%'

AND wait_type NOT LIKE N'%QUEUE%'

) 

SELECT 

W1.wait_type, 

CAST(W1.wait_time_s AS DECIMAL(12, 2)) AS wait_time_s, 

CAST(W1.pct AS DECIMAL(12, 2)) AS pct, 

CAST(SUM(W2.pct) AS DECIMAL(12, 2)) AS running_pct 

FROM Waits AS W1 

INNER JOIN Waits AS W2 

ON W2.rn <= W1.rn 

GROUP BY W1.rn, W1.wait_type, W1.wait_time_s, W1.pct

HAVING SUM(W2.pct) - W1.pct < 95 -- percentage threshold

OR W1.rn <= 5

ORDER BY W1.rn;


這個 query 跟計算累積營收很像，必須重複參考同個 table，接著用 W2.rn <= W1.rn 類似技巧整理出最後要的東西。

Argo (2012 film)

《大河灣》這樣描寫飛機，人已經到達目的地，但心還在原地。


每次出入境都有這種感覺，很緊張，離開馬來西亞讓我最緊張，害怕有壞人偷放毒品到我衣服口袋，馬來西亞運毒是唯一死刑，不是鬧著玩的。我知道那種屏息的感覺，喵的，就只能拼命裝龜孫子，祈禱不要被找到毒品。


記得把呼吸急促寫進去

[SQL] CCC研究

Definition: http://en.wikipedia.org/wiki/Cash_conversion_cycle

CCC = Inventory conversion period + Receivables conversion period - Payables conversion period

• Inventory conversion period = Avg. Inventory / (COGS / 365)
• Receivables conversion period = Avg. Accounts Receivable / (Credit Sales / 365)
• Payables conversion period = Avg. Accounts Payable / (Purchases / 365)

怎麼用 SQL 取出 Inventory conversion period 呢？

-- Average Inventory
select A.activity_date, avg(B.number) as avg_inventory from
(
    select max(report_date), activity_date, number from BalanceSheet
    where
        report_type = 'C'
        and stock_code = '1101'
        and item = '存 貨'
    group by activity_date
) as A,
(
    select max(report_date), activity_date, number from BalanceSheet
    where
        report_type = 'C'
        and stock_code = '1101'
        and item = '存 貨'
    group by activity_date
) as B
where B.activity_date <= A.activity_date
and julianday(A.activity_date) <= julianday(B.activity_date, '+3 month')
group by A.activity_date

-- COGS
select max(report_date), activity_date, number from IncomeStmt
where
    report_type = 'C'
    and stock_code = '1101'
    and item = '營業成本合計'
group by activity_date

-- Annual Adjusted COGS
select
    max(report_date),
    activity_date,
    case
        when strftime('%m', activity_date) = '03' then number * 4/1
        when strftime('%m', activity_date) = '06' then number * 4/2
        when strftime('%m', activity_date) = '09' then number * 4/3
        else number
    end as cogs
from IncomeStmt
where
    report_type = 'C'
    and stock_code = '1101'
    and item = '營業成本合計'
group by activity_date

-- Inventory conversion period (days inventory outstanding)
select activity_date, dio from
(
    select
        C.activity_date,
        C.avg_inventory / D.cogs * 365 as dio
    from
    (
        -- Average Inventory
        select A.activity_date, A.number, avg(B.number) as avg_inventory from
        (
            select max(report_date), activity_date, number from BalanceSheet
            where
                report_type = 'C'
                and stock_code = '1101'
                and item = '存 貨'
            group by activity_date
        ) as A,
        (
            select max(report_date), activity_date, number from BalanceSheet
            where
                report_type = 'C'
                and stock_code = '1101'
                and item = '存 貨'
            group by activity_date
        ) as B
        where B.activity_date <= A.activity_date
        and julianday(A.activity_date) <= julianday(B.activity_date, '+3 month')
        group by A.activity_date
    ) as C,
    (
        --COGS
        select
            max(report_date),
            activity_date,
            case
                when strftime('%m', activity_date) = '03' then number * 4/1
                when strftime('%m', activity_date) = '06' then number * 4/2
                when strftime('%m', activity_date) = '09' then number * 4/3
                else number
            end as cogs
        from IncomeStmt
        where
            report_type = 'C'
            and stock_code = '1101'
            and item = '營業成本合計'
        group by activity_date
    ) as D
    where C.activity_date = D.activity_date
)
where dio is not null
order by activity_date



怎麼用 SQL 取出 Receivables conversion period 呢？

-- Receivables
select activity_date, sum(number) as receivable from
(
    select activity_date, item, number, max(report_date) from BalanceSheet
    where
        report_type = 'C'
        and stock_code = '1101'
        and item like '%應收%'
    group by activity_date, item
)
group by activity_date

-- Average Receivables
select A.activity_date, avg(B.receivable) as avg_receivable from
(
    select activity_date, sum(number) as receivable from
    (
        select activity_date, item, number, max(report_date) from BalanceSheet
        where
            report_type = 'C'
            and stock_code = '1101'
            and item like '%應收%'
        group by activity_date, item
    )
    group by activity_date
) as A,
(
    select activity_date, sum(number) as receivable from
    (
        select activity_date, item, number, max(report_date) from BalanceSheet
        where
            report_type = 'C'
            and stock_code = '1101'
            and item like '%應收%'
        group by activity_date, item
    )
    group by activity_date
) as B
where B.activity_date <= A.activity_date
and julianday(A.activity_date) <= julianday(B.activity_date, '+3 month')
group by A.activity_date

-- Receivables conversion period
select activity_date, dso from
(
    select
        C.activity_date,
        C.avg_receivables / D.revenue * 365 as dso
    from
    (
        -- Average Receivables
        select A.activity_date, avg(B.receivables) as avg_receivables from
        (
            select activity_date, sum(number) as receivables from
            (
                select activity_date, item, number, max(report_date) from BalanceSheet
                where
                    report_type = 'C'
                    and stock_code = '1101'
                    and item like '%應收帳款%'
                group by activity_date, item
            )
            group by activity_date
        ) as A,
        (
            select activity_date, sum(number) as receivables from
            (
                select activity_date, item, number, max(report_date) from BalanceSheet
                where
                    report_type = 'C'
                    and stock_code = '1101'
                    and item like '%應收帳款%'
                group by activity_date, item
            )
            group by activity_date
        ) as B
        where B.activity_date <= A.activity_date
        and julianday(A.activity_date) <= julianday(B.activity_date, '+3 month')
        group by A.activity_date    
    ) as C,
    (
        -- Revenue
        select
            max(report_date),
            activity_date,
            case
                when strftime('%m', activity_date) = '03' then number * 4/1
                when strftime('%m', activity_date) = '06' then number * 4/2
                when strftime('%m', activity_date) = '09' then number * 4/3
                else number
            end as revenue
        from IncomeStmt
        where
            report_type = 'C'
            and stock_code = '1101'
            and item = '營業收入合計'
        group by activity_date
    ) as D
    where C.activity_date = D.activity_date
)
where dso is not null
order by activity_date



怎麼取出 Payables conversion period？

-- Average Payables
select A.activity_date, avg(B.receivable) as avg_receivable from
(
    select activity_date, sum(number) as receivable from
    (
        select activity_date, item, number, max(report_date) from BalanceSheet
        where
            report_type = 'C'
            and stock_code = '1101'
            and item like '%應付%'
        group by activity_date, item
    )
    group by activity_date
) as A,
(
    select activity_date, sum(number) as receivable from
    (
        select activity_date, item, number, max(report_date) from BalanceSheet
        where
            report_type = 'C'
            and stock_code = '1101'
            and item like '%應付%'
        group by activity_date, item
    )
    group by activity_date
) as B
where B.activity_date <= A.activity_date
and julianday(A.activity_date) <= julianday(B.activity_date, '+3 month')
group by A.activity_date

-- Payables conversion period
select activity_date, dpo from
(
    select
        C.activity_date,
        C.avg_payables / D.cogs * 365 as dpo
    from
    (
        -- Average Payables
        select A.activity_date, avg(B.payables) as avg_payables from
        (
            select activity_date, sum(number) as payables from
            (
                select activity_date, item, number, max(report_date) from BalanceSheet
                where
                    report_type = 'C'
                    and stock_code = '1101'
                    and item like '%應付帳款%'
                group by activity_date, item
            )
            group by activity_date
        ) as A,
        (
            select activity_date, sum(number) as payables from
            (
                select activity_date, item, number, max(report_date) from BalanceSheet
                where
                    report_type = 'C'
                    and stock_code = '1101'
                    and item like '%應付帳款%'
                group by activity_date, item
            )
            group by activity_date
        ) as B
        where B.activity_date <= A.activity_date
        and julianday(A.activity_date) <= julianday(B.activity_date, '+3 month')
        group by A.activity_date    
    ) as C,
    (
        --COGS
        select
            max(report_date),
            activity_date,
            case
                when strftime('%m', activity_date) = '03' then number * 4/1
                when strftime('%m', activity_date) = '06' then number * 4/2
                when strftime('%m', activity_date) = '09' then number * 4/3
                else number
            end as cogs
        from IncomeStmt
        where
            report_type = 'C'
            and stock_code = '1101'
            and item = '營業成本合計'
        group by activity_date
    ) as D
    where C.activity_date = D.activity_date
)
where dpo is not null
order by activity_date



Hence, CCC

select A.activity_date, A.dio + B.dso - C.dpo as ccc from
(
    select activity_date, dio from
    (
        select
            C.activity_date,
            C.avg_inventory / D.cogs * 365 as dio
        from
        (
            -- Average Inventory
            select A.activity_date, A.number, avg(B.number) as avg_inventory from
            (
                select max(report_date), activity_date, number from BalanceSheet
                where
                    report_type = 'C'
                    and stock_code = '1101'
                    and item = '存 貨'
                group by activity_date
            ) as A,
            (
                select max(report_date), activity_date, number from BalanceSheet
                where
                    report_type = 'C'
                    and stock_code = '1101'
                    and item = '存 貨'
                group by activity_date
            ) as B
            where B.activity_date <= A.activity_date
            and julianday(A.activity_date) <= julianday(B.activity_date, '+3 month')
            group by A.activity_date
        ) as C,
        (
            --COGS
            select
                max(report_date),
                activity_date,
                case
                    when strftime('%m', activity_date) = '03' then number * 4/1
                    when strftime('%m', activity_date) = '06' then number * 4/2
                    when strftime('%m', activity_date) = '09' then number * 4/3
                    else number
                end as cogs
            from IncomeStmt
            where
                report_type = 'C'
                and stock_code = '1101'
                and item = '營業成本合計'
            group by activity_date
        ) as D
        where C.activity_date = D.activity_date
    )
    where dio is not null
    order by activity_date
) as A,
(
    select activity_date, dso from
    (
        select
            C.activity_date,
            C.avg_receivables / D.revenue * 365 as dso
        from
        (
            -- Average Receivables
            select A.activity_date, avg(B.receivables) as avg_receivables from
            (
                select activity_date, sum(number) as receivables from
                (
                    select activity_date, item, number, max(report_date) from BalanceSheet
                    where
                        report_type = 'C'
                        and stock_code = '1101'
                        and item like '%應收帳款%'
                    group by activity_date, item
                )
                group by activity_date
            ) as A,
            (
                select activity_date, sum(number) as receivables from
                (
                    select activity_date, item, number, max(report_date) from BalanceSheet
                    where
                        report_type = 'C'
                        and stock_code = '1101'
                        and item like '%應收帳款%'
                    group by activity_date, item
                )
                group by activity_date
            ) as B
            where B.activity_date <= A.activity_date
            and julianday(A.activity_date) <= julianday(B.activity_date, '+3 month')
            group by A.activity_date     
        ) as C,
        (
            -- Revenue
            select
                max(report_date),
                activity_date,
                case
                    when strftime('%m', activity_date) = '03' then number * 4/1
                    when strftime('%m', activity_date) = '06' then number * 4/2
                    when strftime('%m', activity_date) = '09' then number * 4/3
                    else number
                end as revenue
            from IncomeStmt
            where
                report_type = 'C'
                and stock_code = '1101'
                and item = '營業收入合計'
            group by activity_date
        ) as D
        where C.activity_date = D.activity_date
    )
    where dso is not null
    order by activity_date
) as B,
(
    select activity_date, dpo from
    (
        select
            C.activity_date,
            C.avg_payables / D.cogs * 365 as dpo
        from
        (
            -- Average Payables
            select A.activity_date, avg(B.payables) as avg_payables from
            (
                select activity_date, sum(number) as payables from
                (
                    select activity_date, item, number, max(report_date) from BalanceSheet
                    where
                        report_type = 'C'
                        and stock_code = '1101'
                        and item like '%應付帳款%'
                    group by activity_date, item
                )
                group by activity_date
            ) as A,
            (
                select activity_date, sum(number) as payables from
                (
                    select activity_date, item, number, max(report_date) from BalanceSheet
                    where
                        report_type = 'C'
                        and stock_code = '1101'
                        and item like '%應付帳款%'
                    group by activity_date, item
                )
                group by activity_date
            ) as B
            where B.activity_date <= A.activity_date
            and julianday(A.activity_date) <= julianday(B.activity_date, '+3 month')
            group by A.activity_date     
        ) as C,
        (
            --COGS
            select
                max(report_date),
                activity_date,
                case
                    when strftime('%m', activity_date) = '03' then number * 4/1
                    when strftime('%m', activity_date) = '06' then number * 4/2
                    when strftime('%m', activity_date) = '09' then number * 4/3
                    else number
                end as cogs
            from IncomeStmt
            where
                report_type = 'C'
                and stock_code = '1101'
                and item = '營業成本合計'
            group by activity_date
        ) as D
        where C.activity_date = D.activity_date
    )
    where dpo is not null
    order by activity_date
) as C
where A.activity_date = B.activity_date
and B.activity_date = C.activity_date
and C.activity_date = A.activity_date

[SQL] 營業外收支研究

可能還要留意會計科目變化

select activity_date, non_operating_income_ratio from
(
    select activity_date, non_operating_income_ratio, max(report_date) from
    (
        select
            A.activity_date,
            A.number / B.number as non_operating_income_ratio,
            A.report_date
        from IncomeStmt as A
        inner join
        IncomeStmt as B
        on A.stock_code = B.stock_code
            and A.activity_date = B.activity_date
            and A.item in ('營業外收入合計', '營業外收入及利益')
            and B.item in ('繼續營業部門稅前淨利(淨損)', '繼續營業單位稅前淨利(淨損)')
            and A.report_type = 'C'
            and B.report_type = 'C'
            and A.stock_code = '2002'
    )
    where non_operating_income_ratio is not null
    group by activity_date
    order by activity_date
)

[SQL] 流動比率與速動比率研究

INNER JOIN 是好物！

select activity_date, current_ratio from
(
    select activity_date, current_ratio, max(report_date) from
    (
        select
        A.activity_date,
        A.number / B.number as current_ratio,
        A.report_date
        from BalanceSheet as A
        inner join
        BalanceSheet as B
        on A.stock_code = B.stock_code
        and A.activity_date = B.activity_date
        and A.item = '流動資產'
        and B.item = '流動負債'
        and A.report_type = 'C'
        and B.report_type = 'C'
        and A.stock_code = 1101
    )
    where current_ratio is not null
    group by activity_date
    order by activity_date
)



select activity_date, rapid_ratio from
(
    select activity_date, rapid_ratio, max(report_date) from
    (
        select
            C.activity_date,
            D.rapid / C.number as rapid_ratio,
            C.report_date
        from BalanceSheet as C,
        (
            select activity_date, rapid, max(report_date) from
            (
                select
                A.activity_date as activity_date,
                A.number - B.not_rapid as rapid,
                A.report_date as report_date
                from BalanceSheet as A,
                (
                    select activity_date, not_rapid from
                    (
                        select activity_date, not_rapid, max(report_date) from
                        (
                            select activity_date, report_date, sum(number) as not_rapid
                            from BalanceSheet
                            where report_type = 'C'
                                and stock_code = '1101'
                                and item in ('存 貨', '預付款項', '其他流動資產')
                            group by activity_date, report_date
                        )
                        group by activity_date
                    )
                ) as B
                where A.activity_date = B.activity_date
                and A.item = '流動資產'
                and A.report_type = 'C'
                and A.stock_code = 1101
            )
            group by activity_date
            order by activity_date
        ) as D
        where C.activity_date = D.activity_date
            and C.item = '流動負債'
            and C.report_type = 'C'
            and C.stock_code = 1101
    )
    group by activity_date
)

[SQL] 三大獲利能力研究

毛利率 (Gross Profit Margin) = 營業毛利(毛損) / 營業收入合計

select activity_date, gross_profit_margin from

(

    select activity_date, gross_profit_margin, max(report_date) from

    (

        select

        A.activity_date,

        A.number / B.number as gross_profit_margin,

        A.report_date

        from IncomeStmt as A

        inner join

        IncomeStmt as B

        on A.stock_code = B.stock_code

        and A.activity_date = B.activity_date

        and A.item = '營業毛利(毛損)'

        and B.item = '營業收入合計'

        and B.report_type = 'C'

        and B.report_type = 'C'

        and B.stock_code = '2498'

    )

    where gross_profit_margin is not null

    group by activity_date

    order by activity_date

)

營益率 (Operating Profit Margin) = 營業淨利(淨損) / 營業收入合計

select activity_date, operating_profit_margin from
(
    select activity_date, operating_profit_margin, max(report_date) from
    (
        select
        A.activity_date,
        A.number / B.number as operating_profit_margin,
        A.report_date
        from IncomeStmt as A
        inner join
        IncomeStmt as B
        on A.stock_code = B.stock_code
        and A.activity_date = B.activity_date
        and A.item = '營業淨利(淨損)'
        and B.item = '營業收入合計'
        and B.report_type = 'C'
        and B.report_type = 'C'
        and B.stock_code = '2498'
    )
    where operating_profit_margin is not null
    group by activity_date
    order by activity_date
)



稅後盈利率 (Net Profit Margin) = 本期淨利(淨損) / 營業收入合計

select activity_date, net_profit_margin from
(
    select activity_date, net_profit_margin, max(report_date) from
    (
        select
        A.activity_date,
        A.number / B.number as net_profit_margin,
        A.report_date
        from IncomeStmt as A
        inner join
        IncomeStmt as B
        on A.stock_code = B.stock_code
        and A.activity_date = B.activity_date
        and A.item = '本期淨利(淨損)'
        and B.item = '營業收入合計'
        and B.report_type = 'C'
        and B.report_type = 'C'
        and B.stock_code = '2498'
    )
    where net_profit_margin is not null
    group by activity_date
    order by activity_date
)



PS. hTC 獲利能力掉蠻快的

[SQL] 長期投資研究

2004年以前會計科目是「長期投資合計」，2005年之後用「基金及投資」


長期投資比率：

        select activity_date, long_term_investments_ratio from
        (
            select activity_date, long_term_investments_ratio, max(report_date) from
            (
                select
                L.activity_date,
                L.number / A.number as long_term_investments_ratio,
                L.report_date
                from BalanceSheet as L
                inner join
                BalanceSheet as A
                on L.stock_code = A.stock_code
                and L.activity_date = A.activity_date
                and L.item in ('長期投資合計', '基金及投資')
                and A.item = '資產總計'
                and L.report_type = 'C'
                and A.report_type = 'C'
                and L.stock_code = 1101
            )
            where long_term_investments_ratio is not null
            group by activity_date
            order by activity_date
        )

[SQL] 營收圖表研究

Source: http://mopsov.twse.com.tw/t21/sii/t21sc03_101_8.html


Example 1: 累計營業收入: 當月累計營收

select A.activity_date, A.income, sum(B.income) from
(
    select max(report_date), activity_date, income from OperatingIncome
    where stock_code = '1101'
    group by activity_date
) as A,
(
    select max(report_date), activity_date, income from OperatingIncome
    where stock_code = '1101'
    group by activity_date
) as B
where B.activity_date <= A.activity_date
and strftime('%Y', B.activity_date) = strftime('%Y', A.activity_date)
group by A.activity_date



Example 2: 營業收入: 去年同月增減(%)

select date, YoY from
(
    select
        B.activity_date as date,
        B.income / A.income - 1 as YoY,
        max(B.report_date)
    from OperatingIncome as A
    inner join
    OperatingIncome as B
    on A.stock_code = B.stock_code
        and strftime('%Y', B.activity_date) - strftime('%Y', A.activity_date) = 1
        and strftime('%m-%d', B.activity_date) = strftime('%m-%d', A.activity_date)
        and A.stock_code = '1101'
    group by B.activity_date
)



Example 3: 累計營業收入: 前期比較增減(%)

select
    D.date,
    D.accumlated / C.accumlated - 1 as YoY
from
(
    select A.activity_date as date, sum(B.income) as accumlated from
    (
        select max(report_date), activity_date, income from OperatingIncome
        where stock_code = '1101'
        group by activity_date
    ) as A,
    (
        select max(report_date), activity_date, income from OperatingIncome
        where stock_code = '1101'
        group by activity_date
    ) as B
    where B.activity_date <= A.activity_date
    and strftime('%Y', B.activity_date) = strftime('%Y', A.activity_date)
    group by A.activity_date
) as C
inner join
(
    select A.activity_date as date, sum(B.income) as accumlated from
    (
        select max(report_date), activity_date, income from OperatingIncome
        where stock_code = '1101'
        group by activity_date
    ) as A,
    (
        select max(report_date), activity_date, income from OperatingIncome
        where stock_code = '1101'
        group by activity_date
    ) as B
    where B.activity_date <= A.activity_date
    and strftime('%Y', B.activity_date) = strftime('%Y', A.activity_date)
    group by A.activity_date
) as D
on strftime('%Y', D.date) - strftime('%Y', C.date) = 1
and strftime('%m-%d', D.date) = strftime('%m-%d', C.date)

《大河灣》P53

關於人生。

我的人生目標到底是什麼？我的生存究竟有什麼意義？我開始覺得，不管在什麼地方過活，日子過得在好，錢賺得再多，生意做得再大，我也只是重複眼前的這種生活。

visual_manager.py

import logging
import sys

import src.jaguar_report.visual_indicator as indicator
import src.common.logger as logger

     
     
class VisualManager():

    def view_template(self, v, args):
        assert args.stock_code
        if args.log:
            v.save(args.stock_code, args.log)
        else:
            v.show(args.stock_code)
         
    def view_roe(self, args):
        self.view_template(indicator.VisualRoe(), args)

    def view_financial_structure(self, args):
        self.view_template(indicator.VisualFinancialStructure(), args)
         
    def view_current_and_quick_ratio(self, args):
        pass
         
    def view_cash_flow(self, args):
        self.view_template(indicator.VisualCashFlow(), args)
         
     
     
def main():
    logger.config_root(level=logging.DEBUG)
 
    import argparse
    parser = argparse.ArgumentParser()
    parser.add_argument('-t', '--target', help='set target: roe, \
            financial_structure, cash_flow')
    parser.add_argument('-s', '--stock_code', help='set stock code')
    parser.add_argument('-l', '--log', help='set log file to save visual chart')
    args = parser.parse_args()
 
    m = VisualManager()
    map = {
        'roe': m.view_roe,
        'financial_structure': m.view_financial_structure,
        'cash_flow': m.view_cash_flow,
    }
    assert args.target
    assert args.target in map
    map[args.target](args)

     
 
if __name__ == '__main__':
    sys.exit(main())

visual_indicator.py

import logging

from ..common import logger
from ..common import plot_util

class VisualBase():
    def __init__(self):
        self.LOGGER = logging.getLogger()
        self.__plot_util = plot_util.PlotUtil()

    def get_config(self, stock_code):
        pass
     
    def save(self, stock_code, dest_file):
        config = self.get_config(stock_code)
        try:
            self.__plot_util.save_plot(config, dest_file)
        except IndexError as e:
            self.LOGGER.error(e)

    def show(self, stock_code):
        config = self.get_config(stock_code)
        try:
            self.__plot_util.show_plot(config)
        except IndexError as e:
            self.LOGGER.error(e)

         

class VisualRoe(VisualBase):

    def get_config(self, stock_code):
        from ..stocktotal import roe_query
        q = roe_query.RoeQuery()
        return {
            'primary_data_list': [
                (q.query_roe(stock_code), 'ROE'),
                (q.query_net_profit_margin(stock_code), 'Net Profit Margin'),
                (q.query_total_assets_turnover(stock_code), 'Total Assets Turnover'),
            ],
            'secondary_data_list': [
                (q.query_equity_multiplier(stock_code), 'Equity Multiplier')
            ],
            'title': '''ROE Analysis: %s''' % stock_code,
            'percent_formatter': None,
        }
         


class VisualFinancialStructure(VisualBase):

    def get_config(self, stock_code):
        from ..stocktotal import financial_structure_query
        q = financial_structure_query.FinancialStructureQuery()
        return {
            'primary_data_list': [
                (q.query_equity_ratio(stock_code), 'Equity Ratio'),
                (q.query_debt_ratio(stock_code), 'Debt Ratio'),
            ],
            'secondary_data_list': [
                (q.query_equity_multiplier(stock_code), 'Equity Multiplier')
            ],
            'title': '''Financial Structure Analysis: %s''' % stock_code,
            'percent_formatter': None,
        }

         

class VisualCashFlow(VisualBase):

    def get_config(self, stock_code):
        from ..stocktotal import cash_flow_query
        q = cash_flow_query.CashFlowQuery()
        # TODO: operating activity, financing activity, investing activity,
        # total cash flow, operating activity - net income
        return {
            'primary_data_list': [
                (q.query_free_cash_flow(stock_code), 'Free Cash Flow'),
            ],
            'title': '''Free Cash Flow Analysis: %s''' % stock_code,
        }

plot_util.py

import numpy as np
import matplotlib.pyplot as plt
from matplotlib.ticker import FuncFormatter

def percent_formatter(x, pos=0):
    return '''%1.f %%''' % (100*x)

class PlotUtil():

    def __init__(self):
        self.COLOR_LIST = ['blue', 'red', 'green', 'cyan', 'magenta', 'black', 'yellow']

    def show_plot(self, config):
        figure, legend = self.plot(config)
        plt.show()
        plt.close(figure)
     
    def save_plot(self, config, dest_file):
        figure, legend = self.plot(config)
        figure.savefig(dest_file, bbox_extra_artists=(legend,), bbox_inches='tight')
        plt.close(figure)
     
    def plot(self, config):
        primary_data_list = config['primary_data_list']
        assert primary_data_list

        color_iter = iter(self.COLOR_LIST)
     
        figure = plt.figure()
        ax1 = figure.add_subplot(111)
     
        if 'percent_formatter' in config:
            ax1.yaxis.set_major_formatter(FuncFormatter(percent_formatter))

        lines, labels = [], []
        for data in primary_data_list:
            series = np.core.records.fromrecords(data[0], names='date,number')
            x_series = [_.toordinal() for _ in series.date]
            y_series = series.number
            ax1.plot_date(x=x_series, y=y_series, fmt='-', linewidth=2, \
                    color=next(color_iter), label=data[1])
        lines1, labels1 = ax1.get_legend_handles_labels()
        lines, labels = lines + lines1, labels + labels1

        if 'secondary_data_list' in config:
            secondary_data_list = config['secondary_data_list']
            assert secondary_data_list
            ax2 = ax1.twinx()
            for data in secondary_data_list:
                series = np.core.records.fromrecords(data[0], names='date,number')
                x_series = [_.toordinal() for _ in series.date]
                y_series = series.number
                ax2.plot_date(x=x_series, y=y_series, fmt='-', linewidth=2, \
                        color=next(color_iter), label=data[1])
            lines2, labels2 = ax2.get_legend_handles_labels()
            lines, labels = lines + lines2, labels + labels2

        legend = ax1.legend(lines, labels, loc='upper center', bbox_to_anchor=(0.5, -0.05), ncol=1)
         
        if 'title' in config:
            plt.title(config['title'])
        plt.grid(True)

        return figure, legend



cash_flow_query.py

from . import query

class CashFlowQuery(query.Query):

    def __init__(self):
        query.Query.__init__(self)

    def query_free_cash_flow(self, stock_code):
        SQL_SELECT = \
        '''
        select activity_date, sum(number) as free_cash_flow from
        (
            select *, max(report_date) from CashFlowStmt where
            report_type = 'C'
            and stock_code = ?
            and item in ('Operating', 'Investing')
            group by activity_date, item, stock_code
        )
        group by activity_date
        '''
        return self.exec_query_series(SQL_SELECT, stock_code)



roe_query.py

# coding: big5

from . import query

class RoeQuery(query.Query):

    def __init__(self):
        query.Query.__init__(self)

    """
    (annual adjusted) ROE
    = net income / shareholder equity
    = ( net income / operating income )
      x ( operating income / total assets )
      x ( total assets / shareholder equity )
    = ( net profit margin ) x ( total assets turnover ) x ( equity multiplier )

    Refernece: 郭恭克, 獵豹財務長投資魔法書 (ISBN：9789868340091)
    """
 
    def query_roe(self, stock_code):
        SQL_SELECT = \
        '''
        select activity_date,
        case
            when strftime('%m', activity_date) = '03' then roe * 4/1
            when strftime('%m', activity_date) = '06' then roe * 4/2
            when strftime('%m', activity_date) = '09' then roe * 4/3
            else roe
        end as annual_adjusted_roe
        from
        (
            select activity_date, roe, max(report_date) from
            (
                select
                E.activity_date,
                I.number / E.number as roe,
                E.report_date
                from BalanceSheet as E
                inner join
                IncomeStmt as I
                on E.stock_code = I.stock_code
                and E.activity_date = I.activity_date
                and E.item = '股東權益總計'
                and I.item = '合併總損益'
                and E.report_type = 'C'
                and I.report_type = 'C'
                and E.stock_code = ?
            )
            where roe is not null
            group by activity_date
            order by activity_date
        )
        '''
        return self.exec_query_series(SQL_SELECT, stock_code)

    """
    net profit margin = net income / operating income
    """
    def query_net_profit_margin(self, stock_code):
        SQL_SELECT = \
        '''
        select activity_date, net_profit_margin from
        (
            select activity_date, net_profit_margin, max(report_date) from
            (
                select
                I.activity_date,
                I.number / O.number as net_profit_margin,
                I.report_date
                from IncomeStmt as I
                inner join
                IncomeStmt as O
                on I.stock_code = O.stock_code
                and I.activity_date = O.activity_date
                and I.item = '合併總損益'
                and O.item = '營業收入合計'
                and I.report_type = 'C'
                and O.report_type = 'C'
                and I.stock_code = ?
            )
            where net_profit_margin is not null
            group by activity_date
            order by activity_date
        )
        '''
        return self.exec_query_series(SQL_SELECT, stock_code)

    """
    (annual adjusted) total assets turnover = operating income / total assets
    """
    def query_total_assets_turnover(self, stock_code):
        SQL_SELECT = \
        '''
        select activity_date,
        case
            when strftime('%m', activity_date) = '03' then total_assets_turnover * 4/1
            when strftime('%m', activity_date) = '06' then total_assets_turnover * 4/2
            when strftime('%m', activity_date) = '09' then total_assets_turnover * 4/3
            else total_assets_turnover
        end as annual_adjusted_total_assets_turnover
        from
        (
            select activity_date, total_assets_turnover, max(report_date) from
            (
                select
                A.activity_date,
                O.number / A.number as total_assets_turnover,
                A.report_date
                from BalanceSheet as A
                inner join
                IncomeStmt as O
                on A.stock_code = O.stock_code
                and A.activity_date = O.activity_date
                and A.item = '資產總計'
                and O.item = '營業收入合計'
                and A.report_type = 'C'
                and O.report_type = 'C'
                and A.stock_code = ?
            )
            where total_assets_turnover is not null
            group by activity_date
            order by activity_date
        )
        '''
        return self.exec_query_series(SQL_SELECT, stock_code)

    """
    equity multiplier = total assets / shareholder equity
    """
    def query_equity_multiplier(self, stock_code):
        SQL_SELECT = \
        '''
        select activity_date, equity_multiplier from
        (
            select activity_date, equity_multiplier, max(report_date) from
            (
                select
                E.activity_date,
                A.number / E.number as equity_multiplier,
                E.report_date
                from BalanceSheet as E
                inner join
                BalanceSheet as A
                on E.stock_code = A.stock_code
                and E.activity_date = A.activity_date
                and E.item = '股東權益總計'
                and A.item = '資產總計'
                and E.report_type = 'C'
                and A.report_type = 'C'
                and E.stock_code = ?
            )
            where equity_multiplier is not null
            group by activity_date
            order by activity_date
        )
        '''
        return self.exec_query_series(SQL_SELECT, stock_code)



financial_structure_query.py

# coding: big5

from . import query

class FinancialStructureQuery(query.Query):

    def __init__(self):
        query.Query.__init__(self)
 
    def query_equity_ratio(self, stock_code):
        SQL_SELECT = \
        '''
        select activity_date, equity_ratio from
        (
            select activity_date, equity_ratio, max(report_date) from
            (
                select
                E.activity_date,
                E.number / A.number as equity_ratio,
                E.report_date
                from BalanceSheet as E
                inner join
                BalanceSheet as A
                on E.stock_code = A.stock_code
                and E.activity_date = A.activity_date
                and E.item = '股東權益總計'
                and A.item = '資產總計'
                and E.report_type = 'C'
                and A.report_type = 'C'
                and E.stock_code = ?
            )
            where equity_ratio is not null
            group by activity_date
            order by activity_date
        )
        '''
        return self.exec_query_series(SQL_SELECT, stock_code)

    def query_debt_ratio(self, stock_code):
        SQL_SELECT = \
        '''
        select activity_date, debt_ratio from
        (
            select activity_date, debt_ratio, max(report_date) from
            (
                select
                D.activity_date,
                D.number / A.number as debt_ratio,
                D.report_date
                from BalanceSheet as D
                inner join
                BalanceSheet as A
                on D.stock_code = A.stock_code
                and D.activity_date = A.activity_date
                and D.item = '負債總計'
                and A.item = '資產總計'
                and D.report_type = 'C'
                and A.report_type = 'C'
                and D.stock_code = ?
            )
            where debt_ratio is not null
            group by activity_date
            order by activity_date
        )
        '''
        return self.exec_query_series(SQL_SELECT, stock_code)

    def query_equity_multiplier(self, stock_code):
        SQL_SELECT = \
        '''
        select activity_date, equity_multiplier from
        (
            select activity_date, equity_multiplier, max(report_date) from
            (
                select
                E.activity_date,
                A.number / E.number as equity_multiplier,
                E.report_date
                from BalanceSheet as E
                inner join
                BalanceSheet as A
                on E.stock_code = A.stock_code
                and E.activity_date = A.activity_date
                and E.item = '股東權益總計'
                and A.item = '資產總計'
                and E.report_type = 'C'
                and A.report_type = 'C'
                and E.stock_code = ?
            )
            where equity_multiplier is not null
            group by activity_date
            order by activity_date
        )
        '''
        return self.exec_query_series(SQL_SELECT, stock_code)



query.py

import os
import sqlite3

from datetime import datetime

class Query():

    def __init__(self):
        self.DB_FILE = './db/stocktotal.db'
        assert os.path.isfile(self.DB_FILE)
   
    def exec_query_series(self, sql_cmd, stock_code):
        conn = sqlite3.connect(self.DB_FILE)
        cursor = conn.cursor()
        cursor.execute(sql_cmd, [stock_code])
        rv = [(datetime.strptime(_[0], '%Y-%m-%d'), _[1]) for _ in cursor.fetchall()]
        cursor.close()
        conn.close()
        return rv
       
    def exec_query_stock_code(self, sql_cmd):
        conn = sqlite3.connect(self.DB_FILE)
        cursor = conn.cursor()
        cursor.execute(sql_cmd)
        rv = [_[0] for _ in cursor.fetchall()]
        cursor.close()
        conn.close()
        return rv       

Python 好用！numpy 及 matplotlib

廢話不多說，直接看 Operating Income Trend Chart Generator:

chart.py

import os
import numpy as np
import matplotlib.pyplot as plt

from ..stocktotal import stocktotal
from ..common import finance_util

class Chart():

    def generate_operating_income_chart(self, stock_code, dest_dir):
        s = stocktotal.Stocktotal()
        s_records = s.select_operating_income(stock_code)
        records = np.core.records.fromrecords(s_records, names='activity_date,income')

        dates = [_.toordinal() for _ in records.activity_date]
        incomes = records.income
        ma3 = finance_util.moving_average(incomes, 3)
        ma12 = finance_util.moving_average(incomes, 12)

        plt.clf()
        plt.plot_date(x=dates, y=incomes, fmt='-', color='gray')
        plt.plot_date(x=dates, y=ma3, fmt='-', color='blue', linewidth=3)
        plt.plot_date(x=dates, y=ma12, fmt='-', color='red', linewidth=6)
        plt.title('''Operating Income Trend: %s''' % stock_code)
        plt.ylabel('Operating Income')
        plt.grid(True)
        plt.savefig(os.path.join(dest_dir, '''%s.png''' % stock_code))

finance_util.py

import numpy as np

def moving_average(sequence, period):
    if len(sequence) < period:
        return sequence
    array = np.asarray(sequence)
    weights = np.ones(period)
    weights /= weights.sum()
    rv = np.convolve(array, weights, mode='full')[:len(array)]
    return rv

stocktotal.py

import os
import sqlite3

from datetime import date
from datetime import datetime

class Stocktotal():

    def __init__(self):
        self.DB_FILE = './db/stocktotal.db'
        assert os.path.isfile(self.DB_FILE)
 
    def select_operating_income_stock_code(self):
        SQL_SELECT = \
        '''
            SELECT code from StockCode where code in
            (
                SELECT distinct(stock_code) FROM OperatingIncome
            )
        '''
        conn = sqlite3.connect(self.DB_FILE)
        cursor = conn.cursor()
        cursor.execute(SQL_SELECT)
        rv = [_[0] for _ in cursor.fetchall()]
        cursor.close()
        conn.close()
        return rv
     
    def select_operating_income(self, stock_code):
        SQL_SELECT = \
        '''
            SELECT activity_date, income from
            (
                SELECT *, max(report_date) FROM OperatingIncome where stock_code = ?
                group by activity_date
                order by activity_date
            )
        '''
        conn = sqlite3.connect(self.DB_FILE)
        cursor = conn.cursor()
        cursor.execute(SQL_SELECT, [stock_code])
        rv = [(datetime.strptime(_[0], '%Y-%m-%d'), _[1]) for _ in cursor.fetchall()]
        cursor.close()
        conn.close()
        return rv

    def select_stock_code(self):
        SQL_SELECT = '''select code from StockCode'''
        conn = sqlite3.connect(self.DB_FILE)
        cursor = conn.cursor()
        cursor.execute(SQL_SELECT)
        rv = [_[0] for _ in cursor.fetchall()]
        cursor.close()
        conn.close()
        return rv