Excel VBA 股權分散表與股價

很久沒發文，趁今天寫書順道做個範例，用VBA抓股權分散表與股價資料，一樣也將資料轉成視覺化圖形。

價量教學 --- 價跌量縮、價跌量增與漲停的意義

今天來討論有關價跌量縮、價跌量增與漲停的意義。

VBA抓選擇權與三大法人未平倉餘額

今天補上用VBA抓每日選擇權交易未平倉餘額的資料，一樣也將資料轉成視覺化圖形，未來再對該圖轉換成長條圖，有興趣的可以參考以下程式碼。

VBA抓期貨指數與三大法人未平倉餘額

前面教了好幾篇用VBA抓股票與期貨的文章，這篇一樣用VBA抓取期貨指數與三大法人未平倉餘額畫圖，部過又多了一條線，總共四條曲線在同一張圖上，來判斷目前在現貨市場上的可能方向，有問題的趕快發問，不然真的救不了。

VBA抓大盤指數與期貨指數畫圖

前一篇VBA抓大盤指數與漲跌家數畫圖，是否學會將三條曲畫在同一張圖上呢?如果還不會可以再參考這篇，一樣有問題趕快舉手發問，拖到後面可是救不了你。

VBA抓大盤指數與漲跌家數畫圖

前一篇VBA抓大盤指數與融資餘額畫圖，用Excel VBA在同一張圖上疊加兩條曲線的技巧是否已經學會了，現在再玩更進階一點，再加入1條曲線，有看懂了嗎?有問題的同學趕快舉手發問。

VBA抓大盤指數與融資餘額畫圖

前面幾篇用VBA抓資料畫圖，如VBA抓大盤融資餘額、VBA抓取恐慌指數(VIX指數)，都是簡單的作法，現在來玩玩進階一點的作法，在融資餘額上再加上一張大盤指數。

VBA抓大盤融資餘額

用VBA抓取證交所每日融資餘額，並且畫成曲線圖，有興趣研究，請參考如下程式碼。

VBA抓取恐慌指數(VIX指數)

用VBA抓取取恐慌指數，並且畫成曲線圖，有興趣研究，請參考如下程式碼。

股價與價量含意

首次發文與股市相關的研究，這是收藏圖，如果錯誤的地方歡迎指證，或有興趣的可以與我相互論與研究。

補上缺少的Python函數

Python抓劵商公司，忘了附上lib在使用的函數，很抱歉，在此補上缺少的部分。

Python抓取上市上櫃各類股票資訊

使用Python解析網頁取得上市上櫃各類股票的資訊

#!/usr/bin/env python
# -*- coding: utf-8 -*-

#---------------------------------------------
#   抓上市與上櫃各股相關資料
#   Version : 1.1
#   Author : Amin white
#   Release Date : 2012-01-01
#   Python version : 2.7.2
#---------------------------------------------

import csv, time, codecs, urllib, os
from sgmllib import SGMLParser

def main():    
    stockkind = ["sii", "otc"]
    stocktype = [u"上市", u"上櫃"]
    stocknoclass = ({ "01": u"水泥工業",
                      "02": u"食品工業",
                      "03": u"塑膠工業",
                      "04": u"紡織纖維",
                      "05": u"電機機械",
                      "06": u"電器電纜",
                      "07": u"化學生技醫療",
                      "08": u"玻璃陶瓷",
                      "09": u"造紙工業",
                      "10": u"鋼鐵工業",
                      "11": u"橡膠工業",
                      "12": u"汽車工業",
                      "13": u"電子工業",
                      "14": u"建材營造",
                      "15": u"航運業",
                      "16": u"觀光事業",
                      "17": u"金融保險業",
                      "18": u"貿易百貨",
                      "19": u"綜合企業",
                      "20": u"其他",
                      "21": u"化學工業",
                      "22": u"生技醫療業",
                      "23": u"油電燃氣業",
                      "24": u"半導體業",
                      "25": u"電腦及週邊設備業",
                      "26": u"光電業",
                      "27": u"通信網路業",
                      "28": u"電子零組件業",
                      "29": u"電子通路業",
                      "30": u"資訊服務業",
                      "31": u"其他電子業",
                      "91": u"存託憑證"})
    
    #指定儲存的路徑,可自行變更儲存路徑
    workdir = 'D:\\stock_database\\'

    if not os.path.isdir(workdir):
        os.makedirs(workdir)

    savefile = workdir + 'stockinfo.csv'

    #開始寫入檔案準備
    writefile = file(savefile, 'wb')

    #指定檔案以UTF8儲存
    writefile.write(codecs.BOM_UTF8)

    #指定CSV檔分隔的方式
    writer = csv.writer(writefile, delimiter=',', quotechar='"', quoting=csv.QUOTE_ALL)

    #寫入欄位說明
    writer.writerow([u'股票代號'.encode('utf8'), u'股票種類'.encode('utf8'), u'產業分類編號'.encode('utf8'), \
                     u'產業分類名稱'.encode('utf8'), u'公司名稱'.encode('utf8'), u'公司資本額'.encode('utf8'),\
                     u'公司地址'.encode('utf8'), u'公司電話'.encode('utf8'), u'公司開幕日'.encode('utf8'), \
                     u'公司上市上櫃日'.encode('utf8')])
    
    for i in range(0, len(stockkind)):
        kindname = stockkind[i]
        cstocktype= stocktype[i]        

        count = 0
        for classno in sorted(stocknoclass.items(), key=lambda stocknoclass:stocknoclass[0], reverse = False):

            #個股票網址
            url = "http://mops.twse.com.tw/mops/web/ajax_t51sb01?step=1&firstin=1&TYPEK=%s&code=%s" %(kindname, classno[0])

            #解析網頁開始
            webcode = urllib.urlopen(url)
            if webcode.code == 200:
                stock = ParseWebData()
                stock.parse(webcode.read())
                webcode.close()
                stock.close()
                
                if stock.webexist:
                    print kindname + " " + classno[0] + " " + classno[1] + " web parser OK......"
                else:
                    print kindname + " " + classno[0] + " " + classno[1] + " not exist......"
                    continue;


                for j in range(0, len(stock.stockid)):
                    
                    #處理中文編碼
                    stockid = unicode(stock.stockid[j],"utf-8")
                    companyname = unicode(stock.stockcompanyname[j],"utf-8")
                    companyaddress = unicode(stock.stockcompanyaddress[j],"utf-8")
                    companytel = unicode(stock.stockcompanytel[j],"utf-8")
                    companyopendate = unicode(stock.stockcompanyopendate[j],"utf-8")
                    companylistingdate = unicode(stock.stockcompanylistingdate[j],"utf-8")
                    companycapital = unicode(stock.stockcompanycapital[j],"utf-8")

                    #寫入股票資料
                    writer.writerow([ '%s' %stockid.encode('utf8'), '%s' %cstocktype.encode('utf8'), '%s' %classno[0].encode('utf8'),\
                                      '%s' %classno[1].encode('utf8'), '%s' %companyname.encode('utf8'), '%s' %companycapital.encode('utf8'),\
                                      '%s' %companyaddress.encode('utf8'), '%s' %companytel.encode('utf8'), '%s' %companylistingdate.encode('utf8'), \
                                      '%s' %companyopendate.encode('utf8')])
                count += 1
                print kindname + " " + classno[0] + " " + classno[1] + " data write to csv OK......\n"

            if(count%6) == 0:
                time.sleep(10)
                
    #關閉檔案            
    writefile.close()

class ParseWebData(SGMLParser):

    #初始化class等同constructor
    def __init__(self):
        SGMLParser.__init__(self)

    #初始化變數數值
    def reset(self):
        SGMLParser.reset(self)
        self.webexist = False
        self.nowrapflag = False
        self.styleflag = False
        self.nowrapcount = 0
        self.stylecount = 0
        self.stockcompanyname = []
        self.stockcompanyaddress = []
        self.stockcompanytel = []
        self.stockcompanyopendate = []
        self.stockcompanylistingdate = []
        self.stockcompanycapital = []
        self.stockid = []

        
    def parse(self,data):
        self.feed(data)
        self.close()
        
    def start_table(self, attrs):
        if attrs[0][0] == 'class' and attrs[0][1] == 'noBorder':
            self.webexist = True                  
                    
    def start_td(self, attrs):
        for name, value in attrs:
            if len(attrs) == 1:
                if name == 'nowrap':
                    self.nowrapflag = True
                    self.nowrapcount += 1                        
                            
            elif len(attrs) == 2:
                #print len(attrs)
                if name == 'style':
                    if value == 'text-align:left !important;' or value == 'text-align:right !important;':
                        self.styleflag = True
                        self.stylecount += 1                   

    def handle_data(self, text):

        if self.nowrapflag :
            if self.nowrapcount == 1:
                self.stockid.append(text)
                #print "stockid : " + text
                self.nowrapflag = False
            elif self.nowrapcount == 7:
                self.stockcompanyopendate.append(text)
                #print "opendate : " + text
                self.nowrapflag = False
            elif self.nowrapcount == 8:
                self.stockcompanylistingdate.append(text)
                #print "listingdate : " + text
                self.nowrapflag = False
            elif self.nowrapcount == 10:
                self.nowrapcount = 0
                self.nowrapflag = False
            
        if self.styleflag :
            if self.stylecount == 1:
                self.stockcompanyname.append(text)
                #print "name : " + text
                self.styleflag = False
            elif self.stylecount == 2:
                self.stockcompanyaddress.append(text)
                #print "address : " + text
                self.styleflag = False
            elif self.stylecount == 4:
                self.stockcompanytel.append(text)
                #print "tel : " + text
                self.styleflag = False
            elif self.stylecount == 5:
                self.stockcompanycapital.append(text.strip().replace(",", ""))
                #print "capital : " + text.strip().replace(",", "")
                self.styleflag = False
            elif self.stylecount == 14:
                self.stylecount = 0
                self.styleflag = False    

if __name__ == "__main__":
    main()

Python抓取股票代碼

使用Python解析網頁元素抓取股票代碼

觀察證交所提供的股票代碼網頁中，每支股票代碼的網頁標籤以td作為起點，其屬性為bgcolor=#FAFAD2，由於屬性長度為1，所以可將此作為取得每支股票的主要識別起頭。

如上圖所示，如果以屬性bgcolor=#FAFAD2來找我們要的股票代碼勢必會有機會出錯，因為會遇到屬性長度為2的資料，而這並非是我們所要的內容，必須要找出屬性長度為1才是我們要的資料。

故程式依據上述的特性來撰寫，以取得股票代碼。

#!/usr/bin/python
# -*- coding: utf-8 -*-

#---------------------------------------------
#   抓股票代碼
#   Version : 0.1
#   Author : Amin white
#   Release Date : 2012-01-01
#   Python version : 2.7.2
#---------------------------------------------

#引用函式庫
import urllib, sgmllib

def main():
    
    #上市, 上櫃股票代碼網址
    StockType = [2,4]

    for i in range(0, len(StockType)):
        url = "http://brk.twse.com.tw:8000/isin/C_public.jsp?strMode=" + str(StockType[i])  
    
        #解析網頁開始
        webcode = urllib.urlopen(url)
        if webcode.code == 200:
            stock = ParsestrModeWeb().feed(webcode.read())
            webcode.close()            
        
class ParsestrModeWeb(sgmllib.SGMLParser):

    #初始化變數數值
    def reset(self):
        sgmllib.SGMLParser.reset(self)
        self.stockinfo = False
        self.cell = 0 

    #解析網頁標籤為td的內容    
    def start_td(self, attrs):
        if len(attrs) == 1:
            if attrs[0][0] == 'bgcolor' and attrs[0][1] == '#FAFAD2':
                self.stockinfo = True
                self.cell+=1
                self.cell%=7

    #開始讀取股票代碼          
    def handle_data(self, text):
            if self.stockinfo:                
                if self.cell == 1:                   
                    data = text.strip().split("    ")
                    if data[0].isalnum():
                        print data[0].strip()  + " " + data[1].strip()
                self.stockinfo = False
            
if __name__ == "__main__":
    main()

Python執行結果畫面

Python每月營收

使用Python透過解析網頁元素取得公開資訊觀測站上所公佈的每月營收

#!/usr/bin/python
# -*- coding: utf-8 -*-

#---------------------------------------------
#   抓上市與上櫃各股每月營收
#   Version : 1.1
#   Author : Amin white
#   Release Date : 2012-06-27
#   Python version : 2.7
#---------------------------------------------

#引用函式庫
import csv, codecs, urllib, datetime, os, time
from sgmllib import SGMLParser
    
def main():
    #上市櫃公司自90年6月才有登入月營收資料

    #包含上市與上櫃
    stockkind = ['sii', 'otc']

    #指定儲存的路徑,可自行修改儲存路徑
    Savefiledir = 'D:\\Revenue\\'    

    #建立儲存營收CSV資料夾
    if not os.path.isdir(Savefiledir):
        os.makedirs(Savefiledir)

    #取得使用當天的日期
    today = datetime.datetime.today()
    todaysec = time.mktime(datetime.datetime(int(today.strftime('%Y')), int(today.strftime('%m')), int(today.strftime('%d'))).timetuple())

    for i in xrange(len(stockkind)):
        stocktype = stockkind[i]
        for j in range(2012, 2013):
            pyADYear = str(j)
            pyROCYear = str(j - 1911)
            for k in range(1, 13):

                #取得從1月至今日每月的營收
                Revenuedaysec = time.mktime(datetime.datetime(j, k, 10).timetuple())
                if Revenuedaysec <= todaysec:

                    print '取得 ' + pyADYear + ' 年 ' + str('%02d' %k) + ' 月 ' + stocktype + ' 全部公司營收資料' 
                    #營收網址
                    url = "http://mops.twse.com.tw/t21/" + stocktype + "/t21sc03_" + pyROCYear + "_" + str(k) + ".html"

                    #解析網頁開始
                    webcode = urllib.urlopen(url)
                    if webcode.code == 200:
                        stock = Parser_htm()
                        stock.parse(webcode.read())
                        webcode.close()

                    #儲存CSV檔名
                    SaveCSVname = Savefiledir + stocktype + '_' + pyADYear + str('%02d' %k) + '.csv'
                    print '設定寫入檔案名稱與格式內容......'

                    #開始寫入檔案準備
                    fileoption = codecs.open(SaveCSVname, 'wb')

                    #指定檔案以UTF8儲存
                    fileoption.write(codecs.BOM_UTF8)

                    #指定CSV檔分隔的方式
                    writer = csv.writer(fileoption, delimiter=',', quotechar='"', quoting=csv.QUOTE_ALL)

                    print '寫入營收至 ' + SaveCSVname +  ' 開始......'
                    #寫入欄位說明
                    writer.writerow([u'產業別'.encode('utf8'), u'公司代號'.encode('utf8'), u'公司名稱'.encode('utf8'), u'當月營收'.encode('utf8'), u'上月營收'.encode('utf8'),\
                                     u'去年當月營收'.encode('utf8'), u'上月比較增減(%)'.encode('utf8'), u'去年同月增減(%)'.encode('utf8'), u'當月累計營收'.encode('utf8'), \
                                     u'去年累計營收'.encode('utf8'), u'前期比較增減(%)'.encode('utf8')])

                    for i in xrange(len(stock.totaldata)):
                        totaldata = stock.totaldata[i]

                        #寫入每間公司各營收資料
                        writer.writerow([totaldata[0].encode('utf8'), totaldata[1].encode('utf8'), totaldata[2].encode('utf8'), \
                                         totaldata[3].encode('utf8'), totaldata[4].encode('utf8'), totaldata[5].encode('utf8'), \
                                         totaldata[6].encode('utf8'), totaldata[7].encode('utf8'), totaldata[8].encode('utf8'), \
                                         totaldata[9].encode('utf8'), totaldata[10].encode('utf8')])
                    #關閉檔案
                    fileoption.close()                    
                    print '寫入營收至 ' + SaveCSVname + ' 完成......\n'
            
#解析營收網頁class
class Parser_htm(SGMLParser):

    #初始化class等同constructor
    def __init__(self):
        SGMLParser.__init__(self)

    #初始化變數數值
    def reset(self):
        SGMLParser.reset(self)
        self.bPrintDetail = False
        self.bStartParserHeml = False
        self.bStartParserdata = False
        self.bDataempty = False
        
        self.bItemclass = False
        self.szitemclass = ""
        self.bStockid = False
        self.szstockid = ""
        self.bCompanyname = False
        self.nowrapflag = False
        self.nowrapcount = 0
        self.stockdata = []
        self.totaldata = []

    #解析網頁
    def parse(self,data):
        self.feed(data)
        self.close()

    #解析網頁標籤為tr的內容
    def start_tr(self, attrs):
        if len(attrs) == 1:
            if attrs[0][0] == 'align':
                if attrs[0][1] == 'right':
                    self.bStartParserdata = True

    #解析網頁標籤為th的內容
    def start_th(self, attrs):
        #抓取準備解析資料內容起點
        if len(attrs) == 2:
            if attrs[0][0] == 'class' and attrs[0][1] == 'tt' and attrs[1][0] == 'align' and attrs[1][1] == 'left':              
                self.bItemclass = True
                self.szitemclass = ""
                self.bStartParserHeml = True
                
        #抓取準備解析資料內容終點       
        if len(attrs) == 4:
            if attrs[0][0] == 'class' and attrs[0][1] == 'tt' and \
               attrs[1][0] == 'nowrap' and attrs[1][1] == 'nowrap' and \
               attrs[2][0] == 'colspan' and attrs[2][1] == '2' and \
               attrs[3][0] == 'align' and attrs[3][1] == 'center':
                self.bStartParserHeml = False
                self.bStartParserdata = False                

    #解析網頁標籤為td的內容
    def start_td(self, attrs):
        #解析td標籤屬性名稱與屬性
        if len(attrs) == 1:
            if attrs[0][0] == 'align':
                
                #抓股票代碼與資料解析起點
                if attrs[0][1] == 'center':
                    self.bStartParserdata = True
                    self.bStockid = True
                    
                #抓公司名稱起點   
                if attrs[0][1] == 'left':
                    self.bCompanyname = True
                    
            #收尋資料為空字串的終點與解析各營收內容起點
            if attrs[0][0] == 'nowrap' and attrs[0][1] == 'nowrap':
                self.nowrapflag = True
                self.bDataempty = False
                self.nowrapcount += 1                

        #收尋資料為空字串的起點
        if len(attrs) == 0:
            self.bDataempty = True

    #取得網頁表格內容是文字,數字以外的資料
    def handle_entityref(self,ref):
        #解析資料內容為空字串
        if ref == 'nbsp':
            if self.bDataempty:
                self.stockdata.append("")
                self.nowrapcount += 1
                #print ref + " " + str(self.nowrapcount)
            
    #開始讀取各公司營收資料到暫存list中
    def handle_data(self, text):
        #產業別
        if self.bItemclass:
            data = unicode(text.strip(), "BIG5").encode('utf8').split('：')
            self.szitemclass = unicode(data[1], "utf8")
            if self.bPrintDetail:
                print "產業別 :          " + self.szitemclass.encode('utf8') 
            self.bItemclass = False
               
        if self.bStartParserdata:
            
            #公司代碼或稱股票代碼
            if self.bStockid :
                if self.bPrintDetail:
                    print "公司代號 :        " + text.strip()
                self.szstockid = text.strip()
                self.stockdata.append(self.szitemclass)
                self.stockdata.append(self.szstockid)
                self.bStockid = False
                self.nowrapcount = 0

            #公司名稱
            if self.bCompanyname :
                #使用BIG5解碼,因為支援字型不夠,就需要加入以下的內容,若使用cp950解碼,只需加入網頁無法呈現的字型即可                
                """if self.szstockid == '1325':
                    data = u'恒大'
                elif self.szstockid == '2353':
                    data = u'宏碁'
                elif self.szstockid == '3046':
                    data = u'建碁'
                elif self.szstockid == '6285':
                    data = u'啟碁科技'
                elif self.szstockid == '4527':
                    data = u'方土霖'
                elif self.szstockid == '8111':
                    data = u'立碁電子'
                elif self.szstockid == '6174':
                    data = u'安碁科技'
                else:
                    data = text.strip().decode('BIG5')"""
                
                if self.szstockid == '4527':
                    data = u'方土霖'
                else:
                    data = text.strip().decode('cp950')
                    
                if self.bPrintDetail:
                    print "公司名稱 :        " + data.encode('utf8')
                self.stockdata.append(data)
                self.bCompanyname = False            

            if self.nowrapflag :
                self.nowrapflag = False
                #各營收資料
                if self.nowrapcount == 1:
                    if self.bPrintDetail:
                        print "當月營收 :        " + text.strip()
                    data = text.strip().replace(",", "")
                    self.stockdata.append(data)
                elif self.nowrapcount == 2:
                    if self.bPrintDetail:
                        print "上月營收 :        " + text.strip()
                    data = text.strip().replace(",", "")
                    self.stockdata.append(data)
                elif self.nowrapcount == 3:
                    if self.bPrintDetail:
                        print "去年當月營收 :     " + text.strip()
                    data = text.strip().replace(",", "")
                    self.stockdata.append(data)
                elif self.nowrapcount == 4:
                    if self.bPrintDetail:
                        print "上月比較增減(%) :  " + text.strip()
                    data = text.strip().replace(",", "")
                    self.stockdata.append(data)
                elif self.nowrapcount == 5:
                    if self.bPrintDetail:
                        print "去年同月增減(%) :  " + text.strip()
                    data = text.strip().replace(",", "")
                    self.stockdata.append(data)
                elif self.nowrapcount == 6:
                    if self.bPrintDetail:
                        print "當月累計營收 :     "  + text.strip()
                    data = text.strip().replace(",", "")
                    self.stockdata.append(data)
                elif self.nowrapcount == 7:
                    if self.bPrintDetail:
                        print "去年累計營收 :     " + text.strip()
                    data = text.strip().replace(",", "")
                    self.stockdata.append(data)
                elif self.nowrapcount == 8:
                    if self.bPrintDetail:
                        print "前期比較增減(%) :  " + text.strip() + "\n"
                    data = text.strip().replace(",", "")
                    self.stockdata.append(data)
                    self.totaldata.append(self.stockdata)
                    self.stockdata = []

#函數進入點
if __name__ == "__main__":
    main()

Python執行畫面如下圖所示

CSV檔案儲存內容如下

Python抓劵商公司

使用Python透過解析網頁元素來取得全台劵商總公司的資訊

#!/usr/bin/python
#coding=utf-8

#---------------------------------------------
#   抓劵商相關資料
#   Version : 1.1
#   Author : Amin white
#   Release Date : 2012-01-01
#   Python version : 2.7.2
#---------------------------------------------

import codecs, csv
from lib import Broker, Broker2


def CheckBrokerAttribute(bankid):
    Brokerlist = ["9300"] #華南永昌    
    for i in range(0, len(Brokerlist)):
        if bankid[0] == Brokerlist[i][0] and bankid[1] == Brokerlist[i][1]:
            return u'公銀'
        
    Brokerlist = ["1020", #合庫
                  "1030", #土銀
                  "1040", #台銀
                  "1110", #台企
                  "1230", #彰銀
                  "5380", #第一金
                  "7000"] #兆豐    
    for i in range(0, len(Brokerlist)):
        if bankid[0] == Brokerlist[i][0] and bankid[1] == Brokerlist[i][1] and bankid[2] == Brokerlist[i][2]:
            return u'公銀'
        
    Brokerlist = [ "1360", #港商麥格理
                   "1380", #港商里昂
                   "1400", #港商蘇皇
                   "1440", #美林
                   "1470", #台灣摩根士丹利
                   "1480", #美商高盛
                   "1520", #瑞士信貸
                   "1530", #港商德意志
                   "1560", #港商野村台北分公司
                   "1570", #港商法國興業
                   "1590", #花旗環球
                   "1650", #新加坡商瑞銀
                   "6640", #渣打商銀
                   "8440", #摩根大通
                   "8700", #花旗
                   "8890", #大和國泰
                   "8900", #法銀巴黎
                   "8910", #台灣巴克萊
                   "8960"] #香港上海匯豐
    for i in range(0, len(Brokerlist)):
        if bankid[0] == Brokerlist[i][0] and bankid[1] == Brokerlist[i][1] and bankid[2] == Brokerlist[i][2]:
            return u'外資'
        
    return ''

def CheckMembership(MasterBrokerid, bankid):
    Brokerlist = []
    Brokerlist = list(MasterBrokerid)
    removelist = ['9100', #群益
                  '9200', #凱基
                  '9300', #華南永昌
                  '9600', #富邦
                  '2200', #元大寶來
                  '9800'] #元大
    #刪除特殊分類
    for i in range(0, len(removelist)):
        Brokerlist.remove(removelist[i])

    #比對所隸屬的總公司
    for i in range(0, len(Brokerlist)):
        if bankid[0] == Brokerlist[i][0] and bankid[1] == Brokerlist[i][1] and bankid[2] == Brokerlist[i][2]:
            return Brokerlist[i]
        
    #比對特殊分類所隸屬的總公司
    Brokerlist = []
    Brokerlist = list(removelist)
    for i in range(0, len(Brokerlist)):
        if bankid[0] == Brokerlist[i][0] and bankid[1] == Brokerlist[i][1]:
            return Brokerlist[i]

    print "Unknow broker id : " + Brokerlist[i]
    return ''


def Brokernamelist():  
    Brokerlist = Broker()

    Brokerfile = 'D:\\stock_database\\Brokerfile.csv'
    writefile = file(Brokerfile, 'wb')
    writefile.write(codecs.BOM_UTF8)
    writer = csv.writer(writefile, delimiter=',', quotechar='"', quoting=csv.QUOTE_ALL)
    writer.writerow([u'劵商代碼'.encode('utf8'), u'劵商名稱'.encode('utf8'), u'劵商他名'.encode('utf8'), \
                     u'劵商主從'.encode('utf8'), u'劵商屬性'.encode('utf8'), u'劵商隸屬'.encode('utf8'), \
                     u'劵商地址'.encode('utf8'), u'劵商電話'.encode('utf8'), u'劵商開業日期'.encode('utf8'),\
                     u'備註'.encode('utf8')])
    
    for i in range(0, len(Brokerlist.brokerid)):
        brokerid = Brokerlist.brokerid[i]
        if brokerid == '6010':
            brokername = unicode('犇亞', "utf-8")
        else:
            brokername = unicode(Brokerlist.brokername[i], "big5")
            
        brokername = brokername.encode('utf8')
        brokeraddress = unicode(Brokerlist.brokeraddress[i], "big5")
        brokertel = unicode(Brokerlist.brokertel[i], "utf-8") 
        brokeropen = unicode(Brokerlist.brokeropen[i], "utf-8") 
        brokerProperty = CheckBrokerAttribute(brokerid)
        Membership = CheckMembership(Brokerlist.brokerid, brokerid)

        writer.writerow([ '%s' %brokerid.encode('utf8'),  '%s' %brokername, '', \
                          '%s' %u'總公司'.encode('utf8'),  '%s' %brokerProperty.encode('utf8'), '%s' %Membership.encode('utf8'), \
                          '%s' %brokeraddress.encode('utf8'), '%s' %brokertel.encode('utf8'), '%s' %brokeropen.encode('utf8'), ''])

        print brokerid + " " + brokername + " " + brokerProperty.encode('utf8') + " " + "write to csv OK......"
        
    Brokerlist2 = Broker2()
    for i in range(0,len(Brokerlist2.brokerid)):
        brokerid = Brokerlist2.brokerid[i]
        if brokerid == '6012':
            brokername = unicode('犇亞-網路', "utf-8").encode('utf8')
        else:
            brokername = unicode(Brokerlist2.brokername[i], "big5").encode('utf8')
        
        brokeraddress = unicode(Brokerlist2.brokeraddress[i], "big5")
        brokertel = unicode(Brokerlist2.brokertel[i], "big5")
        brokeropen = unicode(Brokerlist2.brokeropen[i], "utf-8")
        brokerProperty = CheckBrokerAttribute(brokerid)
        Membership = CheckMembership(Brokerlist.brokerid, brokerid)
    
        writer.writerow([ '%s' %brokerid.encode('utf8'),  '%s' %brokername, '', \
                          '%s' %u'分公司'.encode('utf8'),  '%s' %brokerProperty.encode('utf8'), '%s' %Membership.encode('utf8'), \
                          '%s' %brokeraddress.encode('utf8'), '%s' %brokertel.encode('utf8'), '%s' %brokeropen.encode('utf8'), ''])
        
        print brokerid + " " + brokername + " " + Membership + " " + "write to database OK......"

    writefile.close()
        

def main():
    Brokernamelist()
    
            
if __name__ == "__main__":
    main()

Python執行畫面如下圖所示

CSV檔案儲存內容如下