[備忘] 創建 Excel 動態圖表的步驟

何謂動態圖表 (Dynamic Chart)?

亦即所接收的數據是會持續成長的。例如每分鐘 (或每30秒, etc...)接收即時的台灣加權成交價/量 數據。

創建動態圖表的重點:
  1. 利用「名稱管理員 (Name Manager)」創建具名 (named)可擷取動態資料範圍 (range)的公式 (formula)。
  2. 新增圖表,指定類型與相關屬性,並指定資料來源 (Data Sources)。
一、利用名稱管理員創建具名的公式
  1. (Option) 下載並安裝更好用免費的 NameManager Add-in (原來 Excel 內建的難用)。
     o Name Manager: Range names made easy

    Excel 2010 安裝 3rd party Name Manager

  2. Excel (2010)選單,[公式] → [Name Manager] 啟動。
  3. 測試資料欄位:

    Excel 盤中接收台指期即時數據

  4. Add a Name:VertValue,取得從第一行 (時間欄位)有含資料列的總列數 (row number),公式如下:
     > VertValue = OFFSET(即時資料!$A$2,1,0,COUNT(即時資料!$A:$A),1)
  5. Add a Name:TxfPrice (台指期成交價),取得含台指期成交價欄位的所有資料列。
     > TxtPrice = OFFSET(VertValue,0,2)
  6. Add a Name:TxfBidSpread (委買賣口數價差),取得含台指期委買賣口數價差欄位的所有資料列。
     > TxfBidSpread = OFFSET(VertValue,0,5)
  7. 如何測試輸入的公式是否正確?
    雙點擊 (安裝上述的 Add-in 才有此功能)欲檢查的名稱,並從 Excel 工作表內可看到所圈選資料列的範圍。

    Excel 設定 Name 擷取資料數列

二、新增圖表,指定資料來源
  1. Excel 選單,選擇 [插入] → 圖表區中選擇任一圖形 (後續均可再更改),在此選擇 [折線圖]。
  2. 點選所新增的圖表,在選單中會出現 [圖表工具],點選 [設計] → [選取資料]。 (或於圖表內,滑鼠右鍵點選亦可。)
  3. 在出現的 [選擇資料來源] 對話框中,於左側 [圖例數目 (數列)] 新增欲在 Y (垂直)軸顯示的數列 (SERIES):
     > 數列名稱:台指期價格  數列值: =即時資料!TxtPrice
     > 數列名稱:委買賣口數差  數列值: =即時資料!TxtPrice
    PS. 其中,"即時資料" 為工作表 (Worksheet)名稱,一般預設為 Sheet1, Sheet2, ...,可更改名稱。
  4. 在右側的 [水平(類別)坐標軸標籤],亦即 X (水平)軸,對剛新增的每一個數列,編輯標籤值範圍:
     > = 即時資料!Vertvalues
    PS. 兩個數列的標籤值均一樣,但仍需個別編輯。
  5. 編輯後的初始圖表與 [選擇資料來源] 所新增的數列圖例如下:

    Excel 數列(SERIES)設定

  6. 編輯圖表內容的屬性,包括座標軸與數列等 (雙點擊各圖形元件編輯):
    • 左方的價格坐標軸,修改 最小/最大, 主要/次要 刻度為固定,並給予適合資料顯示的數據範圍。 (例如台指期價格設定為 7000~7200,可包含所記錄的數據。)
    • 下方的時間坐標軸,修改 指定間隔的刻度間距 (如設為 60),使之座標值數據不致繁多緊密;同時可設定數值格式,如設定只顯示至分 (hh:mm)。
    • 點選圖表內 [圖例項目] → [委買賣口數差],也就是準備編輯 [委買賣口數差] 數列的屬性。
       o 右鍵選擇 [資料數列格式],出現對話框,主要的設定要更改該數列資料的繪製於 [副坐標軸]。
       o 右鍵選擇 [變更數列圖表類型],出現對話框,在此選擇設定為直條圖。
    • 可針對各數列更改顯示顏色。

    Excel 圖表屬性設定

設定完成後的圖表如下。

Excel 台指期委買賣口數價差圖

後續待克服問題:

  • 利用 VBA 程式撰寫可於動態期間更改圖表屬性,例如可依據盤中最高/最低價,設定台指期價格上下的數值範圍。
  • 可依據數列的正負值,而顯示不同的顏色。

文章導覽

   

共有 17 則迴響

  1. 請問你那邊有付費教課的嗎?有沒有連絡的住址和電話?
    2.Excel (2010)選單,[公式] → [Name Manager] 啟動。那個部份我看不懂,因為我啟動了[Name Manager] 並未出現一整列(測試資料欄位:)請問你有這一方面的教學嗎?我願意付費學習。

  2. 可以用下式取代.
    其實區間低價,區間高價也可在VBA中直接定義.

    .MinimumScale = Range(“區間低價”)
    .MaximumScale = Range(“區間高價”)

      • 測試結果:如在VBA中直接命名名稱及參考公式,應該如下使用才行,
        其中YH,YL是已定義完成的動態範圍.

        ActiveWorkbook.Names.Add Name:=”區間高價”, RefersTo:=”=ROUND(MAX(YH)*1.05/10,1)*10″
        ActiveWorkbook.Names.Add Name:=”區間低價”, RefersTo:=”=ROUND(MIN(YL)*0.95/10,1)*10″
        ‘ K線圖

        ActiveSheet.ChartObjects(“圖表 3”).Activate
        ‘ ActiveChart.ChartArea.Select
        ActiveChart.Axes(xlValue).Select
        With ActiveChart.Axes(xlValue)
        .MinimumScale = Evaluate(“區間低價”)
        .MaximumScale = Evaluate(“區間高價”)
        .MinorUnitIsAuto = True
        .MajorUnitIsAuto = True
        .Crosses = xlAutomatic
        .ReversePlotOrder = False
        .ScaleType = xlLinear
        .DisplayUnit = xlNone
        End With

  3. 設定台指期價格上下的數值範圍。
    我的作法:
    在某兩儲存格儲存範圍最大和最小值再加百分比空間,如
    [G19]->=ROUND(MAX(YH)*1.05/10,1)*10 ‘取整數
    [G20]->=ROUND(MIN(YL)*0.95/10,1)*10
    當然YH,YL名稱要先定義動態範圍,Y軸高,Y軸低要動態自動抓出來.
    然後在VBA裡如下,即成.
    ActiveSheet.ChartObjects(“圖表 3”).Activate
    ActiveChart.Axes(xlValue).Select
    With ActiveChart.Axes(xlValue)
    .MinimumScale = Sheets(“圖表”).Range(“G20”).Value
    .MaximumScale = Sheets(“圖表”).Range(“G19”).Value
    .MinorUnitIsAuto = True
    .MajorUnitIsAuto = True
    .Crosses = xlAutomatic
    .ReversePlotOrder = False
    .ScaleType = xlLinear
    .DisplayUnit = xlNone
    End With

    • 其實我必較在乎的是那個 G19、G20 等絕對座標的問題。 🙂

      也就是說我在程式實作,盡量想去避免掉而改以相對性座標。所以也才會有這類 Named Range 的做法。

      不過還蠻感謝您能提供相關實作的技術的分享。 ^^

  4. 版主您好 請問您有接觸過j線正字圖嗎 也就是所謂j-chart

    是一種市場輪廓變形的理論 本來差點就能取得 但現在的問題是要有人懂R語言才有機會 這是一項有趣的事 現來懇求版主希望有一些=進展突破

  5. 大哥您好,有幸看到您這篇教學文章,因為我是用2007EXCEL,不知是否能使用,
    另外,在”測試欄位資料”中其數據是否要自己輸入??
    感謝您!!

  6. 補充一下
    應該是要問~
    我若要學習VBA和本篇所談的功能,
    不知是否有專門課程和費用?
    因我住南部,有視訊教學嗎?還是必須到北部上課呢?
    謝謝

    • 您好:

      我這篇 blog 內容只是個人的實作備忘錄而已,所以並未公布所有的實作細節,包括 vba 程式碼等。

      現階段我也沒有開設相關程式交易等實作教學的課程,若以您尚未學習 vba 等基本語法,可能仍需花上一段學習基礎程式語法後,會比較理解本篇的內容。

      若以個人一對一的教學,可能對我目前在軟體設計的領域上,並不太洽當,這點請多包涵。

  7. 版大您好
    我不懂程式但很想透過程式透析盤中籌碼變化,
    一直想找類似貴版的內容,所幸瀏覽至此,
    也下載了”Name Manager”,
    但,還是無法了解”如何將動態數據自動分批紀錄”,而製作成圖表,深感困惑。
    懇請,版大再次詳解步驟與程式語法,或者電話教學,或者當面學習,萬分感謝。

  8. 本人使用EXCEL2003與openoffice不知道是否適用這個檔案呢?
    dde訊號只會擷取貼上其他都有看沒有懂……….>_<

    如果把擷取欄與此程式搭配要怎麼用????

    勞煩請以excel2003模式寫出範例講解
    因為下載至今仍不知道怎麼用…謝謝…………

  9. 大哥…您好…

    關於dde…有幸在此看到大哥的網站…

    希望大哥可以開班授課

    或出本書…造福人群…

    我都被被外資打敗了…

    以上…順心

peter 發表迴響 取消回覆

你的電子郵件位址並不會被公開。 必要欄位標記為 *