動態股票K線圖----從M語言到DAX表達式

偶然見別人畫的股票K線圖,不禁見獵心喜,也來模仿一番.原圖是不能動的,一動MACD移動平滑趨勢線就沒有了.這是微軟excel的一個缺陷.

所以我想了一個辦法彌補這個缺陷.由於手頭沒有數據,開始實施網抓數據.

網址如下:獐子島(sz002069)歷史成交明細_新浪財經_新浪網

先構造如下一個表.表1

365行數據,正好抓一年的股票數據.為什麼不在power query里直接構造呢?這是因為這個表不光用來抓取數據,到時也是用來載入power pivot,做為裡面的一張緯度表使用的.而且最方便的,假如我想要的不是一年的數據,而是兩年數據,那把表格直接往下再拉三百多行就行了.將表1載入進power query.然後將如下代碼複製進去,

let源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content], 測試 = let 測試=(x)=>let 源 = Web.Page(Web.Contents("http://vip.stock.finance.sina.com.cn/quotes_service/view/vMS_tradehistory.php?symbol=sz002069&date="&x&"")){2}[Data]in 源in測試, 已添加自定義 = Table.AddColumn(源, "自定義", each 測試([文本日期])), #"展開的「自定義」" = Table.ExpandTableColumn(已添加自定義, "自定義", {"Column1", "Column2"}, {"自定義.Column1", "自定義.Column2"}), 刪除的列 = Table.RemoveColumns(#"展開的「自定義」",{"日期"}), 已透視列 = Table.Pivot(刪除的列, List.Distinct(刪除的列[自定義.Column1]), "自定義.Column1", "自定義.Column2"), 篩選的行 = Table.SelectRows(已透視列, each ([#"收盤價:"] <> "0.00")), 更改的類型 = Table.TransformColumnTypes(篩選的行,{{"文本日期", type date}, {"收盤價:", type number}, {"前收價:", type number}, {"開盤價:", type number}, {"最高價:", type number}, {"最低價:", type number}, {"成交量(手):", type number}, {"成交額(千元):", type number}, {"漲跌幅:", type number}})in 更改的類型

正好生成我們需要的一年數據.

當然可以多抓幾隻股票,不過用power query時間太慢了.一隻股票的數據勉強還在我忍受的時間範圍,出去上個廁所或吃包辣條,就數據抓完了.數據量小,用powerquery或VBA可以抓一抓,勝在屬於excel內置的功能,比較方便(不過VBA抓數據的速度又比powerquery快不少.).數據量大還是用python.

將抓來的數據還有表1載入進power pivot.設置如下圖所示:

注意關係之間的方向箭頭.緯度表屬於一端,事實表屬於多端.設置好後就是寫計算欄位.計算欄位與度量公式的區別就是,計算欄位寫在紅框框里,根據行上下文,生成許多行,而度量公式只有一行.先寫試題公式,收盤平均價:

收盤價平均:=AVERAGE([收盤價:])

接下來寫計算欄位,每過五天計算一次平均收盤價,與及每十天計算一次平均收盤價

排名=COUNTROWS (FILTER ("表1","表1"[文本日期]<=EARLIER("表1"[文本日期])))五天=CALCULATE(AVERAGE("表1"[文本日期]),FILTER("表1","表1"[排名]=(EARLIER([排名])-5)))五天移動=IF([五天]=BLANK(),BLANK(),CALCULATE([收盤價平均],FILTER("表1","表1"[文本日期]>=EARLIER("表1"[五天])&&"表1"[文本日期]<=EARLIER([文本日期]))))十天=CALCULATE(AVERAGE("表1"[文本日期]),FILTER("表1","表1"[排名]=(EARLIER([排名])-10)))=IF([十天]=BLANK(),BLANK(),CALCULATE([收盤價平均],FILTER("表1","表1"[文本日期]>=EARLIER("表1"[十天])&&"表1"[文本日期]<=EARLIER([文本日期]))))

需要說明的是,這兩條平均線從開始寫公式開始,就已經算好了,不是單純的在日期緯度上單純的向未來日期移動,而是已經考慮到了,股票如果沒有發生交易行為的日期,自動會忽略掉.這是非常精準的計算只發生交易的平均移動.

其次要申明的,在<微軟excel2013用powerpivot建立數據模型>第280頁里採用的公式是VALUES進行公式編寫,可能存在關係循環依賴的錯誤.

而使用AVERAGE或其它聚合函數進行構造,沒出問題.真是靈異現像.這個問題我也是反覆深思許久.讓我吃幾包辣條思索一下再做回答。

<微軟excel2013用powerpivot建立數據模型>這本書是國內關於DAX數據建模唯一的一本最具深度的書.值得反覆一讀再讀,如果你需要從DAX初級選手邁入中級甚至專家級的話.DAX數據建模的好處就是能使得excel處理幾百萬行的數據,建立商業數據分析模型.同樣在power bi里也能使用,在sql server里同樣能使用.在sql server里建立用DAX做出的表格模型,能構建立高達幾十億乃至上百億行的商業數據分析模型,恐怕這是SQL SERVER的趨勢.而MDX表達式無人問津,使用的人太少了,會越來越萎縮.

寫完公式,返回excel工作界面,生成兩張數據透視表.

由於直接用數據透視表作圖,會生成數據透視圖表.而透視圖表是沒有股票圖的,所以必須使用寫函數技巧了,做進名稱管理器里.一張用來做股價圖,一張用來做折線圖,兩張圖做好,X軸與Y軸刻度都設置成一模一樣的,兩張圖表互相對齊,互相吻合,就是一張帶MACD移動平均線的圖表了啦.如下圖所示

這樣就具有動態效果.這樣做確實挺麻煩的.不過有個好消息就是excel365居然改進了這一措施.也就是趨勢線居然也能動態移動了,隨著數據.

我一直不知道,搞得我用DAX去構造兩條平均移動線,後來使用了K線圖自帶的趨勢線,居然也能數據動態移動,真是意外的驚喜.呵呵.不過如果你使用的office2016及其它版本,恐怕還是不行.只有office365才可以.office365每年都會更新好幾次版本,增加新功能或彌補一些缺陷,所以我也不知道他幾時將趨勢線的缺陷彌補的.所以還是使用office365吧.以前一些技術隨著新版本就過時了.比如這種技術:

接下來再講使用excel 365的情況下,怎麼用一張圖表做動態帶有MACD移動線的K線圖.剛才說了,由於使用數據透視表的數據直接做圖,無法生成K線圖.所以得使用函數+名稱管理器構造.這裡為什麼不將數據透視錶轉化為OLAP數據集呢?

OLAP數據集確實好用,但行標籤日期的數據是往下延展的,一旦改成OLAP數據集,日期就無法往下延展了.如果日期不需要向下延展,那使用OLAP數據集是最好的選擇.因此接下來在這裡使用函數+名稱管理器巧妙的做出使用數據透視表裡數據做K線圖的思路.

如圖所示,利用函數offset+counta構造的函數組合具有往下延展的特性.對日期,開盤價,最高價,最低價,收盤價等都利用這個公式.公式如下:

=OFFSET(Sheet5!$C$3,1,,COUNTA(Sheet5!$C:$C)-1,)=OFFSET(Sheet5!$B$3,1,,COUNTA(Sheet5!$B:$B)-1,)=OFFSET(Sheet5!$F$3,1,,COUNTA(Sheet5!$F:$F)-1,)=OFFSET(Sheet5!$E$3,1,,COUNTA(Sheet5!$E:$E)-1,)=OFFSET(Sheet5!$D$3,1,,COUNTA(Sheet5!$D:$D)-1,)

公式寫好後,先選擇K線圖圖表類型,然後就可以填入進圖表公式里.

再插入

添加兩條移動平均線,一條設置為五天平均,一條設置為十天平均.

整個動態MACDE移動平均股份K線圖效果如下.用power pivot的好處就是可以在裡面放上好多支股票,切換不同的股票來觀察K線圖.只是這裡我只抓取了一隻股票.

EasyCharts團隊出品

帥的人都關注了EasyCharts團隊^..^~

QQ交流群：553270834

微信公眾號：EasyCharts