def JohansenTest(self, instruments, quotes): data = pd() for instrument in instruments : data[instrument] = quotes[instrument]["AdjClose"] jres = johansen.coint_johansen(data, 0, 1) #johansen.print_johan_stats(jres) return jres
def get_new_token_dp(cmp_id, g_db): # Модуль обновления токена в в АМО old_token = g_db.get(f'select * from tokens where wf_cmp_id = {cmp_id}') d_token = {i: e[0] for i, e in dict(old_token).items()} url = d_token['amo_domain'] + 'oauth2/access_token' data = json.dumps({ "client_id": d_token['client_id'], "client_secret": d_token['client_secret'], "grant_type": "refresh_token", 'redirect_uri': d_token['redirect_uri'], "refresh_token": d_token['refresh_token'] }) token_new = json.loads( requests.post(url, headers={ "Content-Type": "application/json" }, data=data).text) d_token['token'] = token_new['access_token'] if d_token['token'] != token_new['access_token']: changes_pd = pd( [['token', "'" + d_token['token'] + "'", 'wf_cmp_id', cmp_id]], columns=[ 'changed_cols', 'changed_values', 'case_cols', 'case_vals' ]) update_query = g_db.update_df_q(changes_pd, 'tokens')[0] g_db.put(update_query) if d_token['refresh_token'] != token_new['refresh_token']: changes_pd = pd([[ 'refresh_token', "'" + token_new['refresh_token'] + "'", 'wf_cmp_id', cmp_id ]], columns=[ 'changed_cols', 'changed_values', 'case_cols', 'case_vals' ]) update_query = g_db.update_df_q(changes_pd, 'tokens')[0] g_db.put(update_query) return d_token
def getdata(query): conn = pyodbc.connect(driver) try: data = pd(query, conn) except: data = None conn.commit() conn.close() return data
def import_csv_data(token): global v csv_file_path = askopenfilename() if csv_file_path == '': return v.set(csv_file_path) df = pd(csv_file_path, header=1) df = df[df['Record Type'] == 1].reset_index() upload_to_gamebus(token, df)
def saveCSVFile(self, data): # Opening the file using native file handler csv_column = ["---", "Position", "InTangent", "OutTangent"] my_df = pd(data) #Saving the file the macronizations try: my_df.to_csv(self.fileCSV, index=False, header=csv_column) grable.broadcastError("Success") except IOError: grable.progressUpdate(-1) grable.broadcastError("Please close the csv file first")
def create_custom_fields(self, company_id): lead_data_to_post = [{ 'name': 'Спецификация', "sort": 510, 'type': 'textarea' }, { 'name': 'Тип оплаты', "sort": 511, 'type': 'text' }, { 'name': 'Тип доставки', "sort": 512, 'type': 'text' }, { 'name': 'Сделка на workface', "sort": 513, 'type': 'text' }, { 'name': 'Комментарий покупателя', "sort": 514, 'type': 'text' }] custom_flds = self.post_data( 'leads/custom_fields', lead_data_to_post)['_embedded']['custom_fields'] cnt_custom_to_post = [{ 'name': 'Телефон', 'sort': 515, 'type': 'textarea' }, { 'name': 'Email', 'sort': 516, 'type': 'textarea' }] custom_flds += self.post_data( 'contacts/custom_fields', cnt_custom_to_post)['_embedded']['custom_fields'] companies_custom_to_post = [{ 'name': 'Адрес', 'sort': 515, 'type': 'text' }, { 'name': 'Реквизиты', 'sort': 515, 'type': 'text' }] custom_flds += self.post_data( 'companies/custom_fields', companies_custom_to_post)['_embedded']['custom_fields'] df_custom = pd(custom_flds) drop_columns = [ 'code', 'is_api_only', 'enums', 'request_id', 'required_statuses', 'is_deletable', 'remind', '_links', 'group_id', 'is_predefined', 'sort' ] df_custom = df_custom.drop(drop_columns, axis=1) df_custom['wf_company_id'] = company_id return df_custom
def select(clean, hierarchy): #default values, will update if values present in excel file timeDecayF = 30 #volatile, drop this number - days unchanged > 30 gets killed stdDevTol = 1.28 #tolerance for number of std devs staleF = 10 #standard in Trovo factorImport = open(glob(r'\\xsqnfs2.nyc.ime.reuters.com\TRPS\Bank Loans\Auto 2.0\Factor*.csv')[0], 'r') readFactors = csv.reader(factorImport) for row in readFactors: if "Time" in row[0] and row[1] not in (None, "", " "): timeDecayF = float(row[1]) if "Standard" in row[0] and row[1] not in (None, "", " "): stdDevTol = float(row[1]) if "Stale" in row[0] and row[1] not in (None, "", " "): staleF = float(row[1]) factorImport.close() hierarchyDict = {} for row in hierarchy.iterrows(): #inserting dealer rankings into dictionary hierarchyDict.update({row[1][0] : row[1][1]}) finalFile = pd() clean.insert(len(clean.columns), 'DealerW', 0) #initializing dealer weight clean.insert(len(clean.columns), 'UnchangedW', 0) #initializing days unchanged weight clean.insert(len(clean.columns), 'StaleW', 0) #initializing stale weight clean.insert(len(clean.columns), 'Weight', 0) #initializing weights clean.insert(len(clean.columns), 'StdDev', 0) #initializing stdDev clean.insert(len(clean.columns), 'Calculated Bid', 0) #initializing avg bid clean.insert(len(clean.columns), 'Calculated Offer', 0) #initializing avg offer select = len(clean.columns) clean.insert(select, 'Select', 0) #initializing selection column staleIndex = clean.columns.tolist().index('Not Stale') clean.iloc[:, select] = clean.iloc[:, staleIndex] #copying over initial select values from stale values columns = clean.columns.tolist() stdDevIndex = columns.index('StdDev') weightIndex = columns.index('Weight') dealerIndex = columns.index('dealer') lastBidIndex = columns.index('last_bid') lastOfferIndex = columns.index('last_offer') colorIndex = columns.index('Color') unchangedIndex = columns.index('unchanged_for') calcBid = columns.index('Calculated Bid') calcOffer = columns.index('Calculated Offer') days_stale = columns.index('days_stale') dealerWIndex = columns.index('DealerW') unchangedWIndex = columns.index('UnchangedW') staleWIndex = columns.index('StaleW') bidDate = columns.index('last_bid_date') lins = clean.groupby('lin') #grouping quotes by their LINs hasColor = False #color is midpoint now = timeComp.now() year = now.year month = now.month day = now.day for group, quotes in lins: #iterating through each LIN to do quote selection colorQuote = 0 #color quote price midpoint = 0 #midpoint price midpointW = 0 #midpoint weight stdDev = 0 #variable to get std dev hasColor = False numIncluded = 0 #number of quotes considered stdDevList = [] # keep track of bids to avoid double dataFrame loop tempFrame = pd() bestStaleBid = 0 bestStaleAsk = 0 bidPx = 0 offerPx = 0 stdDevDen = 0 bestStaleW = 0 if len(quotes) == 1: #if only quote, must use it for row in quotes.iterrows(): data = row[1].to_frame().transpose() #skipping over index column data.iloc[0, select] = 1 dealer = data.iloc[0, dealerIndex] lastBid = data.iloc[0, lastBidIndex] weight = 0 dealerCheck = hierarchyDict.get(dealer) #dealer val staleVal = data.iloc[0, days_stale] #increment days stale (yesterday's data) daysStale = 0 if nancheck(staleVal): daysStale = 1 else: daysStale = staleVal if dealerCheck == None: dealerW = 0.75 else: dealerW = 1 - (hierarchyDict.get(dealer) - 1)*.05 #dealer weighting unchVal = data.iloc[0, unchangedIndex] #increment days unchanged (yesterday's data) daysU = 0 if nancheck(unchVal): daysU = 1 elif unchVal < daysStale: #error checking because Trovo isn't good daysU = daysStale else: daysU = unchVal #increment days unchanged (yesterday's data) updateW = 0 if timeDecayF < 1: date = data.iloc[0, bidDate] if daysStale != 0: updateW = 0.25 else: diff = 0 diff += 60 * date.hour diff += now.minute - date.minute updateW = (timeDecayF * 400) / (diff + timeDecayF * 400) #400 is max in a trading day, will act nicely elif daysU >= 8 * timeDecayF: #days unchanged cutoff updateW = 0.2 else: #days unchanged weighting updateW = (3 * timeDecayF) / (daysU + (3 * timeDecayF)) '''need size weight''' staleW = (3 * staleF) /(daysStale + (3 * staleF)) #days stale weight weight = dealerW * updateW * staleW data.iloc[0, dealerWIndex] = '{0:.5g}'.format(dealerW) #dealer weighting data.iloc[0, staleWIndex] = '{0:.5g}'.format(staleW) #stale weighting data.iloc[0, unchangedWIndex] = '{0:.5g}'.format(updateW) #unchanged weighting data.iloc[0, weightIndex] = '{0:.5g}'.format(weight) #final weighting tempFrame = concat([tempFrame, data.iloc[0, :].to_frame().transpose()]) midpoint = data.iloc[0, lastBidIndex] bidPx = midpoint offerPx = data.iloc[0, lastOfferIndex] else: for row in quotes.iterrows(): #otherwise, iterate through all quotes and do selection process data = row[1].to_frame().transpose() #skipping over index column dealer = data.iloc[0, dealerIndex] lastBid = data.iloc[0, lastBidIndex] weight = 0 if isinstance(dealer, float): #has color, mark it- dealer is NaN data.iloc[0, lastOfferIndex] = data.iloc[0, lastBidIndex] #need to set offer price for calculations if hasColor: #if two with color, make midpoint average of two colorQuote = (colorQuote + lastBid) / 2 else: hasColor= True colorQuote = lastBid weight = 1 data.iloc[0, colorIndex] = 1 data.iloc[0, dealerWIndex] = '{0:.5g}'.format(1) #dealer weighting data.iloc[0, staleWIndex] = '{0:.5g}'.format(1) #stale weighting data.iloc[0, unchangedWIndex] = '{0:.5g}'.format(1) #unchanged weighting data.iloc[0, weightIndex] = '{0:.5g}'.format(weight) #final weighting else: dealerCheck = hierarchyDict.get(dealer) #dealer val staleVal = data.iloc[0, days_stale] #increment days stale (yesterday's data) daysStale = 0 if nancheck(staleVal): daysStale = 1 else: daysStale = staleVal if dealerCheck == None: dealerW = 0.75 else: dealerW = 1 - (hierarchyDict.get(dealer) - 1)*.05 #dealer weighting unchVal = data.iloc[0, unchangedIndex] #increment days unchanged (yesterday's data) daysU = 0 if nancheck(unchVal): daysU = 1 data.iloc[0, unchangedIndex] = 1 elif unchVal < daysStale: #error checking because Trovo isn't good daysU = daysStale data.iloc[0, unchangedIndex] = daysU else: daysU = unchVal #increment days unchanged (yesterday's data) updateW = 0 if timeDecayF < 1: try: date, time, timeZone = data.iloc[0, bidDate].split(" ") if int(date.split("-")[0]) != year or int(date.split("-")[1]) != month or int(date.split("-")[2]) != day: updateW = 0.25 else: diff = 0 time = time.split(":") diff += 60 * (now.hour - int(time[0])) diff += now.minute - int(time[1]) updateW = (timeDecayF * 400) / (diff + timeDecayF * 400) #400 is max in a trading day, will act nicely except: updateW = 0.25 #either no date of quote or it was before we kept track of time, very old elif daysU >= 8 * timeDecayF: #days unchanged cutoff updateW = 0.2 else: #days unchanged weighting updateW = (3 * timeDecayF) / (daysU + (3 * timeDecayF)) '''need size weight''' '''IF THERE IS SIZE< TIGHTEN STD DEV''' staleW = (3 * staleF) /(daysStale + (3 * staleF)) #days stale weight weight = dealerW * updateW * staleW data.iloc[0, dealerWIndex] = '{0:.5g}'.format(dealerW) #dealer weighting data.iloc[0, staleWIndex] = '{0:.5g}'.format(staleW) #stale weighting data.iloc[0, unchangedWIndex] = '{0:.5g}'.format(updateW) #unchanged weighting data.iloc[0, weightIndex] = '{0:.5g}'.format(weight) #final weighting if data.iloc[0, select] == 1: if weight > midpointW: midpoint = lastBid midpointW = weight numIncluded += 1 #increment n if weight > 0.6 or dealerW == 1: stdDevList.append(lastBid) stdDevDen += 1 if weight > bestStaleW: bestStaleBid = lastBid bestStaleAsk = data.iloc[0, lastOfferIndex] bestStaleW = weight tempFrame = concat([tempFrame, data]) if hasColor: #if color, auto midpoint midpoint = colorQuote if numIncluded == 0: #all stale if len(quotes) > 2: numCalc = np(True, tempFrame['unchanged_for'], 0) midpointUnch = min(numCalc) midpointDraw = np(midpointUnch == tempFrame['unchanged_for'], tempFrame['last_bid'], 0) midpoint = max(midpointDraw) stdDev = 2 tempFrame.iloc[:, select] = np((abs(tempFrame['last_bid'] - midpoint) <= (stdDev*stdDevTol)), 1, 0) numCalc = np((tempFrame['Select'] == 1), tempFrame['last_bid'], 0) numInc = nonZ(numCalc) bidPx = sum(numCalc)/numInc offerPx = sum(np((tempFrame['Select'] == 1), tempFrame['last_offer'], 0))/numInc else: tempFrame.iloc[:, select] = np((abs(tempFrame['last_bid'] - midpoint) <= (stdDev*stdDevTol)), 1, 0) bidPx = bestStaleBid offerPx = bestStaleAsk midpoint = bestStaleBid stdDev = 0 else: '''IF THERE IS SIZE, TIGHTEN STD DEV, else do everything below''' if stdDevDen == 0: stdDev = .25 #make very tight parameter but allow for depth else: stdDev = ((sum([(x - midpoint)**2 for x in stdDevList])/(stdDevDen))**(1/2)) if stdDev < 0.25: stdDev = 0.25 if stdDev > 5.0: if midpoint < 80: stdDev = 5.0 else: stdDev = 3.0 elif stdDev > 3.0: if midpoint >= 80: stdDev = 3.0 tempFrame.iloc[:, select] = np((abs(tempFrame['last_bid'] - midpoint) <= (stdDev*stdDevTol)) & tempFrame['Not Stale'] == 1, 1, 0) numCalc = np((tempFrame['Select'] == 1), tempFrame['last_bid'], 0) numInc = nonZ(numCalc) if numInc < 3 and stdDev == 0.25: tempFrame.iloc[:, select] = np(((abs(tempFrame['last_bid'] - midpoint) <= (0.25*stdDevTol))), 1, 0) #too tight, add depth, stdDev 0.25 numCalc = np((tempFrame['Select'] == 1), tempFrame['last_bid'], 0) numInc = nonZ(numCalc) bidPx = sum(numCalc)/numInc offerPx = sum(np((tempFrame['Select'] == 1), tempFrame['last_offer'], 0))/numInc tempFrame.iloc[:, stdDevIndex] = stdDev tempFrame.iloc[:, calcBid] = '%.3f' % bidPx tempFrame.iloc[:, calcOffer] = '%.3f' % offerPx finalFile = concat([finalFile, tempFrame]) return finalFile
def get_for_tickers(self, tickers): _, from_date, till_date = self.parameterService.init_params( self.timeframe) table = [] for ticker_record in tickers: ticker_name = ticker_record['name'] ticker_id = ticker_record['id'] if ticker_name in self.cache: ticker_data = self.cache[ticker_name] else: ticker_data = self.tickerRateService.get_rate( ticker_name, from_date, till_date) #self.cache[ticker] = ticker_data #enable cache for debug if ticker_data is not None: ticker_data = self.ticketAnalysisService.analyze_dataframe( ticker_data) simmul_data = self.priceChangeSimulationService.get_simmulation_data( ticker_data, weeks=self.weeks_to_simul) lr_data = self.linearRegressionSerice.calculate_slope_and_rsquare_kernel( simmul_data["weeks"], simmul_data["prices"]) slope_index = None if lr_data["slope"] > 0: slope_index = 1 if lr_data["slope"] < 0: slope_index = -1 last_index = 0 if simmul_data['last'] > simmul_data['start']: last_index = (simmul_data['last'] - simmul_data['start']) / simmul_data['start'] elif simmul_data['last'] < simmul_data['start']: last_index = (simmul_data['start'] - simmul_data['last']) / simmul_data['start'] record = { "id": ticker_id, "ticker": ticker_name, "start": simmul_data['start'], "low": simmul_data['low'], "median": simmul_data['median'], "high": simmul_data['high'], "last": simmul_data['last'], "slope": lr_data["slope"], "slope_index": slope_index, "last_index": last_index, "r_squared": lr_data["r_squared"], "rs_measure": self.linearRegressionSerice.rsquare_group( lr_data["r_squared"]), "delete_link": "<a href='/stock_game/delete?id={}'>delete</a>".format( ticker_id) } table.append(record) if len(tickers) > 0: df = pd.from_records(table) df = df.sort_values(by=['rs_measure', 'slope_index', 'last_index'], ascending=[0, 0, 0]) df = df.reset_index(drop=True) df = df.reset_index() df = df.reindex_axis([ "index", "ticker", "start", "high", "last", "slope", "r_squared", "delete_link" ], axis=1) df["index"] += 1 df.columns = [ "rank", "ticker", "start", "high", "last", "slope", "r_squared", "delete_link" ] else: df = pd(data=[], columns=[ "rank", "ticker", "start", "high", "last", "slope", "r_squared", "delete_link" ]) return df
def __init__(self): self.path = dirname(__file__) self.locale = setlocale(LC_ALL, 'ru_RU') self.morph = MorphAnalyzer() self.read_file = pd(join(self.path, 'Регистрация.xlsx'), na_filter=False).to_dict('index') self.count = len(self.read_file) - 1
integrated_client.append(dict(i[1])) for client in integrated_client[:1]: g_db.reopen() client_json = client.copy() supplier_company_id = client_json['supplier_company_id'] token = get_new_token_dp(supplier_company_id, g_db) amo_connect = get_AMO(token['token'], token['amo_domain']) dicts_amo = amo_connect.get_data( "account?with=pipelines,custom_fields,users") pips = amo_connect.pipiline_loc(dicts_amo['_embedded']['pipelines']) if 'Воронка WorkFace' not in [pips[i][0] for i in pips]: creat_states_map = amo_connect.creat_new_funnels2(supplier_company_id) df_funnles = pd(creat_states_map, columns=['wf_status', 'amo_fld_id', 'company_id']) qu_add_new_funnels = g_db.insert_pd(df_funnles, 'funnels') g_db.put(qu_add_new_funnels) fields_pd = amo_connect.create_custom_fields(supplier_company_id) qu_add_custom_fields = g_db.insert_pd(fields_pd, 'custom_fields') g_db.put(qu_add_custom_fields) names = ['Интернет-Магазин', 'Workface'] data = [] for i in names: data.append({'name': i}) tags_ids = amo_connect.post_data('leads/tags', data) tags_pd = pd(tags_ids['_embedded']['tags']) tags_pd = tags_pd.drop(columns=['request_id']) tags_pd['company_id'] = supplier_company_id q_tags = g_db.insert_pd(tags_pd, 'tags') g_db.put(q_tags)