Board logo

標題: [發問] 輸入數值後，如何啟動自動下拉一格的機制? [打印本頁]
作者: finally0130    時間: 2015-7-21 17:34     標題: 如何在excel表中呈現每日5、10、20周月均線值

本帖最後由 finally0130 於 2015-7-21 17:35 編輯

各位版友們大家好，近日小弟開始學習VBA，想將日收盤價轉換為5、10、20周月均線值，資料下載一般均以日收盤價為主，來源為(上市櫃買中心、yahoo finance 、google finance、鉅亨網)，其中僅有yahoo finance有提供每周均價，但實際比對後發覺部分值有誤，不予參考。

在excel實作中，發現日收盤價轉為周收盤價會有二個問題
1.每周開盤日數不同，Excel抓取資料值會有誤
2.周均價若採機動機制，意味每天五周均價值都會變，似乎技術難度很高

這兩個問題困惑了小弟好幾天了，實在是絞盡腦汁也想不出如何讓Excel變聰明的方法，不知是否版上有高手能解答疑惑，感恩再感恩。
作者: united7878    時間: 2015-7-22 19:18

回復 1# finally0130


    為何不直接由卷商提供的看盤軟體匯出EXCEL就好?
[attach]21451[/attach]→ [attach]21452[/attach]
作者: finally0130    時間: 2015-7-22 23:58

感謝大大分享，這方法也曾想過，但不知如何以自動化方式下載2000檔公司的日周月均值，且需每周每月更新，是否有何特別技巧大大可分享之，感激不盡。
作者: daniel5168    時間: 2015-7-23 00:42

回復 2# united7878


    可以請教一下您這看盤軟體是哪一家劵商的軟體?需要付費使用嗎?此外我也想知道樓主問得有甚麼方式可以自動地把所有股闢的開高低收與成較量等相關資料給傳到EXCEL裡
作者: united7878    時間: 2015-7-23 00:44

回復 3# finally0130

全上市櫃單一日自動更新是可行
(可以即時更新成交價和週、月、季、半年、一年的波動比率)

但要全部日期應不太可能
你光看一檔個股的每日SMA均價就佔住一整個工作表
這還是200根k棒而已
你如果要全部1500多家上市櫃從掛牌到今天的各均價
那要開幾頁工作表才能瀏覽？
作者: finally0130    時間: 2015-7-23 04:11     標題: RE: 如何在excel表中呈現每日5、10、20周月均線值

基本上，小弟目前採取更新【每日收盤價】，以lookup函數抓出每周每月收盤價，但資料有十年，僅能帶出一年正確值，若將選取範圍變大則數值錯誤，
方法有點土法煉鋼，如果大家有更好的方法，可以共同分享討論。數據及圖片如下，再請各位幫忙一下囉!!
[attach]21454[/attach]

[attach]21455[/attach]
作者: united7878    時間: 2015-7-23 16:51

回復  united7878


   可以請教一下您這看盤軟體是哪一家劵商的軟體?需要付費使用嗎?此外我也想 ...
daniel5168 發表於 2015-7-23 00:42


一般卷商提供的看盤軟體都是免費的吧
我用的富邦e-01，最低使用限制條件是：1.集保庫存內有股票、2.半年內需有交易

看盤軟體的個股匯出功能就有您想要的資料更新(即時)
[attach]21462[/attach]
作者: finally0130    時間: 2015-7-23 20:07     標題: 輸入數值後，如何啟動自動下拉一格的機制?

本帖最後由 finally0130 於 2015-7-23 20:09 編輯

大家晚安，請問如何讓手動下拉填滿計算，轉變成單格自動，如圖片所述
首先，操作過程中填入日期後，可自動帶出左方月周函數，然而，輸入價格時，無法自動向下帶出五日均線值。

請問是否有辦法在輸入某列數值，讓手動下拉計算的方式，改為自動下拉?

網址為GIF圖檔示意圖
http://recordit.co/mWppdl4hbe.gif


[attach]21464[/attach]
作者: lpk187    時間: 2015-7-23 20:26

回復 1# finally0130


   可以用Worksheet_Change事件，如下例：
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 4 And Target.Row > 3 And Target <> "" Then '在D欄第4列起，儲存格內容有改為,該儲存格不為空白時啟動
    <<<公式或要執行的程式>>>
End If
End Sub
作者: joey0415    時間: 2015-7-23 21:29

本帖最後由 joey0415 於 2015-7-23 21:30 編輯

回復 6# finally0130

證交所下載一分鐘

轉檔一分鐘
資料放進料庫如access  mysql  sqlite等，再透過sql語法很快才對

查詢完後再放進資料庫例如你的某ma值等

第一次先批次，例如1000天等，比較久，可能三個小時，之時就是每天二分鐘
非常有效率

以前我在某網站上的發問，希望有幫助到
http://www.mobile01.com/topicdetail.php?f=514&t=3522285&p=1#45891769
作者: finally0130    時間: 2015-7-23 21:51

回復 2# lpk187

樓主是以按鈕方式做為啟動嗎?
那該如何設計函數sum(E4:E8)/5、sum(E5:E9)/5，依此類推，或者能直接以儲存格方式呈現，剛學不久很多東西還不很懂，真抱歉= ="
作者: finally0130    時間: 2015-7-23 22:09

回復 7# united7878

U大提供的方法，作為一日股價的外部連結很實用，但因資料庫必須要有十年期數據，未來做資料篩選分析時，才會達到其目的，
某些券商有提供個股(日周月均值每日下載)，但需人工重複選擇(日、周、月均值及1500檔股票)，可能廢時曠日。

謝謝各位的回覆，小弟在實作中發現很多小問題待解決，實在是基本太過薄弱，待將VBA外部連結價格技能習得後，再請教各位囉。
作者: lpk187    時間: 2015-7-23 22:34

本帖最後由 lpk187 於 2015-7-23 22:36 編輯

回復 3# finally0130


    不是用按鈕啟動的！而是在某一範圍(例如你圖例的D欄的第4列起來說)
只要是這其中的儲存格發生變化(就是你有寫入東西後)就會產生變化
此事件的位置如下圖
[attach]21468[/attach]

上圖的程式執行
[attach]21469[/attach]

[attach]21470[/attach]
上圖的程式碼如下(方便你複製)
  1. Private Sub Worksheet_Change(ByVal Target As Range)
  2. On Error Resume Next
  3.     If Target.Column = 4 And Target.Row > 3 And Target <> "" Then
  4.    
  5.         Application.EnableEvents = False
  6.         Cells(Target.Row - 1, 6).AutoFill _
  7.         Destination:=Range(Cells(Target.Row - 1, 6), Cells(Target.Row, 6)), Type:=xlFillDefault
  8.         Application.EnableEvents = True
  9.     End If
  10. End Sub
複製代碼
作者: finally0130    時間: 2015-7-23 22:49

回復 8# joey0415

之前並未接觸過資料庫與VBA，算是初級構想階段，所以有些問題想請教J大
1.SQL資料、Access資料庫、Excel資料庫何者較為方便易學
2.SQL資料庫，如何進行資料篩選分析，類似財報狗https://statementdog.com/pick/tpe#

想說若能先釐清整體資料庫架構方向及結構，對於未來篩選分析應有很大幫助。
可以的話，若能達到設定(日期條件、財報條件、均價條件)，即可資料篩選及分析1500檔物件，選出符合條件之標的，將可有效減少人腦之耗損呀^^。
作者: lcctno    時間: 2015-7-23 22:52

建議您去開個電子交易之帳戶
以玉山證券之 "A+網路下單" 而言 您可以直接由該下單看盤軟體 直接匯出至Excell檔 均價"x" x 可以能由您自己決定 且有10組均價讓您自行設定 當然我無法知道是不是每家卷商的下單看盤軟體都有這功能
另外真的有需要為了喝牛奶而去開牧場養牛嗎? 除非您不相信別人給的資訊 但您的來源也還是網路得的(yahoo finance)
我提供省時的方法 當然若您很有求知精神 那應該是我失言了 還請見諒
作者: GBKEE    時間: 2015-7-24 06:20

本帖最後由 GBKEE 於 2015-7-24 06:21 編輯

回復 14# finally0130

試試看
第6帖檔案的程式碼
  1. Option Explicit
  2. Dim x_Mon As Integer, x_Week As Integer
  3. Sub Ex()
  4.     Dim R As Long, E As Variant, t As Date
  5.     t = Time
  6.     Range("F4:Z" & Rows.Count) = "" '清除舊有資料 Rows.Count 範圍的列總數,這裡為工作表範圍
  7.    
  8.     R = Range("D4").End(xlDown).Row '日平均價的最後一列號
  9.     With Range("B4:B" & R)          '月別範圍
  10.         .Cells = "=MONTH(RC[2])"    '工作表上寫入函數,[R1C1]工作表上欄名列名表示法
  11.         .Value = .Value             '公式轉值
  12.     End With
  13.     With Range("C4:C" & R)          '周別範圍
  14.         .Cells = "=WEEKNUM(RC[1])"  '2003: 增益集須加入 VBA分析工具箱
  15.         .Value = .Value
  16.     End With
  17.     x_Week = DatePart("ww", Range("D4")) 'VBA的週別函數
  18.     x_Mon = Month(Range("D4"))           'VBA的月別函數
  19.    
  20.     For Each E In Range("D4:D" & R)      '日期範圍
  21.         週月收盤價 E                     '呼叫 Sub 週月收盤價 E(傳遞參數)
  22.         x_Week = DatePart("ww", E)       '再讀當週
  23.         x_Mon = Month(E)                 '再讀當月
  24.     Next
  25.     均價      '呼叫 Sub 均價
  26.     MsgBox Application.Text(Time - t, "共計執行 [s] 秒")
  27. End Sub
  28. Sub 均價()
  29.     Dim Rng As Range, E As Variant,  i As Integer, AR()
  30.     AR = Array(5, 10, 20, 60, 120)
  31.     For Each E In Array("F4", "N4", "V4")   '日平均價,周平均價,月平均價的第一個欄位
  32.         Set Rng = Range(E).Resize(Range(E).Offset(, -1).End(xlDown).Row - 3, 5)
  33.         For i = 0 To UBound(AR)
  34.             With Rng.Columns(i + 1) '均價範圍的每一個欄位範圍
  35.                 If .Rows.Count > AR(i) Then  '範圍小於均價日數 下面的With會有錯誤
  36.                     With .Cells(AR(i)).Resize(.Rows.Count - AR(i) + 1)
  37.                         .Cells = "=Average(RC[" & -i + -1 & "]:R[" & -AR(i) + 1 & "]C[" & -i + -1 & "])"
  38.                         '工作表上寫入公式
  39.                     End With
  40.                 End If
  41.             End With
  42.         Next
  43.         Rng.Value = Rng.Value  '轉公式為值
  44.     Next
  45. End Sub
  46. Sub 週月收盤價(E As Variant) '讀取到:周收盤價,月收盤價
  47.     If x_Week <> DatePart("ww", E) Then  '不同週數
  48.         With Range("L" & Rows.Count).End(xlUp).Offset(1)
  49.             .Resize(, 2) = E.Offset(-1).Resize(, 2).Value 'E的上一列
  50.             .Cells(1, 0) = DatePart("ww", E.Offset(-1))
  51.         End With
  52.     End If
  53.     If x_Mon <> Month(E) Then           '不同月數
  54.         With Range("T" & Rows.Count).End(xlUp).Offset(1)
  55.             .Resize(, 2) = E.Offset(-1).Resize(, 2).Value
  56.             .Cells(1, 0) = Month(E.Offset(-1))
  57.         End With
  58.     End If
  59. End Sub
複製代碼
作者: joey0415    時間: 2015-7-24 09:30

回復 14# finally0130

一般日資料或三大法人或期貨資料等，一般資料庫存取就很方便

如果加上財報的話，我個人試做多次，最後是每檔個股放在一個excel檔中，每個檔的頁面存放資料不同，如第一面是現金流量表，第二頁是xxx
財報若放一般資料庫，將資放進去很容昜，但要將資料串接在一起時，它的left join的語法很長容易出錯，不過程度好的沒差，再者財報會有金融與營建與一般公司報表不同，串接時有有問題

我自己則是試寫多年後才改成如此

資料少放什麼地方都快，資料多要組合要串接時問題會有一堆要花很多時間克服，如果真的覺得財報狗對你有用的話，說真的花錢買服務還有客服電話可打也不錯

如果你只是要簡單篩選的話，很多網站都有，不要自己寫程式
作者: finally0130    時間: 2015-8-14 21:27

回復 16# GBKEE


遲來的回覆，G大提供的程式碼真的管用，非常感謝。
作者: finally0130    時間: 2015-8-14 21:31

回復 17# joey0415


J大您好，您說的excel單股單檔的資料庫模式，是指ADO外部連結資料嗎?
如果要學習的話，有什麼推薦書籍或網站可供參考，可供新手入門，感恩。
作者: joey0415    時間: 2015-8-14 21:48

本帖最後由 joey0415 於 2015-8-14 21:50 編輯

回復 19# finally0130

http://www.kingstone.com.tw/search/result.asp?c_name=%25E5%258F%25A4%25E5%25B7%259D%25E9%25A0%2586%25E5%25B9%25B3&se_type=4&q_type=au

這個人的書可以買，我有好幾本

g大的方法是一檔一檔，假設一檔1秒，每日1500檔掃股，就要1500秒，
若改用資料庫語法，它可以一次算所有的股票一起計算類似ma等指標，一次約一分鐘，最後輸出至excel中判斷，最多再加五秒...

access可以、sqlite也行

我自己是sqlite，它的好處是和access都是單檔，但是它沒有容量限制，access是2g，多了要切割，它是免費的
入門後多練習sql語法就會發現真的很快

以上為個人經驗參考即可

以下為sql語法教學網站
http://www.1keydata.com/tw/sql/sql.html



歡迎光臨 麻辣家族討論版版 (http://forum.twbts.com/)