一個Excel公式搞定獨立性分析

不管是分析男女差異、調薪幅度跟績效有沒有關聯、不同話題引起的討論度是否不一樣，都可以利用Excel中的CHITEST函數來實現。 

舉例來說, 某家冰淇淋店想知道性別會不會影響對冰淇淋口味的選擇,

於是它們統計了最近1個月顧客的消費數據:

理論上, 如果男女對冰淇淋口味都沒什麼特別偏好的話,

消費數據應該是這樣子(兩種口味各50%):

因為在性別與冰淇淋口味間沒有關係的條件下,
理論購買人數的算法如下:

整個獨立性分析最複雜的計算就這樣子了,
計算完上述結果後CHITEST函式就可以登場了,

→ CHITEST(actual_range, expected_range) = χ2

CHITEST的輸入變數有兩個, 分別為:

     1. Actual Range : 實際上的分布

     2. Expected Range : 理論上的分布

回傳的值則是在1與2分布一樣的假設之下, 

有多少機率出現這樣子的分布, 也就是所謂的p value.

把圖一、圖二的分布代進CHITEST後我們得到2%的數字,

也就是說在這樣的分布下, 其實有2%的機率性別不影響冰淇淋口味的選擇.

一般我們把5%當作一個顯著與否的分界點,

The House Advantage(莊家優勢)的作者Jeffrey

也是以5%的機率門檻檢驗旗下的撲克算牌手有沒有黑吃黑的嫌疑,

在這樣的水準下我們可以說性別確實顯著影響冰淇淋的口味選擇.

動腦時間:

某公司將網頁改版後得到了新顧客數據,

舊數據與新數據分別如下, 在5%的門檻下, 

新版網頁的顧客購買率(購買人數/進站人數)究竟有沒有上升呢?

中信金特別股。 認股？ 不認股？

以目前(2017/10/13)國泰特、富邦特的股價做比較，認股中信特，掛牌後十天半月隨即賣掉是可以小賺一筆的。

最近國內銀行積極發行特別股以充實一級資本適足率，
中信金(2891)也不落人後(詳見新聞)：
https://udn.com/news/story/7239/2673750 


先附上幾檔特別股的比較表：

特別股發放股利的先決條件是公司有賺錢，
接著繳納稅捐、彌補先前虧損、提存盈餘公積、發放可累積特別股以前沒發放的股利後，
才可以發放股利給當期的特別股股東。

因此特別股股利佔公司平均稅後淨利的比率越低，特別股股東拿到股利的機率也就越高。

中信特的殖利率應該在國泰特、富邦特之間，

加上特別股股利負擔較兩者為輕，掛牌後股價應該也是伯仲之間，
以國泰特、富邦特、及台新特三檔特別股做預測的話，
中信特的價格應該會落在61.2 ～ 62.7元附近：

因此，
若有認股的權力或者抽籤抽到認股權，短線來說倒不失為掛牌後快速賣出的獲利機會，

長期投資的話還是要再三考量利率走向以免賺了利息卻賠了價差。

輕鬆用VBA做網路爬蟲（中）之 字串處理

字串處理(String Parsing)簡單說就是將一堆文字(包括符號、數字等)清理、整理後擷取我們需要的部份；以“輕鬆用VBA做網路爬蟲（上）” 最後的小挑戰做例子的話，可以(跟面試官)說我們是在開發處理玩股網報價的演算法。

開發一個網路爬蟲時，大概有八成以上的時間都在做字串處理，

在這篇文章中我會用網路爬蟲“上篇”中最後的小挑戰作為例子，

再用一個線上的字庫讓大家練習一下。

在上一篇文章中我們抓下來的原始資料長這樣：

200.32▲3.861.96%

108.47▲2.011.89%

134.03▲2.41.82%

130.71▲2.341.82%

113.09▲1.861.67%

82.59▲1.161.42%

208.67▲2.581.25%

160.88▲1.941.22%

484.47▲5.691.19%

380.09▲4.31.14%

263.25▲2.921.12%

首先，我們在被證明錯誤前先找找其中自以為的規律：「

1. 指數報價一定都到小數點後兩位；

2. 指數報價後會跟著上漲▲或下跌▼的符號，平盤時沒有符號(某天看見的)；

3. 當天漲跌(有負號)一定都到小數點兩位，平盤時為0(某天看見的)；

4. 不論當天漲跌(有負號)，漲跌幅一定到小數點後兩位(eg. x.xx%)。」

因此，萃取指數報價的方式就是：「

找到第一個"."的位置(假設為n)，然後抓第1位～第n+2位，

也就是 Left( 原始資料, inStr( 1, 原始資料, "." )+2 )。」

再來，萃取指數漲跌的方法稍微複雜點：「

【先看原始資料中是否有那兩個符號，有的話：「

首先，我們知道指數報價的結尾在n+2處，

因此我們新創一個從n+3開始的資料叫做"改動資料"，

改動資料= Right( 原始資料, (len( 原始資料) -(n+2)) )，

所以，我們一樣找改動資料中第一個"."的位置(假設為m)，然後抓第1位～第m+2位就行了，

也就是 Left( 改動資料, inStr( 1, 改動資料, "." )+2 )。」

如果找不到▲或▼的符號，那當日漲跌就是0了。】」

最後是當日漲跌幅：「

如果當日漲跌不為0，那麼改動資料的第m+3位～最後一位就是漲跌幅了，

也就是Right( 改動資料, (len( 改動資料) -(m+2)) )。

當日漲跌為0的話，漲跌幅自然也為0( 0.00%)了。」

========================================================

以下是另一個字串處理+網路爬蟲的例子

========================================================

這是一個古登堡計畫下的字庫網頁(A開頭)：
http://www.mso.anu.edu.au/~ralph/OPTED/v003/wb1913_a.html

我們要嘗試著計算出A開頭字庫中各種詞性(part of speech)佔的數目，

原始碼大致上長這樣子：「

<P><B>Abacinate</B> (<I>v. t.</I>) To blind by a red-hot metal plate held before the eyes.</P>

<P><B>Abacination</B> (<I>n.</I>) The act of abacinating.</P>

 <P><B>Abstaining</B> (<I>p. pr. & vb. n.</I>) of Abstain</P>  」

單字被<B>標籤包住、詞性則被<I>標籤包住，

除此之外一個單字可能會有多個詞性（在<I>標籤裡面被&符號分開）。

（註：實作後才發現有極少數的多詞性會被“ /” 以及“ ,” 符號分開）

爬蟲的前置除了網址不一樣外，其它的與上篇文章都一樣，

以下是抓取詞性的code：「

圖1:

圖2:

圖3:

理論上，運行完後我們得到的資料大概會長這樣(部份)：

Part of Speech	numbers
prep.	70
n.	5444
a.	2861
v.t.	1088
adv.	574
pl.	239
p.p.	317
imp.	287
p.pr.	285
vb.n.	285
v.	67
v.i.	278
n.pl.	171
superl.	10
i.	44
p.a.	7
a	2
interj.	15
p.p.Adored(/);p.pr.	1
a.superl.	2
Aprefix.	3
conj.	26

網路爬蟲中，字串處理的部份大致上就是這個樣子。

以下是範例檔案（附code）：

https://goo.gl/uyMn5l   」

字串處理是一門相當重要的功夫，

無論是做資料分析、語意分析、或甚至要暴力破解某個密碼都會用到它，

而有些問題只有在實作後才會被發現，

建議大家多多找出生活、工作中可以被自動化的工作，試著動手開發。

有任何問題歡迎來信或者留言，

如果想自動化一件工作卻毫無頭緒也可以來信我們討論看看，

IT'S   ALL   FREE !!

輕鬆用VBA做網路爬蟲（上）

網路爬蟲(Web Crawler)簡單來說就是自動化的從網路上抓取我們要的資料，通常需要懂一些基礎的HTML語言以及還不賴的字串處理能力。

因為大部份網路爬蟲的相關來信都是想知道怎麼抓類／個股資料，

因此在這一系列的教學當中，我會從0到1教各位怎麼用VBA打造自己的股票資訊更新程式。

先介紹一下HTML，它的全名是Hyper Text Markup Language，

(註:本人極度不擅長HTML，若文章有錯請不吝嚴厲的來信或是在下面留言)
中國的翻譯是超文本標記語言，

在接下來的課程中我們會學著怎麼找到我們要的標記，然後用程式抓下來。

以下是一些標記(markup// tag)的例子：

标签	描述
<!--...-->	定义注释。
<!DOCTYPE>	定义文档类型。
<a>	定义锚。
<abbr>	定义缩写。
<acronym>	定义只取首字母的缩写。
<address>	定义文档作者或拥有者的联系信息。
<applet>	不赞成使用。定义嵌入的 applet。

因為Yahoo股市實在是太主流了，我決定用玩股網當做本系列的例子。

這是我們的目標網址：

「 http://www.wantgoo.com/Stock/TWStock/Class?id=2 」

在本篇文章中，我們會抓下這個頁面的：類股名稱、指數、漲跌、以及漲跌幅。

首先我們進到上述的網址，花個幾分鐘看看我們要的資料長什麼樣子，

再花一點時間看看網頁原始碼(source code)長什麼樣子(點右鍵通常就看得到這個功能了)，
接著我們發現我們要的資料被一些東西包了起來，就像我的肝一樣。

==========================↓以玻璃作為例子↓==========================

<em class="qIdxName"><a href="/Stock/TWStock/ClassCont?id=^019">玻璃</a></em>
<p class="qIdx ig up">36.46<span class="qIdxChg">▲1.52<b>4.35%</b></span></p>

==========================↑以玻璃作為例子↑==========================

接下來很重要，我們要決定怎麼有效率的抽出(extract)我們要的資料，
以"玻璃"這個產業類別作為例子，
我們發現它被三個東西包住：<em>的標籤、qIdxName的類別名稱、以及<a>的標籤，
但是綜觀整個source code，我們發現<a>標籤包含了太多我們不要的資訊了，
相對的<em>這個標籤只用來包住產業別而已，qIdxName這個名稱也是，
二選一，<em>比較短我就選它了；

然後我們發現指數、漲跌、漲跌幅通通混在一起分不開，
還好<p>標籤在整個原始碼當中幾乎只被用來記錄這些資訊，
所以我們就把這串東西一次抽出，之後再想辦法分開吧。

接下來該VBA上場了。

(註：本文最下方會附上範例資料)

設定完開發環境後，以下是加上附註的程式碼。
(不知道怎麼設定的朋友請看一下前文或是下載我的講義 https://goo.gl/9qtTiv)

圖片1：

圖片2：

運行以上的程式碼後，我們應該可以在excel上看到下面的結果：
(如果沒有的話，建議objIE.visible改成True，看過程中是不是IE有什麼問題)
圖片3：

為了拆開後面那一串東西，這裡介紹五個函式：『

第一個是inStr；inStr(起始位置，母字串，要找的字)；
inStr(1, "My name is Tamio", "a")會產出5(空格也算一個字)。

第二個是inStrRev；inStrRev(母字串，要找的字)；
inStrRev( "My name is Tamio", "a")會產出13(找後面數回去的第一個a)。

第三個是Left；Left(母字串，字數)；
Left( "My name is Tamio", 6)會產出"My nam"。

第四個是Right；Right(母字串，字數)；
Right( "My name is Tamio", 7)會產出"s Tamio"。

第五個是Mid；Mid(母字串，起始點，長度)；
Mid( "My name is Tamio", 5, 5)會產出" ame i"。』

至於實務上要怎麼拆開那串東西，就留給大家做做看吧！
(文末我的範例檔案裡面有解法)
兩個提示：
1. 漲跌幅的格式幾乎都是x.xx%
2. 除了當日漲跌為0以外，都會有▲或是▼的符號，而指數一定落在這些符號之前


完成後應該長這樣子，
圖片4：

在下一篇文章中，
我會說明怎麼用VBA“點擊”產業類別的連結，
然後把裡面的資訊分門別類儲存起來。
圖片5：

以下附上我的範例檔案：
「 https://goo.gl/D112gl 」

如果有問題歡迎留言或是在FB上msg我。



P.S. 搞不懂什麼時候要加Set、記不起來那麼多函式、
老是要google才知道一個功能該怎麼實現都是正常的，
寫這篇文章我也google的要死要活，請大家不要沮喪氣餒，
寫久了就會多記得一點點。

P.S.S. 寫這篇文章我參考了不少這系列影片的內容：
「 https://goo.gl/n4SeFS 」，英文不太好的人可以看看練習一下。

P.S.S.S. 我花了很多時間還是沒辦法把文章中的所有圖片大小變得一致，
不曉得跟陸客不來有沒有關係，請大家多多海涵～

輕鬆用Excel函式做蒙地卡羅分析（上）

蒙地卡羅分析又被稱作統計模擬方法，是一種利用電腦強大計算能力產生大量模擬資料以供分析的方法。

但是在什麼情況之下會用到它呢？

下面先來個不會用到它的簡單例子（先備知識），
【假設長期聘請顧問給予公司經營上的建議，
據信可以讓每個月的利潤較聘請顧問前增加4%～8%。】
某公司目前每個月的利潤是500萬元，
(顧問費用/每月)應該要控制在多少以下，才能確信這八成是個賺錢的交易？

這樣的問題不需要用到蒙地卡羅分析，我們來小試身手：
『Step1:
每個月利潤增長率為4%～8%(換算利潤為20萬~40萬)，
我們可以簡單估算出增長率的標準差大約為1.2%(換算利潤為60,790元)，
(常態分佈中，數值有90%的機會坐落在±1.645個標準差內，
故對於大部份的資料區間，我們可以直接除以(1.645*2)來得到估計的標準差。)

Step2:
接下來我們要找出下圖的X點（"八成"是個賺錢的交易），

Excel中有個函式可以解決這個問題，
叫做NORM.INV(累積機率, 平均值, 標準差)，
它可以給出在特定累積機率下，常態分佈曲線上的值，
在這個例子上即是NORM.INV(20%, 300000, 60790)，
可以得出248838元這個數字，
也就是說聘請顧問後所增加的利潤有80%以上的機率大於248,838元，
因此若能控制顧問成本在一個月248,838元以下，則這八成就是筆賺錢的交易了。』

注：
與NORM.INV成雙成對的是NORM.DIST函數，
詳細為NORM.DIST(某值, 平均值, 標準差, 是否為累積機率)，
它會給出某值在常態分配中的累積機率。
舉例來說：
『NORM.DIST(248838 ,300000 ,60790 ,TRUE)的值就是近似於20%了』

下一篇文章中，我們會討論更多相關的函數，
以及它們在蒙地卡羅分析法中的應用。

用簡單的函式做出排行榜

開始實作前先介紹幾個會用到&常用到的函式。

SMALL函式簡介
SMALL函式的架構為: SMALL(要尋找的範圍，排名)，
舉例來說，SMALL(A1:C3,3)這個函數會呈現A1到C3當中排名第3小的數字。

LARGE函式簡介
LARGE函式的架構為: LARGE(要尋找的範圍，排名)，
舉例來說，LARGE(A1:C3,1)這個函數會呈現A1到C3當中最大的數字。

OFFSET函式簡介
OFFSET函式的架構為: OFFSET(標的欄位，欄數，列數)，
OFFSET(H6, 3, -2)這個函數會show出位於H6下面3欄、左邊2列的格子，也就是F9。

MATCH函式簡介
MATCH函式的架構為: MATCH(目標的值，要尋找的欄或列，搜尋方式)，
若搜尋方式打0的話，該函式會show出目標值位於要尋找的"欄"或"列"中的第幾個位置。

範例實作

上圖左側是台灣上市櫃公司7/4的部份資料，

只要每次有所更新，透過函式，右側的表格也會連動的呈現出最新的資訊，

這是Excel函式簡單又有效率的地方。

以找出外資當日買超張數最多的股票股號為例，邏輯如下:『

使用LARGE
我們先找出C列中外資買超最多的數量為何，

→LARGE(C:C,G1)，

之所以在排名當中輸入G1的原因，

是做好了這個函式後我們只要順著拉下來就會跑出排名1-10的張數了。

使用MATCH
接下來我們要找這個最大值位於C列的第幾個欄位上，

→MATCH(LARGE(C:C,G1),C:C,0)，在這個例子中是660。

使用OFFSET
已經知道是第幾個欄位了，

剩下我們只要用OFFSET往左邊推幾格，就會知道買超排名第一的股票股號了，

→OFFSET(C$1, MATCH(LARGE(C:C,G1),C:C,0)-1, -2)

這裡稍微複雜些，我們先講為什麼要C1要變成C$1，

這是因為之後我們把函式往下拉的時候，C1會隨著我們啦下去而變成C2、C3，

寫成C$1之後它就會固定在C1的欄位上不動如山~

而MATCH(LARGE(C:C,G1),C:C,0)之所以要減掉1的原因是，

我們已經知道C欄中最大的數字位於第660個欄位上，

因此從C1算下去，欄位660是數下來的第659個欄位，因此要減掉1，

OFFSET當中最後的-2，就是股號相對於C列上的位置囉（往左邊2列）。』

所以..OFFSET(C$1, MATCH(LARGE(C:C,G1),C:C,0)-1, -2)這串式子，

在這個例子中會跑出3231的股號，

如果要跑出股票名稱，改成OFFSET(C$1, MATCH(LARGE(C:C,G1),C:C,0)-1, -1)即可，

剩下的2-10名只要把最上面的函式拉下去就可以了。

範例檔案

現在你就擁有一個可以自動化更新的排行表了~

該範例我上傳至: https://goo.gl/I7d56p

有興趣的朋友可以自行下載試做，

如果有問題也歡迎提出來討論！