def getValuesForApp4(self, CIK, session=None): if (session is None): dbconnector = DBConnector() session = dbconnector.getNewSession() params = {'CIK': CIK} query = text( """ select conceptName, value, endDate, explicitMemberValue from (select concept.conceptName, efv.value, p.instant as endDate, em.explicitMemberValue FROM fa_entity_fact ef join fa_entity_fact_value efv on ef.oid = efv.entityFactOID join fa_concept concept on ef.conceptOID = concept.OID join fa_period p on p.oid = efv.periodOID join fa_file_Data fd on fd.oid = efv.fileDataOID join fa_company c on c.oid = fd.companyOID join fa_ticker t on t.companyOID = c.OID join fa_explicit_member em on em.oid = efv.explicitMemberOID where c.CIK = :CIK union select 'PRICE', pri.value, p.instant as endDate, null as explicitMemberValue from fa_price pri join fa_period p on p.oid = pri.periodOID join fa_file_Data fd on fd.oid = pri.fileDataOID join fa_company c on c.oid = fd.companyOID join fa_ticker t on t.companyOID = c.OID where c.CIK = :CIK) as a order by conceptName, explicitMemberValue""") return session.execute(query, params)
def getStatusList(self, statusAttr, session=None): dbconnector = DBConnector() if (session is None): session = dbconnector.getNewSession() query = session.query(FileData)\ .with_entities(getattr(FileData, statusAttr).distinct()) objectResult = query.all() return objectResult
def getExpressionList2(self, isCurrent, session=None): if (session is None): dbconnector = DBConnector() session = dbconnector.getNewSession() objectResult = session.query(Expression)\ .filter(Expression.isCurrent == isCurrent)\ .all() return objectResult
def getAllTicker(self, session=None): if (session is None): dbconnector = DBConnector() session = dbconnector.getNewSession() objectResult = session.query(Ticker)\ .with_entities(Ticker.ticker)\ .all() return objectResult
def getTickerLike(self, tickerLike, session=None): if (session is None): dbconnector = DBConnector() session = dbconnector.getNewSession() objectResult = session.query(Ticker)\ .filter(Ticker.ticker.like(tickerLike))\ .all() return objectResult
def getErrorKeyList(self, session=None): dbconnector = DBConnector() if (session is None): session = dbconnector.getNewSession() query = session.query(ErrorMessage)\ .with_entities(ErrorMessage.errorKey.distinct()) objectResult = query.all() return objectResult
def getLastFileData(self, session): dbconnector = DBConnector() if (session is None): session = dbconnector.getNewSession() query = session.query(func.max(FileData.fileName))\ .with_entities(FileData.fileName)\ .group_by(FileData.companyOID) objectResult = query.all() return objectResult
def getFirstResult(self, objectClazz, condition, session=None): if (session is None): dbconnector = DBConnector() session = dbconnector.getNewSession() objectResult = session.query(objectClazz)\ .filter(condition)\ .first() return objectResult
def getExpressionForReport(self, session=None): if (session is None): dbconnector = DBConnector() session = dbconnector.getNewSession() objectResult = session.query(Expression)\ .join(Expression.customConcept)\ .with_entities(CustomConcept.conceptName, Expression.defaultOrder, Expression.periodType, Expression.expression)\ .all() return objectResult
def getEntityFactValueList(self, fileDataOID, conceptOID, session): if (session is None): dbconnector = DBConnector() session = dbconnector.getNewSession() query = session.query(EntityFactValue)\ .join(EntityFactValue.fileData)\ .filter(and_(Concept.OID.__eq__(conceptOID), EntityFactValue.fileDataOID == fileDataOID)) objectResult = query.all() return objectResult
def getCompanyListForReport(self, session=None): if (session is None): dbconnector = DBConnector() session = dbconnector.getNewSession() query = session.query(Company)\ .join(Company.tickerList)\ .with_entities(Company.CIK, Company.entityRegistrantName, Company.listed, Company.notListedDescription,Ticker.ticker, Ticker.tickerOrigin, Ticker.active) objectResult = query.all() return objectResult
def getQuarterPeriodList(self, session=None): dbconnector = DBConnector() if (session is None): session = dbconnector.getNewSession() query = session.query(QuarterPeriod)\ .with_entities(QuarterPeriod.OID, functions.concat(QuarterPeriod.year, '-' ,QuarterPeriod.quarter))\ .order_by(QuarterPeriod.year.desc(), QuarterPeriod.quarter.desc()) objectResult = query.all() return objectResult
def getErrorList(self, fileName, session=None): dbconnector = DBConnector() if (session is None): session = dbconnector.getNewSession() query = session.query(FileData)\ .outerjoin(FileData.errorMessageList)\ .order_by(ErrorMessage.errorKey)\ .filter(FileData.fileName == fileName)\ .with_entities(ErrorMessage.errorKey, ErrorMessage.errorMessage) return query.all()
def getCustomFact3(self, customConceptOID, customReportOID, session): try: if (session is None): dbconnector = DBConnector() session = dbconnector.getNewSession() objectResult = session.query(CustomFact)\ .filter(and_(CustomFact.customReportOID == customReportOID, CustomFact.customConceptOID == customConceptOID))\ .one() return objectResult except NoResultFound: return None
def getCompanyListByTicker(self, tickerList, session=None): if (session is None): dbconnector = DBConnector() session = dbconnector.getNewSession() query = session.query(Company)\ .join(Company.tickerList)\ .filter(and_(Ticker.ticker.in_(tickerList), Ticker.active == True))\ .with_entities(Company.CIK, Company.entityRegistrantName, Ticker.ticker) objectResult = query.all() return objectResult
def getFileDataList4(self, ticker, session=None): dbconnector = DBConnector() if (session is None): session = dbconnector.getNewSession() query = session.query(FileData)\ .join(FileData.company)\ .join(Company.tickerList)\ .with_entities(Company.CIK, Ticker.ticker, FileData.fileName, FileData.documentPeriodEndDate, FileData.documentType, FileData.documentFiscalYearFocus, FileData.documentFiscalPeriodFocus, FileData.fileStatus, FileData.companyStatus, FileData.entityStatus, FileData.priceStatus, FileData.factStatus, FileData.copyStatus, FileData.calculateStatus, FileData.expressionStatus)\ .order_by(FileData.documentPeriodEndDate)\ .filter(Ticker.ticker == ticker) objectResult = query.all() return objectResult
def getFileData6(self, statusAttr, statusValue, session=None, limit=None): """get FD by one attribute""" dbconnector = DBConnector() if (session is None): session = dbconnector.getNewSession() query = session.query(FileData)\ .order_by(FileData.documentPeriodEndDate)\ .filter(and_(getattr(FileData, statusAttr) == statusValue))\ .with_entities(FileData.fileName)\ .limit(limit) objectResult = query.all() return objectResult
def getCConceptAndConcept(self, session = None): if (session is None): dbconnector = DBConnector() session = dbconnector.getNewSession() query = session.query(CustomConcept)\ .join(CustomConcept.relationConceptList)\ .join(RelCustomConceptConcept.concept)\ .join(CustomConcept.defaultCustomReport)\ .with_entities(CustomReport.shortName,CustomConcept.conceptName.label("CustomConceptName"), Concept.conceptName, RelCustomConceptConcept.order_, RelCustomConceptConcept.customConceptOID, RelCustomConceptConcept.conceptOID)\ .order_by(CustomReport.shortName, CustomConcept.conceptName, RelCustomConceptConcept.order_) objectResult = query.all() return objectResult
def getErrorMessageGroup(self, errorKey, statusKey, session=None): dbconnector = DBConnector() if (session is None): session = dbconnector.getNewSession() query = session.query(ErrorMessage)\ .join(ErrorMessage.fileData)\ .filter(ErrorMessage.errorKey == errorKey)\ .with_entities(getattr(FileData, statusKey), ErrorMessage.errorMessage, func.count().label('Count'))\ .order_by(ErrorMessage.errorMessage)\ .group_by(FileData.companyStatus, ErrorMessage.errorMessage) objectResult = query.all() return objectResult
def getFileData5(self, statusAttr, statusValue, session=None, limit=None, errorMessage2 = ''): """get FD by one attribute and error message""" dbconnector = DBConnector() if (session is None): session = dbconnector.getNewSession() query = session.query(FileData)\ .outerjoin(FileData.errorMessageList)\ .order_by(FileData.documentPeriodEndDate.desc())\ .filter(and_(getattr(FileData, statusAttr) == statusValue, or_(errorMessage2 == '', ErrorMessage.errorMessage.like(errorMessage2))))\ .with_entities(FileData.fileName)\ .limit(limit) objectResult = query.all() return objectResult
def getAllResult(self, objectClazz, condition=(1 == 1), session=None, limit=None): if (session is None): dbconnector = DBConnector() session = dbconnector.getNewSession() objectResult = session.query(objectClazz)\ .filter(condition)\ .limit(limit)\ .all() return objectResult
def getFileDataByError(self, errorKey, errorMessage, session=None, limit=None): """get FD by error key and error message""" dbconnector = DBConnector() if (session is None): session = dbconnector.getNewSession() query = session.query(FileData)\ .outerjoin(FileData.errorMessageList)\ .order_by(FileData.documentPeriodEndDate.desc())\ .filter(and_(ErrorMessage.errorKey== errorKey, ErrorMessage.errorMessage.like(errorMessage)))\ .with_entities(FileData.fileName)\ .limit(limit) objectResult = query.all() return objectResult
def getFirstEntityFact(self, fileDataOID, conceptName, session): if (session is None): dbconnector = DBConnector() session = dbconnector.getNewSession() try: query = session.query(EntityFactValue)\ .join(EntityFactValue.explicitMember)\ .filter(and_(Concept.conceptName.__eq__(conceptName), EntityFactValue.fileDataOID == fileDataOID))\ .order_by(ExplicitMember.order_) objectResult = query.first() except NoResultFound: objectResult = None return objectResult
def getFileData3(self, statusAttr, statusValue, statusAttr2, statusValue2, session=None, limit=None, listed = ''): """get FD by two attributes and listed""" dbconnector = DBConnector() if (session is None): session = dbconnector.getNewSession() query = session.query(FileData)\ .join(FileData.company)\ .order_by(FileData.documentPeriodEndDate)\ .filter(and_(getattr(FileData, statusAttr) == statusValue, getattr(FileData, statusAttr2) == statusValue2, or_(listed == '', Company.listed == listed)))\ .with_entities(FileData.fileName)\ .limit(limit) objectResult = query.all() return objectResult
def getCustomFactValue4(self, companyOID, periodType, documentFiscalYearFocus, session = None): if (session is None): dbconnector = DBConnector() session = dbconnector.getNewSession() query = session.query(CustomFactValue)\ .join(CustomFactValue.customFact)\ .join(CustomFactValue.fileData)\ .join(CustomFactValue.period)\ .filter(and_(FileData.documentFiscalYearFocus == documentFiscalYearFocus, \ FileData.companyOID == companyOID, \ Period.type.__eq__(periodType)))\ .with_entities(CustomFactValue.value, CustomFactValue.periodOID, Period.endDate, FileData.documentFiscalYearFocus, FileData.documentFiscalPeriodFocus, CustomFactValue.fileDataOID, CustomFact.customConceptOID)\ .order_by(FileData.documentPeriodEndDate) objectResult = query.all() return objectResult
def getCustomFactValue5(self, fillStrategy = '', ticker = '', session = None): try: if (session is None): dbconnector = DBConnector() session = dbconnector.getNewSession() objectResult = session.query(CustomFactValue)\ .join(CustomFactValue.customFact)\ .join(CustomFactValue.fileData)\ .join(CustomFact.customConcept)\ .join(FileData.company)\ .join(Company.tickerList)\ .filter(and_(or_(fillStrategy == '', CustomConcept.fillStrategy.__eq__(fillStrategy)), or_(ticker == '', Ticker.ticker.__eq__(ticker))))\ .all() return objectResult except NoResultFound: return None
def getFactValue3(self, periodTypeList=None, fileDataOID=None, session=None): dbconnector = DBConnector() if (session is None): session = dbconnector.getNewSession() objectResult = session.query(FactValue)\ .join(FactValue.factList)\ .join(FactValue.period)\ .join(Fact.fileData)\ .filter(and_(Period.type.in_(periodTypeList), \ FileData.OID == fileDataOID))\ .with_entities(FactValue.value, FactValue.periodOID, Period.endDate, FileData.documentFiscalYearFocus, FileData.documentFiscalPeriodFocus, Fact.fileDataOID, Fact.conceptOID, Period.type)\ .all()#.distinct()\#.order_by(Period.endDate)\ return objectResult
def getOneResult(self, objectClazz, condition=(1 == 1), session=None, raiseNoResultFound=True): if (session is None): dbconnector = DBConnector() session = dbconnector.getNewSession() try: objectResult = session.query(objectClazz)\ .filter(condition)\ .one() except NoResultFound as e: if (raiseNoResultFound): raise Exception(str(e) + " " + str(objectClazz)) return None return objectResult
def getEntityFactValueForReport(self, CIK, session=None): if (session is None): dbconnector = DBConnector() session = dbconnector.getNewSession() try: query = session.query(EntityFactValue)\ .join(EntityFactValue.explicitMember)\ .join(EntityFactValue.fileData)\ .join(EntityFactValue.period)\ .join(FileData.company)\ .filter(and_(Company.CIK.__eq__(CIK)))\ .order_by(func.ifnull(Period.endDate, Period.instant),ExplicitMember.order_)\ .with_entities(func.date_format(func.ifnull(Period.endDate, Period.instant), '%Y-%m-%d').label("period"),func.format(EntityFactValue.value, 0).label("value"), ExplicitMember.explicitMemberValue, ExplicitMember.order_) objectResult = query.all() except NoResultFound: objectResult = None return objectResult
def importMasterIndexFor(self, period, replaceMasterFile, session=None, threadNumber=1): dbconnector = DBConnector() if (session is None): session = dbconnector.getNewSession() localURL = Constant.CACHE_FOLDER + 'master' + str( period.year) + "-Q" + str(period.quarter) + '.gz' secURL = "https://www.sec.gov/Archives/edgar/full-index/" + str( period.year) + "/QTR" + str(period.quarter) + "/master.gz" print(localURL, secURL) file = getBinaryFileFromCache(localURL, secURL, replaceMasterFile) with gzip.open(BytesIO(file), 'rb') as f: file_content = f.read() text = file_content.decode("ISO-8859-1") text = text[text.find("CIK", 0, len(text)):len(text)] point1 = text.find("\n") point2 = text.find("\n", point1 + 1) text2 = text[0:point1] + text[point2:len(text)] df = pandas.read_csv(StringIO(text2), sep="|") df.set_index("CIK", inplace=True) df.head() print("STARTED FOR PERIOD " + str(period.year) + "-" + str(period.quarter)) for row in df.iterrows(): CIK = row[0] filename = row[1]["Filename"] formType = row[1]["Form Type"] if (formType == "10-Q" or formType == "10-K"): fd = FileDataDao.getFileData(filename, session) if (fd is None): company = CompanyEngine().getOrCreateCompany( CIK=CIK, session=session) fd = FileData() fd.fileName = filename fd.company = company Dao().addObject(objectToAdd=fd, session=session, doCommit=True) print("FD Added " + filename) print("FINISHED")