投資A3: 運用Excel進行股票投資組合回報最優化 Portfolio Optimizer in Excel

運用Excel進行股票投資組合回報最優化

Portfolio Optimizer in Excel

步驟1:輸入沒風險資產回報率, 例如政府債券每月利率/定期存款每月利率

Step 1: Input risk free rate such as monthly government bond rate/fixed deposit monthly interest rate

步驟2:計算股票 Apple, GE, Netflix (只是本文例子, 不是投資建議)預期回報 =average(click all Monthly return cells)

Step 2: Calculate Apple, GE, Netflix (examples only, not investment recommendation) expected returns =average(click all Monthly return cells)

步驟3:建立 variance and covariance matrix, weights 輸入dummy weights, 相同股票variance =var.s (click all Monthly return for apple); 不同股票covariance =covariance.s(all apple Monthly return cells, all GE Monthly return cells), 其他如此類推

Step 3: set up variance and covariance matrix, input dummy weights, same stocks with variance function, different stocks with covariance function.

步驟4:計算Contribution to Variance, use sumproduct function

Step 4: Calculate Contribution to Variance , use sumproduct function

步驟5:加總計算加權總數, 組合預期回報,組合標準差,expected sharpe ratio

step5:sum of weights, portfolio expected return, portfolio SD,expected sharpe ratio

步驟6:運用excel solver 讓excel 自動計算股票投資組合各股票回報最優化權重, 極大化expected sharpe ratio,by changing variable cells 基於局限條件:每個股票權重大個等於零, 加總權重等於1

Step 6: Apply excel solver let excel automatic calculate Portfolio Optimizer with weights , maximize sharpe ratio, by changing variable cells subject to the constraints: each stock weight more or equal to zero, sum of weight equals to one.

Final step: Solve

最後步驟: 解答

附下列一個視頻有示範運用Excel進行股票投資組合回報最優化

Attached a youtube video to demostrate Portfolio Optimizer in Excel

Portfolio Optimizer in Excel Excel股票投資組合回報最優化

投資A2: 股票投資組合設計 Investment Portfolio Design

上回說到如果儲了一筆初次投資資金約美元 USD$13,000。

In the last essay, we assume a person save USD$13,000 and begins his initial investment.

今天要介紹一個投資模型, 它英文名叫"Markowitz Portfolio Optimization Model"

Today I will introduce this "Markowitz Portfolio Optimization Model" to readers.

Markowitz Portfolio Optimization Model 提供了一個用數量化工具的投資模型, 它主要基於投資組合中不同股票的資產平均值回報, 投資組合中不同股票的資產的方差;透過數量法方法讓投資者去分配他的財務資源去設計出一個投資組合平衡風險及回報 。

Markowitz Portfolio Optimization Model provides a method to analyse how good a given portfolio is. It is based only on the means and the variance of the returns of the assets contained in the portfolio. It is a quantitative tool that allows an investor to allocate his resources by considering trade-off between risk and return.

步驟1:由財經網站下載經A1:初步篩選後的股票, 例如 APPLE, GE and NETFLIX (只是本文例子, 不是投資建議)過去5年每月底收市股價

Step 1: Download the data of shares like APPLE, GE and NETFLIX (examples only, not investment recommendation) from financial website with past 5 years monthly closing stock prices after A1 initial screening.

步驟2:計算每個股票每月回報(monthly return):即本月比上一個月股價增長-- [本月收市價減上月收市價]/上月收市價

Step 2: Calculate each stock monthly return by:-- (This month closing share price - previous month closing share price)/previous month closing share price

步驟3: 之後用excel function 計算每個股票平均回報[=AVERAGE], 方差[=VAR] 及標準差[=STDEV]

Step 3 ; Next, using excel function to calculate each stock average return[=AVERAGE], variance[=VAR] and standard[=STDEV] deviation.

步驟4: 之後在excel worksheet 每股票每月計算超額回報(Excess return) = Monthly return /stock average return

Step 4: Then, use excel worksheet to calculate monthly excess return of each stocks.

步驟5:計算股票APPLE, GE and NETFLIX 的Covariance [=COVAR(??:??,??:??)]

Step 5: Calculate stocks APPLE, GE and NETFLIX Covariance [=COVAR(??:??,??:??)]

步驟6:計算股票APPLE, GE and NETFLIX 的相關系數

Step 6: Calculate APPLE, GE and NETFLIX correlation

由於這分析需excel 輔助及概念比較深, 下一篇投資A3會把餘下步驟寫出及輔以excel solver 計算實例完成這個模型的介紹。

Due to this analysis need the assistance of excel and the concept is complicate, next essay Investment A3 will write out the rest of the steps with excel solver calculation to finish this model introduction.

港股恆指2021年9月中至10月初波浪分析

港股恆指本輪跌浪A浪回吐如下:

A浪下跌:18/2/21-27/7/21:恆指由31,183點下跌下跌至24,748點,共跌6,435點(A浪跌幅), 恆指週線圖27/7/21:RSI(14)跌至32.826,HSI PE大約10.90倍, A浪調整看來已經完成。

B浪如果21年7月27日至8月11日港股恆指展開反彈, 反彈幅度超過A浪跌幅6,435點的0.236倍[反彈1,519點]:恆指本輪B浪反彈波浪升幅幅度已經在11/8/21抵達26,822點, 反彈了2,074點(反彈了A浪跌幅的32.23%,介乎A浪跌幅0.236至0.382倍之間),已經完成此反彈目標;

C浪:恆指由21年8月11日開始進入C浪調整, 保守估計本跌浪是沉底格局;

15/9/2021 HSI 收市價: 25,033點, PE=10.32, PB=1.0513,RSI(14)=37.964;依此計算:HSI 1.0 PB=23,811點 [HSI PE=9.8］,恆指可能俟近23,800點附近才見本輪跌浪浪底 。

結論:港股本輪熊市調整浪未完,等C浪尾行完才會出現反彈浪。

Microsoft acquires start-up TakeLessons 微軟公司收購初創學習公司 TakeLessons

Microsoft has acquired TakeLessons, a start-up with a website where people book paid online and in-person classes on a variety of subjects, a Microsoft spokesperson confirmed Friday. Terms of the deal weren’t disclosed.

微軟公司收購初創學習公司 TakeLessons, TakeLessons是一個網站平台讓人們網上付款後可以學習不同科目內容, 微軟公司發言人已經證實有關收購, 收購條款沒有公開。

TakeLessons helps people sign up for online and offline courses on consumer-oriented topics, like playing musical instruments and speaking foreign languages.

TakeLessons有助人們在有關平台付款及登入後可以在線上線下學習客戶導師的課程/課題, 例如音樂, 樂器及學習外語。

Takelessons.com

投資A1: 初步篩選 Initial Screening

上文說到大約儲蓄了10萬港元(美元$12,857元,匯率 1USD$=7.7776HKD$)可作投資, 現在看一下正式建立個人投資組合之前需作初步篩選。

In previous Investmnet A0 essay, assume a person save HKD$100,000 (USD$12,857;exchange rate 1USD$=7.7776HKD$) and begin investment, now we discuss initial screening before investment.

他可以開設一試算表工作頁收集以下數據:-

He can open an excel worksheet to collect the following data:-

Business Sector商業板塊	Ticker 公司股票代號	PE 市盈率	PB 市帳率	Debt/Equity% 負債/權益資本比率	Beta 風險系數	ROE 資本報酬率	Market Cap 市值
stock1 股票1	??	??	??	??	??	??	??
stock2 股票2	??	??	??	??	??	??	??
stock3 股票3	??	??	??	??	??	??	??
stock4 股票4	??	??	??	??	??	??	??
stock5 股票5	??	??	??	??	??	??	??

以上數據可在Yahoo finance, bloomberg, investing.com, aastocks等財經網站採集。

The above data can be obtained from Yahoo finance, bloomberg, investing.com, aastocks finance websites.

完成以上初步收集數據用作初步篩選股票, 稍後另一篇文會採用試算表配以正規財務學方法進行一個投資組合設計。

After finishing the above data collection for stock initial screening, next essay will adopt an excel worksheet assist with formal finance method to build up an investment portfolio.

投資A0: Investment begineer

投資A0即沒有任何投資經驗的投資新手。

Investment beginner means anyone without investment experience.

由零開始, 那如何開始呢?

Start from zero, how to start then?

假設一位大學畢業生畢業後找了一份工作, 每月有HKD$13,300扣除退休用強積金收入, 每月可以用$60元一天,返工午餐及車費開支, 返22天工作, 每月支出$1,320; 手機月費費用:$100,家用:$1,500, 每月雜項費用支出:$380.即支出大約$3,300一個月; 餘下$10,000儲蓄起來, 儲蓄率便有75.19%。

Assumed a fresh graduate finds a job with monthly salary HKD$13,300 after MPF contribution, he can use HK$60 in daily lunch and travel fees with HKD$1,320 monthly expense. $100 mobile fee, $1,500 family expenses and $380 sundry expenses. He can save around HK$10,000 a month. Saving ratio is about 75.19%.

第一件事先於投資先做是先儲蓄。

The first thing to do before investment is saving.

如果他把首2個月儲蓄儲起,買入年息最少有2厘I bond, 每年會收到利息HKD$400, 之後10個月可以月供年股息率5厘或以上/20送1紅股股票/過去10年平均每年股價投資組合有5%增長或以上股票組合, 第一年便可以產生大約$5,400的被動收入。

If he save up the first two months saving and buy 2% annual interest return i bond, he can get HKD$400 interest income , and continue to save HK$10,000 in the next ten months and contribute into a 5% or above dividend yield /1 for 20 bonus shares /5% or more average growth stock portfolio in the past ten years, the first year can generate about HKD$5,400 passive income after the first year.

以上設計先有等同現金的通脹掛勾債券為基礎後才投入現金在有風險的股票投資組合, 起碼有一些應急用現金等價物。

The above design the begineer can have some cash equivalent i Bond as his foundation and input the following 10 months saving in a more risky stock portfolio, at least he can have some cash equivalents to meet the rainy days for urgent needs.

稍後文章會討論投資組合的設計。

Later essays will discuss the investment portfolio design.

港股恆指2021年9至10月初預測

港股恆指本輪跌浪A浪回吐如下:

A浪下跌:18/2/21-27/7/21:恆指由31,183點下跌下跌至24,748點,共跌6,435點(A浪跌幅), 恆指週線圖RSI(14)跌至32.826,HSI PE大約10.90倍, A浪調整看來已經完成。

B浪如果21年7月27日至8月11日港股恆指展開反彈, 反彈幅度超過A浪跌幅6,435點的0.236倍[反彈1,519點]:恆指本輪B浪反彈波浪升幅幅度已經在11/8/21抵達26,822點, 反彈了2,074點(反彈了A浪跌幅的32.23%,介乎A浪跌幅0.236至0.382倍之間),已經完成此反彈目標;

C浪:恆指由21年8月11日開始進入C浪調整, 最樂觀保守估計本跌浪是陰跌磨底格局;

C1浪(或Ca浪): 恆指由11/8/21的26,822點跌至20/8/21的 24,581點,跌了2,241點;

目前是C2浪(或Cb浪):由20/8/21:24,581點反彈至1/9/21:26,101點;反彈了1,520點(C1浪趺幅的67.8%)

1/9/21 恆指收市:26,028點, HSI PE=10.08倍, HSI PB=1.014倍

後市有這個可能性, 如果以a,b,c浪最樂觀跌浪方式跌完, 本輪C浪目標可能是 26,101點 - 2,241點= 23,860點; 到時 HSI PE=9.24倍; HSI PB=0.93倍

1.恆指後市(2021年9月至10月初)看淡原因係恆指目前已經出現死亡交叉, 目前SMA(50) 50天平均線在26,857點,已經跌穿SMA(250) 250天平均線在27,297點;當此死亡交叉出現便確定熊市。

2.另一個恆指後市看淡原因是港股恆指受制於保歷加通道20天平均線中軸, 未能升穿突破企穩中軸3天或以上,港股恆指RSI(14)=49.294,亦反映後市看淡。

結論:港股本輪熊市調整浪未完,等C浪尾行完才會出現反彈浪。