最近有學員問到一個常被問到，也是很實用的問題，
因此整理成為期貨VBA自動化範例，
算是把之前所教的基本功夫融入進來，
就可以自動完成本來需要非常耗費時間的事，
可以將十幾萬筆資料，在幾秒鐘內，就可以自動得到需要的資料，
包括開盤、收盤、最高、最低、總量，
這裡需要用的的技術包括：
1.篩選巨集錄製
2.如何新增工作表
3.如何刪除工作表與不顯示確定刪除對話視窗
4.如何動態輸入函數取得結果，
如最高的MAX、最低的MIN、總計的SUM。
5.另外就是迴圈範圍與取得範圍內的最下面一列。
可能最容易犯錯的大概就是把變數放在字串之中了，
其實只要熟悉後，就可以在數秒完成本來需要幾個小時的工作，問題：
- 時間在TXDATA中84500指是的8點45分00秒,84559指的是8點45分59秒,
846為84500-84600,847為84601-84700,以後以此類推。 - 開為開盤價，每一個時間的第一個價格,例如847的第一個價格為84601的第一個價格。
- 高、低則為該分鐘內最高價和最低價,如時間847的高低為84601至84700的最大值和最小值。
- 收為收盤價,如時間847的高低為84601至84700的最後一個價格也就是84700中的最後一個價
- 近月量指是該分鐘所有秒數的成交量加總
- 近月總量則為從開盤後累積的成交量
步驟如下：
01_需求畫面
02_結果畫面(取得開收高低與量)
.png)
03_首先利用篩選功能並錄製巨集(條件為起迄)
.png)
04_錄完的程式進行修改
05_建立起迄工作表當成篩選範圍
06_修改程式(迴圈與篩選條件)
.png)
07_取得開收高低與量程式
教學影音：
不足之處思考：
- 如何自動下載期交所的期貨資料
- 如何自動每幾個小時自動下載資料
- 如何讓畫面閃爍
- 如何自動將每天的結果自動會出到資料庫中
- 如何批次下載股市資料到EXCEL中
- 如何建立表單與將EXCEL當成資料庫
- 如何讓用ACCESS將EXCEL的資料
- 如何排序工作表
- 如何將單次查詢改為批次查詢(EXCEL VBA自動化)
- 如何移動工作表與排序之1
- 如何在EXCEL VBA的工作表隨機上顏色
- 如何快速在EXCEL中建立查詢系統(免資料庫)之2
- 如何精簡VBA程式與傳遞引數
- 如何快速在EXCEL中建立查詢系統(免資料庫)之1
- 如何將大量工作表整合在一個工作表
- 如何在EXCEL VBA新增沒有重複名稱工作表
- 如何自動下載YAHOO股市資料到EXCEL中
- 如何建立表單與將EXCEL當成資料庫使用
- 如何在EXCE VBA中複製工作表
- 如何在EXCEL VBA如何保護工作表
- 如何在EXCEL VBA隱藏與顯示工作表
- 如何在EXCEL VBA新增沒有重複名稱工作表
- 如何在EXCEL工作表中移動
- EXCEL VBA辦公自動化_如何建立查詢系統
https://groups.google.com/forum/#!forum/scu_excel_vba2_83
與前幾期的課程雖然用的是相同的範例，但最大的不同在：
1.除了解說建函數公式，並將之轉成自訂函數，把複雜的公式變簡單。
2.如何將複雜的公式變成簡單的按鈕，按下按鈕就自動完成工作。
內容主要分成：
1.常用函數
2.進階函數應用
3.從函數到VBA設計
4.如何自訂函數
5.VBA程式設計入門
6.如何錄製巨集與修改
7.如何將大量資料轉存到資料庫
8.如何讀取資料庫資料
詳細目錄：http://goo.gl/eQptxx
有講義與範例和完成的畫面公式與程式碼，
只要按照每周的順序學習，學會EXCEL VBA自動化絕非難事，
優點：
1.可非線性學習：可按照自己最不熟的部分多次學習。
2.可反覆學習：有疑問可以多次聽講，保證學的會。
3.可攜帶學習：只要有瀏覽器就可以播放SWF檔，MAC電腦也沒問題。
課程理念:
1.以循序漸進的方式, 透過詳細的說明和實用的Excel VBA範例, 逐步了解整個 VBA 的架構與輪廓,進而學習 VBA 變數、函式及邏輯的觀念, 即使沒有任何程式設計基礎, 也能自己親手撰寫 VBA 程序來提昇工作效率, 晉身職場 Excel 高手！ 2.進而解說EXCEL與資料庫的結合，將EXCEL當成資料庫來使用，結合函數、VBA等更深入的功能，讓資料處理和分析的應用更上層樓。 3.將結合GOOGLE雲端試算表，教您如何將EXCEL函數雲端化與網路化。
更多EXCEL VBA連結：
01_EXCEL函數與VBA http://terry28853669.pixnet.net/blog/category/list/1384521
02_EXCEL VBA自動化教學 http://terry28853669.pixnet.net/blog/category/list/1384524
吳老師 2015/1/31
EXCEL,VBA,函數東吳進修推廣部,EXCEL,VBA,函數,程式設計,,指令教學,vba範例教學excel,,excel,vba教學視頻,excel函數教學,excel函數應用
How to batch download stock data to EXCEL in the 2
01_ establish a list of
02_ recording macros and modify two variables
03_ two additional variables and loops
04 _ Delete Column A satellite automatically adjust column widths
05_ complete code
Delete the old worksheet 06_ up
Following the previous share, how to download batches of stock market data to EXCEL in a [link]
Most often asked is how the participants automatic batch downloading information on the web to EXCEL in
And can be automatically placed in the new worksheet,
The best possible time when the next new batch delete the old worksheet,
But how to do it?
First of all, we must first learn how to automatically download data to EXCEL in stock YAHOO [link]
Then be ready first destination URL required form fields: stocks and URLs
I used to convert WORD format, and finally converted to the form can be,
Again is written (copy) required for the program, in fact, are also part to change prior written on it,
That part of the program is on the download page to record macros comes just put it back into the ring,
Over and over again so that he can help us to download the data.
Great response, so there will be more after the 2,3,4 ....,
This main detail to illustrate the method on such automated download Internet resources,
First, the macro record is good, the next is the two materials into a variable, I use X, Y,
But the name can be customized with a name stocks when X, Y as a URL,
As for friends will ask, why not declare because VBA can not declare you can use,
It does not need more convenient, of course, you can declare it!
Just because talk again declare variables and data types, for users of non-IT background, I am afraid there is difficult.
The next loop plus range, the list is end to end, automatic caught last column position
So you can automatically increase or decrease the range, and
And remove unneeded columns and columns, and automatically adjust column widths,
Of course, you can use the Columns object control on it, the column items Rows object likewise,
Finally, at the beginning of add, delete the old worksheet, thus completing automated.
There are many shortcomings, after then explain Hello!
01_ establish a list of
02_ recording macros and modify two variables
03_ two additional variables and loops
04 _ Delete Column A satellite automatically adjust column widths
05_ complete code
Delete the old worksheet 06_ up
Teaching Video:
Deficiencies thinking:
How fast will the page URL into the form?
By the way how the next screening results when data needs (such as data download only rise)
How to rewrite a fixed time automatically download data?
Other related learning:
How to batch download stock data in EXCEL
How to create a form with the EXCEL as a repository
How to make use of the information ACCESS to EXCEL
How to sort worksheets
How will a single query to a batch query (EXCEL VBA automation)
How to move a worksheet and sort of a
How random color in EXCEL VBA worksheet
How to quickly build a query system in EXCEL (free library) of 2
VBA program and how to streamline the transfer argument
How to quickly build a query system in EXCEL (free library) of 1
How to integrate a large number of worksheets in a worksheet
How no duplicate names new worksheet in EXCEL VBA
How to automatically download data to EXCEL in stock YAHOO
How to create a form and as a repository using the EXCEL
How do I copy a worksheet in EXCE VBA in
How to protect a worksheet in EXCEL VBA
How to hide and display the worksheet in the EXCEL VBA
How no duplicate names new worksheet in EXCEL VBA
How to move in EXCEL worksheet
EXCEL VBA office automation system _ how to build queries
Teaching Forum:
https://groups.google.com/forum/#!forum/scu_excel_vba2_83
With the previous period, although the use of the course is the same paradigm, but the biggest difference in:
1. In addition to commentary build function formula, and it turned into a custom function, the complex formula becomes simple.
2. How will the complex formula becomes simple button, press the button to automatically complete the work.
The content is divided into:
1. Common Functions
2. Advanced Function
3. VBA function to design
4. How to customize the function
5.VBA introductory programming
6. How to record macros and modification
7. How will dump large amounts of data repositories
8. How to read data repository
Inventory: http: //goo.gl/eQptxx
There are handouts with examples and complete picture of formulas and code,
Just follow the order of a week of learning, not difficult to learn EXCEL VBA automation,
Benefits:
1. Can the nonlinear learning: learning can be repeated in accordance with their most familiar part.
2. Can Repetition: There are questions you can listen several times to ensure that school will be.
3. portable learning: As long as there is a browser can play SWF files, MAC computer, no problem.
Class reference book:
Look! -50 Is more than you had to work a ExcelVBA master problem-solving method
Author: Yang Yuwen Press: Chung
Publication Date: February 15, 2011 Price: 550 yuan
Curriculum ideas:
1. in a progressive manner, through detailed descriptions and practical examples of Excel VBA, develop an understanding of architecture and outline the entire VBA, and then learn the concept of VBA variables, function and logical, even without any programming foundation, but also to their own hands Write a VBA program to improve work efficiency, entry into the workplace Excel expert! 2. then combined with commentary EXCEL database, the database will be used as EXCEL, combining functions, VBA and other more in-depth feature that allows applications to new heights data processing and analysis. 3. GOOGLE cloud will combine spreadsheets, teach you how to EXCEL cloud-based and network-based functions.
More EXCEL VBA link:
01_EXCEL functions and VBA http://terry28853669.pixnet.net/blog/category/list/1384521
02_EXCEL VBA automation teaching http://terry28853669.pixnet.net/blog/category/list/1384524
Wu 2015/1/13
EXCEL, VBA, Soochow education promotion department functions, EXCEL, VBA, function, program design,, instruction teaching, vba examples of teaching excel ,, excel, vba instructional videos, excel function of teaching, excel function application, large data processing
How to batch download stock data to EXCEL in the 2
01_ establish a list of
02_ recording macros and modify two variables
03_ two additional variables and loops
04 _ Delete Column A satellite automatically adjust column widths
05_ complete code
Delete the old worksheet 06_ up
Following the previous share, how to download batches of stock market data to EXCEL in a [link]
Most often asked is how the participants automatic batch downloading information on the web to EXCEL in
And can be automatically placed in the new worksheet,
The best possible time when the next new batch delete the old worksheet,
But how to do it?
First of all, we must first learn how to automatically download data to EXCEL in stock YAHOO [link]
Then be ready first destination URL required form fields: stocks and URLs
I used to convert WORD format, and finally converted to the form can be,
Again is written (copy) required for the program, in fact, are also part to change prior written on it,
That part of the program is on the download page to record macros comes just put it back into the ring,
Over and over again so that he can help us to download the data.
Great response, so there will be more after the 2,3,4 ....,
This main detail to illustrate the method on such automated download Internet resources,
First, the macro record is good, the next is the two materials into a variable, I use X, Y,
But the name can be customized with a name stocks when X, Y as a URL,
As for friends will ask, why not declare because VBA can not declare you can use,
It does not need more convenient, of course, you can declare it!
Just because talk again declare variables and data types, for users of non-IT background, I am afraid there is difficult.
The next loop plus range, the list is end to end, automatic caught last column position
So you can automatically increase or decrease the range, and
And remove unneeded columns and columns, and automatically adjust column widths,
Of course, you can use the Columns object control on it, the column items Rows object likewise,
Finally, at the beginning of add, delete the old worksheet, thus completing automated.
There are many shortcomings, after then explain Hello!
01_ establish a list of
02_ recording macros and modify two variables
03_ two additional variables and loops
04 _ Delete Column A satellite automatically adjust column widths
05_ complete code
Delete the old worksheet 06_ up
Teaching Video:
Deficiencies thinking:
How fast will the page URL into the form?
By the way how the next screening results when data needs (such as data download only rise)
How to rewrite a fixed time automatically download data?
Other related learning:
How to batch download stock data in EXCEL
How to create a form with the EXCEL as a repository
How to make use of the information ACCESS to EXCEL
How to sort worksheets
How will a single query to a batch query (EXCEL VBA automation)
How to move a worksheet and sort of a
How random color in EXCEL VBA worksheet
How to quickly build a query system in EXCEL (free library) of 2
VBA program and how to streamline the transfer argument
How to quickly build a query system in EXCEL (free library) of 1
How to integrate a large number of worksheets in a worksheet
How no duplicate names new worksheet in EXCEL VBA
How to automatically download data to EXCEL in stock YAHOO
How to create a form and as a repository using the EXCEL
How do I copy a worksheet in EXCE VBA in
How to protect a worksheet in EXCEL VBA
How to hide and display the worksheet in the EXCEL VBA
How no duplicate names new worksheet in EXCEL VBA
How to move in EXCEL worksheet
EXCEL VBA office automation system _ how to build queries
Teaching Forum:
https://groups.google.com/forum/#!forum/scu_excel_vba2_83
With the previous period, although the use of the course is the same paradigm, but the biggest difference in:
1. In addition to commentary build function formula, and it turned into a custom function, the complex formula becomes simple.
2. How will the complex formula becomes simple button, press the button to automatically complete the work.
The content is divided into:
1. Common Functions
2. Advanced Function
3. VBA function to design
4. How to customize the function
5.VBA introductory programming
6. How to record macros and modification
7. How will dump large amounts of data repositories
8. How to read data repository
Inventory: http: //goo.gl/eQptxx
There are handouts with examples and complete picture of formulas and code,
Just follow the order of a week of learning, not difficult to learn EXCEL VBA automation,
Benefits:
1. Can the nonlinear learning: learning can be repeated in accordance with their most familiar part.
2. Can Repetition: There are questions you can listen several times to ensure that school will be.
3. portable learning: As long as there is a browser can play SWF files, MAC computer, no problem.
Class reference book:
Look! -50 Is more than you had to work a ExcelVBA master problem-solving method
Author: Yang Yuwen Press: Chung
Publication Date: February 15, 2011 Price: 550 yuan
Curriculum ideas:
1. in a progressive manner, through detailed descriptions and practical examples of Excel VBA, develop an understanding of architecture and outline the entire VBA, and then learn the concept of VBA variables, function and logical, even without any programming foundation, but also to their own hands Write a VBA program to improve work efficiency, entry into the workplace Excel expert! 2. then combined with commentary EXCEL database, the database will be used as EXCEL, combining functions, VBA and other more in-depth feature that allows applications to new heights data processing and analysis. 3. GOOGLE cloud will combine spreadsheets, teach you how to EXCEL cloud-based and network-based functions.
More EXCEL VBA link:
01_EXCEL functions and VBA http://terry28853669.pixnet.net/blog/category/list/1384521
02_EXCEL VBA automation teaching http://terry28853669.pixnet.net/blog/category/list/1384524
Wu 2015/1/13
EXCEL, VBA, Soochow education promotion department functions, EXCEL, VBA, function, program design,, instruction teaching, vba examples of teaching excel ,, excel, vba instructional videos, excel function of teaching, excel function application, large data processing
No comments:
Post a Comment