Zi 字媒體
2017-07-25T20:27:27+00:00
資產配置再平衡google試算表（一）- 再平衡
資產配置再平衡google試算表（二）- 交易
資產配置再平衡google試算表（三）- 換匯
資產配置再平衡google試算表（四）- 資產成長、VT全球股市市值比例
[Python]上傳List資料到google試算表
暨之前做的內容，
接下來這次要做的是跑個程式讓豐存股的交易自動寫到google試算表，
下面的程式只會上傳當月的交易記錄，
所以假如6號扣款，
7號10:30後~月底前跑一次程式就好了
多跑會重複記錄，
記得砍掉多的記錄。
首先下載chromedriver到資料夾，
版本要跟目前使用的瀏覽器版本相同
到說明->關於google chrome可以看到目前版本
版本不對的話下面跑程式他會告知你該用哪個版本，
我mac目前的版本如下。
接著安裝Anaconda，
到chome設定裡的proxy增加http://localhost:8888，
這樣才不會被擋住打不開Anaconda
接著打開Jupyter notebook
建立python3
安裝selenium,pandas,gspread,numpy,datetime模組
pip install selenium
pip install pandas
pip install gspread
pip install numpy
pip install datetime
打完按Run。
接著打以下內容修改橘色文字，
跑完會自動上傳豐存股台股、美股的本月交易記錄到google試算表。
from selenium import webdriver
from selenium.webdriver.chrome.options import Options
from selenium.webdriver.common.keys import Keys
from selenium.webdriver import DesiredCapabilities
from selenium.common.exceptions import NoSuchElementException
from selenium import webdriver
from selenium.webdriver.support.wait import WebDriverWait
from selenium.webdriver.support import expected_conditions as EC
from selenium.webdriver.common.by import By
from selenium.webdriver.common.action_chains import ActionChains
import datetime
import time
import pandas as pd
import gspread
from google.oauth2.service_account import Credentials
from gspread_dataframe import get_as_dataframe, set_with_dataframe
import numpy as np
options = webdriver.ChromeOptions()
options.add_argument('--headless')
#------ google sheet授權 ------
scopes = ['https://www.googleapis.com/auth/spreadsheets','https://www.googleapis.com/auth/drive']
credentials = Credentials.from_service_account_file('自己的試算表api.json',scopes=scopes) #參考[Python]上傳List資料到google試算表
gc = gspread.authorize(credentials)
spreadsheet_key='試算表名稱' #以下圖為例就是「資產配置的副本」
sh=gc.open_by_key("試算表id") #讀取工作表，以下圖為例 試算表id是1YDThQPsPf2-AWYRztM66rVsXlGudcejLk7iAEPpmK-w
# ------ 設定要前往的網址 ------
url = 'https://aiinvest.sinotrade.com.tw/Login'
# ------ 登入的帳號與密碼 ------
username = '輸入自己的豐存股帳號'
password = '輸入自己的豐存股密碼'
# ------ 透過Browser Driver 開啟 Chrome ------
driver = webdriver.Chrome('chromedriver位址') #剛剛下載的chromedriver，我是放在/Users/chromedriver
#,options=options
# ------ 前往該網址 ------
driver.get(url)
print("---------------------------登入成功---------------------------")
# ------ 賬號密碼 ------
# time.sleep(1)
WebDriverWait(driver, 30).until(EC.presence_of_element_located((By.XPATH, '//*[@id="LoginId"]')))
elem = driver.find_element_by_id("LoginId")
elem.send_keys(username)
elem = driver.find_element_by_id("Pwd")
elem.send_keys(password)
elem.send_keys(Keys.RETURN)
# ------ 切換台股頁面 ------
spec_url = 'https://aiinvest.sinotrade.com.tw/Member/Trans#reloaded'
driver.get(spec_url)
# ------ 選取成交明細 ------
button = driver.find_element_by_link_text("ETF")
button.click()
#------ 取得本頁表格 ------
WebDriverWait(driver, 30).until(EC.presence_of_element_located((By.XPATH, '//*[@id="mm-0"]/div[12]/div/div[2]/div[1]/div[2]')))
html=driver.page_source
tables=pd.read_html(html)
print("---------------------------取得台股表格---------------------------")
# ------ 獲得成交明細 ------
table=tables[0]
table.columns = ['日期','商品','股數','收盤價','交割金額','手續費','狀態']
# ------ 切換美股頁面 ------
spec_url = 'https://aiinvest.sinotrade.com.tw/foreign/member/detail'
driver.get(spec_url)
# ------ 再次登入 ------
WebDriverWait(driver, 30).until(EC.presence_of_element_located((By.XPATH, '//*[@id="LoginId"]')))
elem = driver.find_element_by_id("LoginId")
elem.send_keys(username)
elem = driver.find_element_by_id("PWord")
elem.send_keys(password)
elem.send_keys(Keys.RETURN)
# ------ 打開成交明細 ------
spec_url = 'https://aiinvest.sinotrade.com.tw/foreign/member/detail'
driver.get(spec_url)
#------ 取得本頁表格 ------
WebDriverWait(driver, 30).until(EC.presence_of_element_located((By.XPATH, '//*[@id="start_date"]')))
html=driver.page_source
tables2=pd.read_html(html)
print("---------------------------取得美股表格---------------------------")
#------ 關掉瀏覽器 ------
driver.close()
# ------ 獲得成交明細 ------
table2=tables2[0]
table2.columns = ['申購方式','成交日期','商品','買賣','成交量','成交價','成交價金','手續費','幣別']
#------ 台股買入表格整理 ------
data = pd.DataFrame()
data.insert(0, column="日期", value=pd.to_datetime(table['日期'], format='%Y%m%d'))
data.insert(1, column="名稱", value=table['商品'].str.replace("00692 富邦公司治理100","00692富邦公司治理"))
data.insert(2, column="股數", value=table['股數'].astype('float64'))
data.insert(3, column="收盤價", value=table['收盤價'].astype('float64'))
data.insert(4, column="買入金額", value=table['交割金額']-table['手續費'].astype('float64'))
data.insert(5, column="手續費", value=table['手續費'].astype('float64'))
data.insert(6, column="總投入金額", value=table['交割金額'].astype('int'))
data.insert(7, column="匯率", value=1)
#data.insert(8, column="總投入金額(TWD)", value="")
#------ 美股買入表格整理 ------
data_U=pd.DataFrame()
data_U.insert(0, column="日期", value=pd.to_datetime(table2['成交日期'], format='%Y/%m/%d'))
table2['商品']=table2['商品'].str[:4]
table2['商品']=table2['商品'].str.replace("(","", regex=True)
data_U.insert(1, column="名稱", value=table2['商品'].str.replace(")","", regex=True))
data_U.insert(2, column="股數", value=table2['成交量'].astype('float64'))
data_U.insert(3, column="收盤價", value=table2['成交價'].astype('float64'))
data_U.insert(4, column="買入金額", value=table2["成交價金"].astype('float64'))
data_U.insert(5, column="手續費", value=table2['手續費'].astype('float64'))
data_U.insert(6, column="總投入金額", value=data_U['買入金額']+data_U['手續費'])
data_U.insert(7, column="匯率", value="='換匯'!$G$3")
#data_U.insert(8, column="總投入金額(TWD)", value="")
#------ 合併,排序,重置表格順序 ------
總交易表格 = pd.concat([data, data_U], ignore_index=True)
up_data = 總交易表格.sort_values(["日期"], ascending=True)
#------ 讀取交易筆數 ------
trade=sh.worksheet("交易") #寫在交易的工作表最底下空白處
count=0
for i in trade.col_values(1):
count=count+1
if i=='':
break
#------ 定義這個月1號 ------
now = datetime.date.today()
this_month_start = datetime.datetime(now.year, now.month, 1)
#------ 本月交易記錄 ------
this_month_trade=up_data["日期"]> this_month_start
#------ 建立日期大於這個月1號的DataFrame 然後reset index ------
upload=pd.DataFrame()
upload=up_data[this_month_trade]
upload.reset_index(drop=True, inplace=True)
#------ 揷入總投入金額公式 ------
t=pd.DataFrame(np.arange(len(upload.index))+count,columns=['map'])
upload.insert(8, column="總投入金額(TWD)", value="=G"+t["map"].map(str) + "*H" + t["map"].map(str))
#上傳資料
set_with_dataframe(trade, upload, row = count, col = 1, include_index = False, include_column_header = False)
print("---------------------------寫入成功---------------------------")
upload
成功後會出現
google試算表也會出現
但豐存股有時會變更網頁位置資料，
有變動我再來修改更新吧。
寫了
5860316篇文章，獲得
23313次喜歡