Created
September 17, 2019 10:19
-
-
Save shahab67/1c1cb0edcc8f80d84a22b814c56af894 to your computer and use it in GitHub Desktop.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
import requests | |
from openpyxl import Workbook | |
import time | |
start_time = time.time() | |
from json import loads | |
import json | |
import gspread | |
from slackclient import SlackClient | |
import os | |
from oauth2client.service_account import ServiceAccountCredentials | |
import xlrd | |
#داده های دیده بان بازار را در یک فایل اکسل به اسم Data میریزد | |
url='http://members.tsetmc.com/tsev2/excel/MarketWatchPlus.aspx?d=0&format=0' | |
r=requests.get(url) | |
f = open("data.xlsx", 'wb') | |
f.write(r.content) | |
book = Workbook() | |
sheet = book.active | |
f.close() | |
#داده های فایل اکسل data را از روی کامپیوتر فراخوانی میکند | |
workbook = xlrd.open_workbook("C:\\Users\\ShahaB\\.PyCharmCE2018.3\\config\\scratches\\data.xlsx") | |
sheet = workbook.sheet_by_index(0) | |
nrows=sheet.nrows | |
#paste name and price in google sheet | |
# use creds to create a client to interact with the Google Drive API | |
scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive'] | |
creds = ServiceAccountCredentials.from_json_keyfile_name('My Project-d85f8f9bb0dd.json', scope) | |
client = gspread.authorize(creds) | |
# Find a workbook by name and open the first sheet | |
gsheet = client.open_by_key('1CpOYRWZL02QJTCYf0BssqTsdhJV-olpBDAsOD7mKk-A').worksheet('stock_data') | |
#شماره آخرین ستون دارای مقدار را می گیرد | |
num_col= len(gsheet.row_values(10)) | |
#نام نماد و قیمت پایانی را در دو لیست میریزد | |
name=[] | |
price=[] | |
cellList =[] | |
m=0 | |
# flist=['شوينده','غفارس','وتوصا'] | |
for i in range(0, nrows-2): | |
data1=sheet.cell_value(2+i, 0).replace('ك','ک').replace('ي','ی') | |
data2=sheet.cell_value(2+i, 10) | |
cellList.append(gspread.Cell(5 + i,num_col+1,data1)) | |
cellList.append(gspread.Cell(5 + i, num_col+2, data2)) | |
# name.append(data1) | |
# price.append(data2) | |
# gsheet.update_cell(1+i,1,name[i]) | |
# gsheet.update_cell(1+i,2,price[i]) | |
gsheet.update_cells(cellList) | |
#درج تاریخ و ساعت در اولین سلول | |
gsheet.update_cell(4,num_col+1,sheet.cell_value(1, 0)[16:26]) | |
gsheet.update_cell(4,num_col+2,sheet.cell_value(1, 0)[48:]) | |
print("--- %s overall seconds ---" % round((time.time() - start_time),2)) | |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment