def processNotifyNews():
notifySidList = []
# maybe fetch googlesheet
googlesheetService = GooglesheetService(
"1F3cT6ltHQ7gOYxCPSrPJGvMpUt3b5mRJIMR0gJ5ITr8")
rowList = googlesheetService.getValues("新聞通知清單")
for row in rowList:
if len(row) == 0 or row[0] == '' and row[1] == '':
continue # 略過空白行
notifySidList.append(row[0])
print(notifySidList)
with open("news.csv", "r", encoding="utf-8") as f1:
csvRowList = list(csv.reader(f1))
for csvRow in csvRowList:
if csvRow[0] in notifySidList and csvRow[6] == "W":
msg = "{} {} {} {} {}\n\n{}".format(csvRow[0], csvRow[1],
csvRow[2], csvRow[3],
csvRow[4], csvRow[5])
lineTool.lineNotify(os.environ["LINE_TEST_TOKEN"], msg)
csvRow[6] = "Y"
else:
csvRow[6] = "N"
# 寫回 csv 檔
with open("news.csv", "w", encoding="utf-8", newline="") as f1:
csv.writer(f1).writerows(csvRowList)
def fetchMyStockIdList():
stockIdList = []
googlesheetService = GooglesheetService(
"1F3cT6ltHQ7gOYxCPSrPJGvMpUt3b5mRJIMR0gJ5ITr8")
rowList = googlesheetService.getValues("損益")
for row in rowList[4:46]:
if len(row) != 0:
stockIdList.append(row[0])
return stockIdList
def main():
googlesheetService = GooglesheetService(
"1bydUUYVTxyyz2jxm3JRzIQkdElNthLHuQpzW6-DLVUk")
rowList = [
[1, 2, 3],
[4, 5, 6],
]
googlesheetService.appendSheet("華倫存股 穩中求勝",
rowList,
insertDataOption="INSERT_ROWS")
def updateSheet(sheetId, sheetName):
googlesheetService = GooglesheetService(sheetId)
rowNum = 0
rowList = []
for value in googlesheetService.getValues(sheetName):
rowNum += 1
rowList.append(value)
# header
if rowNum == 1:
if len(value) <= 10:
value.append("")
value[10] = datetime.datetime.now().strftime(
'%m%d %H:%M:%S') + " OTC Updated"
continue # header 不繼續下面的邏輯
if len(value) == 0 or value[0] == '':
continue # 略過空白行，或後面有東西，但前面連代號都沒有的
if stockIdMap.get(value[0], "") == "上櫃":
row = stockDataMap[value[0]]
if row != None:
value[3] = '=(E{}-C{})/C{}'.format(rowNum, rowNum, rowNum)
value[4] = row[2] # 現價
value[5] = row[3] # 漲跌
value[6] = row[4] # 漲跌幅度
value[7] = row[5] # 成交量
elif stockIdMap.get(value[0], "") == "上市":
value[3] = '=(E{}-C{})/C{}'.format(rowNum, rowNum, rowNum)
value[
4] = '=GOOGLEFINANCE(CONCATENATE("TPE:", $A{}), "price")'.format(
rowNum)
value[
5] = '=GOOGLEFINANCE(CONCATENATE("TPE:", $A{}), "change")'.format(
rowNum)
value[
6] = '=GOOGLEFINANCE(CONCATENATE("TPE:", $A{}), "changepct") / 100'.format(
rowNum)
value[
7] = '=GOOGLEFINANCE(CONCATENATE("TPE:", $A{}), "volume") / 1000'.format(
rowNum)
value[
8] = '=GOOGLEFINANCE(CONCATENATE("TPE:", $A{}), "pe")'.format(
rowNum)
if stockIdMap.get(value[0], "") == "":
print("not support {}".format(value[0]))
googlesheetService.updateSheet(sheetName, rowList)
def main1():
# googlesheetService = GooglesheetService("1F3cT6ltHQ7gOYxCPSrPJGvMpUt3b5mRJIMR0gJ5ITr8")
# rowList = googlesheetService.getValues("新聞通知清單")
# 平大
# googlesheetService = GooglesheetService("1xgtt4xjZh4Nsg6_uQnuphIbMLrBSLM-0OsOuz93NZAc")
# rowList = googlesheetService.getValues("新聞通知清單")
# 雪人
googlesheetService = GooglesheetService(
"1u5QaL_uyfXhom9iHVMXF1mX8D-Ssdc3zOSGFeS-Z5tk")
rowList = googlesheetService.getValues("新聞通知清單")
print(rowList)
pass
def fetchAllSheetDataListFromMyGooglehseet():
googlesheetService = GooglesheetService("1F3cT6ltHQ7gOYxCPSrPJGvMpUt3b5mRJIMR0gJ5ITr8")
rowList = googlesheetService.getValues("掃描清單")
# rowList = googlesheetService.getValues("測試掃描清單")
rowNum = 0
sheetDataList = []
for row in rowList:
rowNum += 1
if rowNum == 1:
continue
if len(row) == 0 or row[0] == '' and row[1] == '':
continue # 略過空白行
sheetId = row[1].replace("https://docs.google.com/spreadsheets/d/", "").split("/")[0]
# name, sheetId, token, rangeName
sheetDataList.append([row[0], sheetId, row[2], row[3]])
return sheetDataList
def fetchOtcStockIdFormSheet(sheetId, sheetName, token):
googlesheetService = GooglesheetService(sheetId)
rowNum = 0
for value in googlesheetService.getValues(sheetName):
rowNum += 1
# header
if rowNum == 1:
continue # header 不繼續下面的邏輯
if len(value) == 0:
continue # 略過空白行
if value[0] == '':
continue # 連股票代號都沒有的，略過
if stockIdMap.get(value[0], "") == "上櫃":
stockDataMap[value[0]] = None # 本來是想放名稱，但這裡沒有
elif stockIdMap.get(value[0], "") == "":
print("{} 非上市上櫃目前不支援".format(value[0]))
lineTool.lineNotify(token, "{} 非上市上櫃目前不支援".format(value[0]))
def main():
rowList = []
for filename in os.listdir("data"):
sid = filename.split(".")[0]
row = process(sid)
if row != None:
rowList.append(row)
# sid = "00636K"
# process(sid)
# TODO 過濾只有 SHEET 裡有的再印
googlehseetService = GooglesheetService("")
rowList = sorted(rowList, key=lambda row: row[3], reverse=True)
print("---------------------")
for row in rowList:
print("{}\t{} {}\t{}".format(row[0], row[1], row[2], row[3]))
pass
def processNotifyNews():
# sheetDataList = fetchAllSheetDataListFromMyGooglehseet()
notifySidList = []
pingNotifySidList = []
sharonNotifySidList = []
# maybe fetch googlesheet
googlesheetService = GooglesheetService(
"1F3cT6ltHQ7gOYxCPSrPJGvMpUt3b5mRJIMR0gJ5ITr8")
rowList = googlesheetService.getValues("新聞通知清單")
for row in rowList:
if len(row) == 0 or row[0] == '' and row[1] == '':
continue # 略過空白行
notifySidList.append(row[0])
print(notifySidList)
# 平大
time.sleep(2)
googlesheetService = GooglesheetService(
"1xgtt4xjZh4Nsg6_uQnuphIbMLrBSLM-0OsOuz93NZAc")
pingRowList = googlesheetService.getValues("新聞通知清單")
for row in pingRowList:
if len(row) == 0 or row[0] == '' and row[1] == '':
continue # 略過空白行
pingNotifySidList.append(row[0])
print(pingNotifySidList)
# 雪人
time.sleep(2)
googlesheetService = GooglesheetService(
"1u5QaL_uyfXhom9iHVMXF1mX8D-Ssdc3zOSGFeS-Z5tk")
sharonRowList = googlesheetService.getValues("新聞通知清單")
for row in sharonRowList:
if len(row) == 0 or row[0] == '' and row[1] == '':
continue # 略過空白行
sharonNotifySidList.append(row[0])
print(sharonNotifySidList)
with open("news.csv", "r", encoding="utf-8") as f1:
csvRowList = list(csv.reader(f1))
for csvRow in csvRowList:
if csvRow[6] != "W":
continue
msg = "{} {} {} {} {}\n\n{}".format(csvRow[0], csvRow[1],
csvRow[2], csvRow[3],
csvRow[4], csvRow[5])
print("process notify...")
print(msg)
if csvRow[0] in notifySidList:
print("notify me")
notifyLineMsg(os.environ["LINE_TEST_TOKEN"], msg)
if csvRow[0] in pingNotifySidList:
print("notify ping")
notifyLineMsg(os.environ["LINE_PING_TOKEN"], msg)
if csvRow[0] in sharonNotifySidList:
print("notify sharon")
notifyLineMsg(os.environ["LINE_SHARON_TOKEN"], msg)
csvRow[6] = "Done"
csvRowList = csvRowList[-1000:]
# 寫回 csv 檔
with open("news.csv", "w", encoding="utf-8", newline="") as f1:
csv.writer(f1).writerows(csvRowList)
'''
fetch fanPageData on google sheet and notify
Created on 2018年3月19日
@author: rocky.wang
'''
from googleService import GooglesheetService
import requests
import json, os
import lineTool
import time
import datetime
sheetService = GooglesheetService(
"1bydUUYVTxyyz2jxm3JRzIQkdElNthLHuQpzW6-DLVUk")
def main():
print("\n執行時間 {}".format(
datetime.datetime.now().strftime('%Y/%m%d %H:%M:%S')))
for rangeName in sheetService.getRangeNameList():
if rangeName == "粉絲頁列表":
continue
print("process notify step in rangeName: ", rangeName)
rowList = sheetService.getValues(rangeName)
# get not notify topics
with open("0000withRSVK9.csv") as f1:
for row in csv.reader(f1):
rowList.append(row)
# 初使前面幾筆沒有 K 值為 50
for row in rowList:
if row[0] == "日期":
continue
row[0] = row[0].strip()
row[0] = "{}/{}/{}".format(
int(row[0].split("/")[0]) + 1911, row[0].split("/")[1],
row[0].split("/")[2])
if row[6] == '':
row[6] = 50
rowListMap = {}
for row in rowList:
rowListMap[row[0]] = row
print(rowListMap)
sheetService = GooglesheetService(
"1033HVmaLyxYkfiX889L5J4ypBuw9xvowotKGPtXWRV0")
rangeName = "T00"
sheetService.clearSheet(rangeName)
sheetService.updateSheet(rangeName, rowList)
openPrice = Column(Float)
highPrice = Column(Float)
lowPrice = Column(Float)
closePrice = Column(Float)
diffPrice = Column(String) # 漲跌價差
def __repr__(self):
return "StockPrice => {}, {}, {}, {}, {}, {}, {}".format(
self.stockId, self.txDate, self.txCount, self.txAmount,
self.txMoney, self.closePrice, self.diffPrice)
Base.metadata.create_all(engine)
# operate https://docs.google.com/spreadsheets/d/1033HVmaLyxYkfiX889L5J4ypBuw9xvowotKGPtXWRV0/edit#gid=0
googlesheetService = GooglesheetService(
"1033HVmaLyxYkfiX889L5J4ypBuw9xvowotKGPtXWRV0")
# get all sheets range name
rangeNameList = googlesheetService.getRangeNameList()
def main():
Session = sessionmaker(bind=engine)
session = Session()
rows = session.query(StockPrice).distinct(StockPrice.stockId).group_by(
StockPrice.stockId)
for row in rows:
processStockToGooglesheet(row.stockId)
'''
寫入公式到 googlesheet 產生所有的基本價格資料
Created on 2018年5月18日
@author: rocky.wang
'''
import csv
from googleService import GooglesheetService
googlesheetService = GooglesheetService(
"1v_h_IGT9gRYqm1jIf4N413k1KqwGRKwxYx0_ww5Tnz0")
def main():
with open("stockIds.csv", encoding="utf-8") as f1:
rowList = list(csv.reader(f1))
cnt2 = 0
rowNum = 1
for row in rowList:
if row[5] != '金融保險業':
continue
# TODO i cannot handle 上櫃 now
if row[4] != '上市':
continue
rowNum += 1
sheetRow = [
row[0],
'''
Created on 2018年6月14日
@author: rocky.wang
'''
from googleService import GooglesheetService
gid = "1F3cT6ltHQ7gOYxCPSrPJGvMpUt3b5mRJIMR0gJ5ITr8"
rangeName = "掃描清單"
googlesheetService = GooglesheetService(gid) # init service by spreadsheetId
rowList = googlesheetService.getValues(rangeName)
rowNum = 0
for row in rowList:
rowNum += 1
if rowNum == 1:
continue
name = row[0]
url = row[1].replace("https://docs.google.com/spreadsheets/d/",
"").split("/")[0]
token = row[2]
print(name, url, token)
'''
檢查粉絲頁列表，建立尚未建立的 sheet
https://docs.google.com/spreadsheets/d/1bydUUYVTxyyz2jxm3JRzIQkdElNthLHuQpzW6-DLVUk/edit#gid=1244591370
Created on 2018年3月19日
@author: rocky.wang
'''
from googleService import GooglesheetService
import json, os, requests
googlesheetService = GooglesheetService("1bydUUYVTxyyz2jxm3JRzIQkdElNthLHuQpzW6-DLVUk") # init service by spreadsheetId
def main():
# 抓取
rowList = googlesheetService.getValues("粉絲頁列表")
cnt = createSheetIfNotExist(rowList)
if cnt > 0:
googlesheetService.updateSheet("粉絲頁列表", rowList)
print("update sheet rows amount %s" %(cnt))
else:
print("no sheet need to create")
def createSheetIfNotExist(rowList):
# 抓取 spredsheet 所有分頁名稱
rangeNameList = googlesheetService.getRangeNameList()
'''
Created on 2018年4月9日
@author: rocky.wang
'''
from googleService import GooglesheetService
import csv
googlesheetService = GooglesheetService(
"1AaYDVOacDO60MgPTf0Taf4k-ZGIupL9bJM7ovMTIAhM")
cnt = 0
for value in googlesheetService.getValues("工作表1"):
cnt += 1
try:
with open("data/{}.csv".format(value[1]), encoding="MS950") as f1:
row = list(csv.reader(f1))[-1]
if float(row[6]) < float(value[0]):
diff = float(value[0]) - float(row[6])
pect = round(diff / float(row[6]) * 100, 2)
if pect > 30:
print("{} {}\t目標價: {}\t現價: {}\t({}%)".format(
value[1], value[2], value[0], row[6], pect))
else:
# print(value)
pass
except:
# print(value[1], "no file")
pass
def processSheet(sheetId, sheetName, notifyLineToken):
googlesheetService = GooglesheetService(sheetId)
rowNum = 0
rowList = []
msg = ""
for value in googlesheetService.getValues(sheetName):
rowNum += 1
rowList.append(value) # 之後資料要回寫回 sheet
# header
if rowNum == 1:
if len(value) <= 10:
value.append("")
columnNum = len(value) # 取 header 的總 column 數
value[10] = datetime.datetime.now().strftime('%m%d %H:%M:%S')
continue # header 不繼續下面的邏輯
if len(value) == 0:
continue # 略過空白行
# 明細列若欄位不足，先補齊，避免 exception 發生
if len(value) < columnNum:
for i in range(columnNum - len(value)):
value.append("")
if value[0] == '':
continue # 連代號都沒有，其他卻還有 #N/A 的值，大家還是會亂搞，防呆
# 開始比價
try:
nowPrice = float(value[4].replace(",", ""))
except:
print("something wrong", value[4])
print(value)
nowPrice = 5000.0
wantPrice = value[2]
# if nowPrice != "" and wantPrice != "" and nowPrice <= float(wantPrice):
if wantPrice != "" and nowPrice <= float(wantPrice):
if value[10] != datetime.datetime.now().strftime('%Y%m%d'):
msg += "\n{} ({}) 買進價 {}，現價 {}，PE: {}，買進原因： {}\n".format(value[1], value[0], value[2], value[4], value[8], value[9])
value[10] = datetime.datetime.now().strftime('%Y%m%d')
if stockIdMap.get(value[0], "") == "上櫃":
# value[3] = '=(E{}-C{})/C{}'.format(rowNum, rowNum, rowNum)
# value[4] = '=IFERROR(ARRAY_CONSTRAIN(importXML(CONCATENATE("http://m.wantgoo.com/s/", $A{}),"//*/div[2]/div/div[1]"),1,1))'.format(rowNum)
# value[5] = 'N/A'
# value[6] = 'N/A'
# value[7] = 'N/A'
# value[8] = 'N/A'
# print("上櫃資料改由別隻處理")
pass
elif stockIdMap.get(value[0], "") == "上市":
value[3] = '=(E{}-C{})/C{}'.format(rowNum, rowNum, rowNum)
value[4] = '=GOOGLEFINANCE(CONCATENATE("TPE:", $A{}), "price")'.format(rowNum)
value[5] = '=GOOGLEFINANCE(CONCATENATE("TPE:", $A{}), "change")'.format(rowNum)
value[6] = '=GOOGLEFINANCE(CONCATENATE("TPE:", $A{}), "changepct") / 100'.format(rowNum)
value[7] = '=GOOGLEFINANCE(CONCATENATE("TPE:", $A{}), "volume") / 1000'.format(rowNum)
value[8] = '=GOOGLEFINANCE(CONCATENATE("TPE:", $A{}), "pe")'.format(rowNum)
elif stockIdMap.get(value[0], "") == "":
print("{} 非上市上櫃不支援".format(value[0]))
lineTool.lineNotify(notifyLineToken, "{} 非上市上櫃目前不支援".format(value[0]))
# 其實應該只要更新時間欄位就好，其他欄位不要再更新，但我懶的再改了，之後再說
googlesheetService.updateSheet(sheetName, rowList)
if msg != '':
print("notify msg => {}".format(msg), flush=True)
lineTool.lineNotify(notifyLineToken, msg)
https://docs.google.com/spreadsheets/d/1Zgl32clbWwwOTOP9mW9eOHiE8uojXmRgVjxvxQvjscM/edit?usp=sharing
Created on 2018年3月20日
@author: Rocky
'''
import os
from googleService import GooglesheetService
import csv
filenames = os.listdir()
filenames = [filename for filename in filenames if filename.endswith(".csv")]
print(filenames)
googlesheetService = GooglesheetService(
"1Zgl32clbWwwOTOP9mW9eOHiE8uojXmRgVjxvxQvjscM")
filenames.reverse()
for filename in filenames:
rowList = [[
"市場", "代碼", "股票名稱", "股利所屬年度", "股東會日期", "除息交易日", "除息參考價(元)", "除權交易日",
"除權參考價(元)", "現金股利發放日", "現金股利盈餘", "現金股利公積", "現金股利合計", "股票股利盈餘",
"股票股利公積", "股票股利合計", "股利合計"
]]
with open(filename, encoding="utf-8") as f1:
print(filename)
for row in csv.reader(f1):
rowList.append(row)