ªð¦^¦Cªí ¤W¤@¥DÃD µo©«

[­ì³Ð] ¤W¥«Âd¤T¤jªk¤H¶R½æ¶W¤é³ø¸ê®Æ·J¾ã³B²z

¦^´_ 10# VBALearner
¯à§_´£¦@¥t¤T­Óºô§}¡H

TOP

¦^´_ 11# c_c_lai

¤W¥«¶R½æ¶W¤é³ø : http://www.twse.com.tw/ch/trading/fund/T86/T86.php
¤W¥«½L«á¦æ±¡ªí : http://www.twse.com.tw/ch/trading/exchange/MI_INDEX/MI_INDEX.php
¤WÂd¶R½æ¶W¤é³ø : http://www.tpex.org.tw/web/stock/3insti/daily_trade/3itrade_hedge.php?l=zh-tw
¤WÂd½L«á¦æ±¡ªí : http://www.tpex.org.tw/web/stock/aftertrading/otc_quotes_no1430/stk_wn1430.php?l=zh-tw

¥Ø«e³o­Ó·J¾ãExcelÁÙ¦b¶}µo¶¥¬q«¢«¢«¢¡A¥¼¨Ó¯à¦³¤°»ò»ù­ÈÁ٫ݵZ¿i...-w-

TOP

¥»©«³Ì«á¥Ñ c_c_lai ©ó 2016-8-6 10:36 ½s¿è

¦^´_ 12# VBALearner
§Ú°µ¤F¤@¨Ç§ó°Ê (¾ã¦X¡B¥H¤Î»yªk­×¥¿)¡A ½Ð°Ñ¦Ò¡G
¤W¥«Âd¤T¤jªk¤H¶R½æ¶W.rar (860.81 KB)
¦Ü©ó¦Û°Ê¤U¸ü©|¦b¬ã¨s¡B«ä¦Ò¤¤¡C
¤W¥«¤WÂd¥æ©ö(tbl As String, tbl2 As String, pos1 As Integer, pos2 As Integer, typ As String, Optional auto As Boolean = False)
¤¤ªº auto §Y¬°¹w¯d¤§¥ñµ§¡C

TOP

¥»©«³Ì«á¥Ñ c_c_lai ©ó 2016-8-6 11:52 ½s¿è

¦^´_ 12# VBALearner
  1. Option Explicit
  2. Option Base 1

  3. Sub ½Õ¾ãÀx¦s®æ¤j¤p()
  4.     Dim cts As Integer
  5.    
  6.     For cts = 1 To Sheets.Count
  7.         With Sheets(cts)
  8.             .Activate
  9.             .Columns.ColumnWidth = 10
  10.             .Rows.RowHeight = 15
  11.         End With
  12.     Next
  13. End Sub

  14. Sub §å¶q¶×¤J¶R½æ¶W»P»ù®æ()
  15.     Call ¤W¥«¤WÂd¥æ©ö("¤W¥«¥~¸ê", "¤W¥«§ë«H", 4, 7, "201*")    '  §å¶q¶×¤J¶R½æ¶W
  16.     Call ¤W¥«¤WÂd¥æ©ö("¤WÂd¥~¸ê", "¤WÂd§ë«H", 4, 7, "BIG*")    '  §å¶q¶×¤J»ù®æ
  17.     Call ¤W¥«¤WÂd¥æ©ö("¤W¥«¦¬½L»ù", "", 8, 0, "A112201*")     '  §ó·s¶R½æ¶W
  18.     Call ¤W¥«¤WÂd¥æ©ö("¤WÂd¦¬½L»ù", "", 2, 0, "SQUOTE*")     '  §ó·s»ù®æ
  19.     Beep
  20. End Sub

  21. Sub ¤W¥«¤WÂd¥æ©ö(tbl As String, tbl2 As String, pos1 As Integer, pos2 As Integer, typ As String, Optional auto As Boolean = False)
  22.     Dim myFile As String, myDate As String
  23.     Dim rng As Range, stkNo As Variant
  24.     Dim wb As Workbook
  25.     Dim cts As Long, xs As Long
  26.    
  27.     Application.ScreenUpdating = False
  28.     xs = Sheets(tbl).Range("A" & Rows.Count).End(xlUp).Row
  29.     ReDim tget2(xs - 1) As Long
  30.     If pos2 > 0 Then
  31.         ReDim tget3(xs) As Long
  32.     End If

  33.     stkNo = Application.Transpose(Range(Sheets(tbl).Range("A2"), Sheets(tbl).[A2].End(xlDown)))
  34.       
  35.     myFile = Dir("C:\¤T¤jªk¤H§ó·s°Ï\" & typ)
  36.     Do While myFile <> ""
  37.         Workbooks.Open "C:\¤T¤jªk¤H§ó·s°Ï\" & myFile
  38.         Select Case typ
  39.             Case "201*"               '  (20160802_2by_issue.csv)
  40.                 myDate = Left(myFile, 8)
  41.             Case "BIG*"               '  (BIGD_1050802.csv)
  42.                 myDate = Mid(myFile, 6, 7): myDate = CStr((Val(Left(myDate, 3)) + 1911)) + Mid(myDate, 4, 4)
  43.             Case "A112201*"           '  (A11220160802ALL.csv)
  44.                 myDate = Mid(myFile, 5, 8)
  45.             Case "SQUOTE*"            '  (SQUOTE_AL_1050802.csv)
  46.                 myDate = Mid(myFile, 11, 7): myDate = CStr((Val(Left(myDate, 3)) + 1911)) + Mid(myDate, 4, 4)
  47.         End Select
  48.          
  49.         myDate = Left(myDate, 4) + "/" + Mid(myDate, 5, 2) + "/" + Right(myDate, 2)
  50.         
  51.         myFile = Dir '²Ä¤G¦¸½Õ¥ÎDir¨ç¼Æ¦Ó¤£±a¥ô¦ó°Ñ¼Æ¡A«h·|ªð¦^¦Ü¦P¤@¥Ø¿ý¤Uªº¤U¤@­Ó¤å¥ó
  52.         
  53.         Set wb = ActiveWorkbook
  54.         With Sheets(1)
  55.             For cts = 1 To xs - 1
  56.                 Set rng = .Range("A:A").Find(stkNo(cts))
  57.                 If Not rng Is Nothing Then
  58.                     tget2(cts) = IIf(IsNumeric(rng.Offset(0, pos1).Value), rng.Offset(0, pos1).Value, 0)                    '  (0, 8)
  59.                     If pos2 > 0 Then tget3(cts) = IIf(IsNumeric(rng.Offset(0, pos2).Value), rng.Offset(0, pos2).Value, 0)   '  (0, 7)
  60.                 Else
  61.                     tget2(cts) = 0
  62.                     If pos2 > 0 Then tget3(cts) = 0
  63.                 End If
  64.             Next
  65.         End With
  66.         wb.Close SaveChanges:=False
  67.         
  68.         With Sheets(tbl)
  69.             .Activate
  70.             Set rng = Range(.[A1], .[A1].End(xlToRight))     '  ±q A Äæ¦Ü³Ì¥kÄæ¦ì½d³ò
  71.             Set rng = rng.Find(CDate(myDate), LookIn:=xlValues, LookAt:=xlWhole)
  72.             If rng Is Nothing Then
  73.                 .Range("C:C").Insert
  74.                 Set rng = .[C1]
  75.                 rng.Value = myDate
  76.             End If
  77.             '  Range(.Range("C2"), .Range("C" & .[A2].End(xlDown).Row)) = Application.Transpose(tget2)
  78.             Range(rng.Offset(1), .Range(Chr(64 + rng.Column) & .[A2].End(xlDown).Row)) = Application.Transpose(tget2)
  79.         End With
  80.         
  81.         If tbl2 <> "" Then
  82.             With Sheets(tbl2)
  83.                 .Activate
  84.                 Set rng = Range(.[A1], .[A1].End(xlToRight))     '  ±q A Äæ¦Ü³Ì¥kÄæ¦ì½d³ò
  85.                 Set rng = rng.Find(CDate(myDate), LookIn:=xlValues, LookAt:=xlWhole)
  86.                 If rng Is Nothing Then
  87.                     .Range("C:C").Insert
  88.                     Set rng = .[C1]
  89.                     rng.Value = myDate
  90.                 End If
  91.                 '  Range(.Range("C2"), .Range("C" & .[A2].End(xlDown).Row)) = Application.Transpose(tget3)
  92.                 Range(rng.Offset(1), .Range(Chr(64 + rng.Column) & .[A2].End(xlDown).Row)) = Application.Transpose(tget3)
  93.             End With
  94.         End If
  95.     Loop
  96.    
  97.     Application.ScreenUpdating = True
  98. End Sub
½Æ»s¥N½X

TOP

¦^´_ 14# c_c_lai

«z~ÁY´î¦n¦hµ{¦¡½X ·íªì¤]¨S·Q¨ì­n¥Îselect case~·PÁÂ!!! ¦A¹L¤@¨â¤Ñ§Ú§â·s¼W¥[ªº¥\¯à¥[¤W¥h ¦APo¤W¨Óµ¹¤j®a¬Ý¬Ý~

TOP

§Ú¥Ø«e³£¬O¥Îpython§â¸ê®Æ§ì¤U¨Ó¡A·Pıpython¦n¹³¤ñ¸û¦n¼g¤@ÂI¡C
http://forum.twbts.com/thread-18219-1-1.html
¥i¥H¥Îshellªº¤è¦¡¾ã¦X¦b­ì¨Óªºµ{¦¡¤¤¡C
  1. exe_path = ThisWorkbook.Path & "\getTwseOtc\getTwseOtc.exe"
  2. ChDir (exe_path)
  3. Shell (exe_path)
  4. Application.Wait (Now + TimeValue("0:00:05"))
½Æ»s¥N½X

TOP

¦^´_ 16# zyzzyva

ªüªü§Ú½Ä°Ê¦a¥ý¦^ÂФF±zªº­ì³Ð¤å¤FxD ¦]¬°¤Ó¿³¾Ä¤F«¢«¢«¢
¯uªº«Ü·PÁ±zªº§V¤O! §Ú·|¦n¦n¬ã¨s!!! ­è­è§Ú¤w¸g°õ¦æ±zªºµ{¦¡¡A¦¨¥\¤F!!! ¶W¼F®`ªº¶W¨ØªA>w<
¯uªº·P¿E¤£ºÉ~~~!!!
¯u¤ß·PÁ¨C¤@¦ìÄ@·N¤À¨É©Ò¾Ç¡B«ü¾É·s¤âªº¤H!

TOP

        ÀR«ä¦Û¦b : ¶¢¤HµL¼Ö½ì¡A¦£¤HµL¬O«D¡C
ªð¦^¦Cªí ¤W¤@¥DÃD