o
    (i^                    @   s  d dl Z d dlZd dlZd dlmZmZmZmZmZm	Z	m
Z
mZ d dlmZmZmZmZ d dlmZmZ d dlmZ d dlmZmZmZ d dlmZmZmZ d dlmZmZm Z m!Z!m"Z" d d	l#m$Z$m%Z%m&Z&m'Z'm(Z(m)Z)m*Z*m+Z+ d d
l,m-Z- d dl.m/Z/ d dl0m1Z1 d dl2m3Z3 d dl4Z4d dl5Z5d dl6Z6d dl7m8Z8 d dl9m:Z:m;Z;m<Z< d dl=Z>d dl?Z@d dlmAZA d dlBmCZC d dlDmEZE d dl5Z5d dlFZFd dlmZmGZGmZm	Z	 d dl9m:Z: d dlHmIZI d dlmZ d dlmZ d dlJZJd dlKZLd dlMmNZN ejOjPjQejRd e ZSeSTeE eSUe1jV eSjTe/dgddgdgd e4WdZXe4WdZYdd  ZZd!Z[d"d# Z\d$d% Z]eS^d&eeZfd'e_d(e_d)e_d*e_d+e_d,efd-d.Z`eSad/ed0ed1ed1edeeZfd)e_d(e_d*e_d+e_d2eee_  d,efd3d4ZbeSad5d6d7 ZceSad8d9d: ZdeSad;d<d= ZeeeZfd,efd>d?ZfeSad@ed0ed1ed1ededeeZfd(e_d*e_d+e_d2eee_  dAeee_  d,efdBdCZgeSadDed0ed1ed1ededeeZfd(e_d*e_d+e_d2eee_  dAeee_  d,efdEdFZhdGdH ZieSadIeeZfd,efdJdKZjeSadLed1eeZfdMe_d,efdNdOZkddQdRZlddSdTZmddUdVZndWdX ZoddYdZZpdd\d]Zqd^d_ ZreSad`ed1eeZfd(e_d*e_d+e_dae_dbee_ d,efdcddZseSadedfdg Ztdhedie@jufdjdkZvdldm ZweS^dned1eeZfdoed,efdpdqZxeS^dred1eeZfdoed,efdsdtZyeSadueeZfd,efdvdwZzeSadxeeZfd,efdydzZ{eSad{eeZfd,efd|d}Z|eSad~eeZfd,efddZ}eS^ded1eeZfdoed,efddZ~eS^ded1eeZfdoed,efddZd dlmZmZmZmZ de _dd Zdd Zdd Zdd Zdd Zdd Zdd Zdd Zdd Zdd Zdd Zdd Zdd Zdd Zdd Zdd Zdd Zdd Zdd ZdddZdd Zdd ZeS^deded1ed1e
dedeeZfdAeee_  de_de_deee_ef  d2eee_  d,efddZde_de_fddZdd Zde_die_fddÄZeeZfd,efddńZdddɄZeS^dʡdefdd̄ZdS )    N)FastAPIDependsQuery
UploadFileFileHTTPExceptionBodylogger)ListOptionalDictAny)datetime	timedelta)and_)Sessionsessionmakerrelationship)modelsdatabasecrud)fetch_kpi_dataparse_dotnet_dategenerate_date_rangefind_continuous_rangesstore_kpi_response)
InstrumentKPIKPIValueMarketExchngesMarketSectorIndexPriceValueInstrumentPriceValue)SessionLocal)CORSMiddleware)admin_routes)	KPIFilter)IntegrityError)JSONResponseFileResponseStreamingResponse)func)NamedTemporaryFile)!ComprehensiveAPILoggingMiddleware)r   Requestr   r   )r)   )BaseHTTPMiddleware)r   )r   )t)bind*T)allow_originsallow_credentialsallow_methodsallow_headersDATASTREAM_API_USERNAMEDATASTREAM_API_PASSWORDc                  c   s*    t  } z
| V  W |   d S |   w N)r   r$   closedb r>   +/var/www/html/stock_analysis/be/app/main.pyget_db=   s
   r@   z?https://product.datastream.com/DSWSClient/V1/DSService.svc/restc                  C   sJ   t jt dttdd} |   |  }|d}|s#td| |S )Nz/Tokenusernamepassword)params
TokenValuezFailed to fetch token: )requestsgetAPI_BASErB   rC   raise_for_statusjson
ValueError)responsedatatokenr>   r>   r?   	get_tokenF   s   

rO   c                  C   s*   z
t jttd} | W S  ty   Y d S w )NrA   )ds
DatastreamrB   rC   	Exception)DSr>   r>   r?   get_datastreamR   s   rT   z/fetch-kpis/rN   	frequencysymbol
start_dateend_dater=   c              
   C   s  dd | tj D }| ddidd |D |||dd|ddid	d
dgd}t| |}td t| |d d }	dd |	d D }
|	d d d d d }| tjj|d }|sttj|d}|	| |
  || |	d D ]m}|d }|d d d }|d d d}| tjj|d }|stj|d}|	| |
  || t|
|D ].\}}|d u rq| tjj|j|j||d }|stj|j|j||||d}|	| qqx|
  ddiS )Nc                 S      g | ]}|j qS r>   code.0kr>   r>   r?   
<listcomp>\       zfetch_kpis.<locals>.<listcomp>RequestAllMetadataTc                 S      g | ]	}|d didqS 
ReturnNameTValue
Propertiesr>   r]   r[   r>   r>   r?   r_   b          StartEnd	FrequencyKindrd   re    	DataTypesDater   TagrE   rg   DataRequestsz<=============== Response from DataStream API ===============DataResponsesr   c                 S   s   g | ]}t |qS r>   )r   r]   dr>   r>   r?   r_   v       DatesDataTypeValuesSymbolValuesSymbolrV   DataTyperf   CurrencyrZ   )instrument_idkpi_idrU   date)r   r   rU   r   valuecurrencymessagezKPI data fetched and stored.)queryr   r   allr   printr   	filter_byfirstaddcommitrefreshrG   zipr   id)rN   rU   rV   rW   rX   r=   	kpi_codesbodyrL   rM   datesinstrument_symbol
instrumententrykpi_codevaluesr   kpir   r   existingkpi_valr>   r>   r?   
fetch_kpisY   s~   





r   z/kpis/Q.kpisc              	      s   t  }t|d }t|d }ddddd}	|	|d}
|r_dd  t D td	 fd
d|D }td| |D ]} tj	|d
 sY t||d qC   ndd  t D } tj	| d
 }|st| d} |     |  fdd|D } tjtj|jktj|ktj|tj|ktj|ktjttjt|k }dd |D td t|||}td| tfdd|D }td| |r)t||}td| |D ]/\}}|ddidd |D | | |
dd| ddidd d!gd"}t||}t  | qi }|D ]D} tj	|d
 }|s?q- ttj|jktj|jktj|ktj|ktj|k!tj }d#d |D ||j"po|j#< q-| |t$|t$||d$}|S )%N%Y-%m-%dDailyMonthly	QuarterlyYearlyDMr   Yc                 S      i | ]
}|j p	|j|jqS r>   namer[   r\   r>   r>   r?   
<dictcomp>       zget_kpis.<locals>.<dictcomp>zName to Code Mapping:c                       g | ]}  ||qS r>   rG   r]   r   name_code_mapr>   r?   r_          zget_kpis.<locals>.<listcomp>zKPI Codes to Fetch:rZ   r[   r   c                 S   rY   r>   rZ   r\   r>   r>   r?   r_      r`   r   c                    @   g | ]}  tjj|d  dur  tjj|d  qS rZ   Nr   r   r   r   scalarrh   r<   r>   r?   r_          c                 S      h | ]}|d  qS r   r>   rx   r>   r>   r?   	<setcomp>   rz   zget_kpis.<locals>.<setcomp>zExisting Dates in DB:zFull Date Range:c                       g | ]}| vr|qS r>   r>   rx   existing_datesr>   r?   r_      r   zMissing Dates:zChunks:ra   Tc                 S   rb   rc   r>   rh   r>   r>   r?   r_      ri   rj   rk   rd   re   rp   rq   ru   c                 S   "   g | ]}t |j|j|jd qS r   r   r   strr   r   r   r]   rr>   r>   r?   r_   	      )rV   rU   rW   rX   r   )%rO   r   strptimer   rG   r   r   r   r   r   r   r   r   r   r   r   filterr   r   rU   r   in_group_byhavingr,   countlenr   sortedr   	isoformatr   r   order_byr   r[   r   )rV   rU   rW   rX   r   r=   rN   startendfreq_mapexternal_freqr   r[   r   kpi_idsdb_dates
full_rangemissing_dateschunkschunk_start	chunk_endr   rL   kpi_datar   recordsr>   r=   r   r   r?   get_kpis   s   	








"









r   z/stocks_details/c                  C   .   t  } | t }dd |D }|   |S )Nc              
   S   sF   g | ]}|j |j|j|j|j|jr|jjnd |jr|jjnd dqS )N)r   rV   r   	market_id	sector_idmarket_namesector_name)r   rV   r   r   r   marketsectorr]   instr>   r>   r?   r_      s    
zget_stocks.<locals>.<listcomp>)r$   r   r   r   r;   )r=   instrumentsresultr>   r>   r?   
get_stocks  s   
r   z/market_list/c                  C   r   )Nc                 S      g | ]	}|j |jd qS r   r   r   )r]   r   r>   r>   r?   r_   3      z#get_market_list.<locals>.<listcomp>)r$   r   r    r   r;   )r=   marketsr   r>   r>   r?   get_market_list/     r   z/sector_list/c                  C   r   )Nc                 S   r   r   r   )r]   r   r>   r>   r?   r_   C  r   z#get_sector_list.<locals>.<listcomp>)r$   r   r!   r   r;   )r=   sectorsr   r>   r>   r?   get_sector_list?  r   r   c           #   
      s  t |d }t |d }h d}h d}	h d}
i dddgdd	d
gdg ddddgdddgdddgdddgdg ddddgdddgd d!dgd"g d#d$d%d&gd'g d(d)d*d+gd,d-d.g}h d/}h d0}d'd$h}|| |	| |
| g }|rd1d2  t D fd3d4|D }|d d  D ]'}||v r|| || || D ]}|v r||  qqt	d5 qnd6d4  t D }d7d2  t D i }|r|D ]} t
j|d8 } fd9d4|D } tjtj|jktj| ktj|tj|ktj|ktjttjt|k }d:d; |D t| ||}tfd<d4|D }|rt }t|| }|D ]0\}}|d=d>id?d4 |D | | | d@dA|dBd>idCdDdEgdF}t||}t | q[i } |D ]R} tj|dG }!|!sq ttj|jktj|!jktj| ktj|ktj|k tj }"|"rdHd4 |"D | |!j!p|!j"< qd | |!j!p|!j"< qt	dI|  t#| ||||| } ||| i qt	dJ| |||
||	fS )KNr   >   PEDIODSO	EVToSales
EVToEBITDADebtEquityRatioOperatingMargin>   EPSPOUTPEGRatio	MarketCap
EPSDiluted
QuickRatioOCFPerShareCurrentRatioEBITDAMarginDividendYieldEPSGrowthRateReturnOnAssetsReturnOnEquityEnterpriseValueNetProfitMarginInterestCoverageBookValuePerShare>   BetaPTBVAlphaPrice52WeekLowPrice52WeekHighNetDebtToEBITDANetDebtEBITDADPOCOGSAverageInventoryCashConversionCycle)r   r   r  r  FCFfcfcapex
EBITMarginebitdarevenueAssetTurnovertotal_assetsInventoryTurnovercogs	inventoryTangibleBVPSequityintangible_assetsgoodwillshares_outstanding	ForwardPEdwfcexpected_epsPriceToSalesprice_per_sharesales_per_shareGrossMargingross_incomeFCFYieldr  r  
market_capShortInterestRatiosiduvoSharpeRatiomsdpdryvolInsiderOwnershipPctshares_owned_by_insidersr*  InstitutionalOwnershipPctnoshouwc05475>   r  r+  r.  r  r  >	   r  r3  r  r1  r%  r   r"  r>  r@  c                 S   r   r>   r   r\   r>   r>   r?   r     r   z-get_stock_kpi_calculation.<locals>.<dictcomp>c                    r   r>   r   r   r   r>   r?   r_     r   z-get_stock_kpi_calculation.<locals>.<listcomp>falsec                 S   rY   r>   rZ   r\   r>   r>   r?   r_     r`   c                 S   r   r>   r   r\   r>   r>   r?   r     r   r   c                    r   r   r   rh   r<   r>   r?   r_     r   c                 S   r   r   r>   rx   r>   r>   r?   r     rz   z,get_stock_kpi_calculation.<locals>.<setcomp>c                    r   r>   r>   rx   r   r>   r?   r_     r   ra   Tc                 S   rb   rc   r>   rh   r>   r>   r?   r_     ri   rj   rk   rd   re   rp   rq   ru   rZ   c                 S   r   r   r   r   r>   r>   r?   r_     r   r   stock_wise_kpi_data)$r   r   r   updater   r   r   appendremover   r   r   r   r   r   r   r   rU   r   r   r   r   r,   r   r   r   r   rO   r   r   r   r   r   r   r[   dynamic_calculate_kpis)#rU   rW   rX   r   symbolsr=   r   r   lowerhigherNA_KPISKPI_DEPENDENCIEScalculated_lowercalculated_highercalculated_nacalculation_listr   r[   deprD  rV   r   r   r   r   r   rN   r   r   r   r   rL   r   r   r   r>   r   r?   get_stock_kpi_calculationN  s   
	










"




	

rS  z/stock_performance_calculation/rI  c           :         s*  t dd |D }i }t t|}t| ||||\}}}	}
}}t|d }t|d }|D ]}|tj|d	 }|j
j}|j
j}|ttj|j
jktj|ktj|ktj }|rstdd |D d}n+t }|j|dg||d	 }|js| D ]\}}|t|j
j||d
 q|  |jrtd|jd }|jd }|| | d }|ttj |jktj|ktj|ktj }|rtdd |D d}n+t }|j|dg||d	 }|js| D ]\}}|t|j||d q|  g }|jr|!|d d d d d n|jd }|jd }|| | d } | | }!|t"|dt"|dt"| d|t"|dt"|dt"|dt"|!dd	}"fdd|#|i pbi $ D }#t }$|% D ]&}%|%D ] }&|&|
% v r|
$ D ]\}'}(|(|&kr|$|'  nqqrqn|#$ D ]u\})}*|)|$v rq|)|	v rd|"|) d< d|"|) d< q|*sd|"|) d< d|"|) d< qt|*}+t&|+d |+d< |+'dj(dd}+|+d jd },|+d jd }-|,d u s|-d u rq|-|, }.t"|.d}/|/|"|) d< qt)d|# |!|" t|*d}0|| t+|t+||0j,d d!d"}1|-||1i q2g }2|$ D ]\}}3|2.|3d#  q?t|2}0|0j/D ]k}4|40d$r|4d%vrtj1|0|4 d&d'|0|4< |0|4 2  rwqT|43dd(})|)|v r|0|4 j4dd)d*5d+|0|4 d,< qT|)|v r|0|4 j4d-d)d*5d+|0|4 d,< qT|0|4 j4d-d)d*5d+|0|4 d,< qT|0d. j4d-d)d*5d+|0d/< |06 D ])\}5}6|6d0 }7|$ D ]\}8}3|3d# d d0 |7kr|3d# d -|6,  qސq҇ fd1d2 |t7|||
d3}9 |9}9t)|9 |9S )4Nc                 S   $   h | ]}| d D ]}| q	qS ,splitstripr]   itemsr>   r>   r?   r        $ z4get_stock_performance_calculation.<locals>.<setcomp>r   r   c                 S   r   r   r   r_  r   r>   r>   r?   r_     ri   z5get_stock_performance_calculation.<locals>.<listcomp>r   PI	date_fromdate_tomarket_exchange_for_idr   r   zNo index data found!)r   r   )r   d   c                 S   r   r^  r_  r   r>   r>   r?   r_   1  ri   Pinstrument_for_idr   r   )TickerStart Price	End PriceReturn %zExcess vs ATX %   )	rk  rl  rm  rn  
Index NamezIndex Startz	Index EndzIndex Return %Total Score %c                    s   i | ]\}}| v r||qS r>   r>   r]   r^   v)r   r>   r?   r   Z  s    z5get_stock_performance_calculation.<locals>.<dictcomp>NAz Score %z Score % RankT)dropr   r   rf  zkpi_data-----r   orient)rV   rU   rW   rX   stock_with_performancerx  zScore %)rq  coerce)errorsrp   dense)	ascendingmethodInt64z RankFrq  Rankrk  c                    sX   t | tr fdd|  D S t | tr fdd| D S t | tr*t| r*d S | S )Nc                    s   i | ]	\}}| |qS r>   r>   rr  
clean_nansr>   r?   r     ri   zIget_stock_performance_calculation.<locals>.clean_nans.<locals>.<dictcomp>c                       g | ]} |qS r>   r>   r]   ir  r>   r?   r_     rz   zIget_stock_performance_calculation.<locals>.clean_nans.<locals>.<listcomp>)
isinstancedictitemslistfloatmathisnan)objr  r>   r?   r    s   

z5get_stock_performance_calculation.<locals>.clean_nans)stock_performance_datarD  )8r  setrS  r   r   r   r   r   r   r   market_exchange
index_code
index_namer"   r   re  r   r   r   pd	DataFrame	set_indexrT   fetchdropnaempty
itertuplesr   r   rK   ilocr#   rj  rF  roundrG   r  r   to_datetimesort_valuesreset_indexr   fillnar   to_dictrE  extendcolumnsendswith
to_numericisnareplacerankastypeiterrows$filter_dependency_kpis_from_response):rU   rW   rX   r   rI  r=   response_datarD  rM  rL  r   rJ  rK  r   r   rV   r   index_symbolr  index_recordsdf_indexrS   ry   valindex_start	index_endindex_returnstock_recordsdf_stockperformancestart_price	end_pricestock_returnexcess_returnall_datar   all_dependency_codesdepsrR  r   r[   kpi_namer   df_kpi	start_valend_val
abs_change	kpi_scoreperf_dfcalculation_responserowsdetailscol_rowtickersymr   r>   )r  r   r?   !get_stock_performance_calculation  s@  
























&
&"
r  z"/download_stock_performance_excel/c                 C   s   t | |||||d}|d }g }| D ]\}	}
||
d  qt|}t }tj|dd}|j|ddd W d    n1 sCw   Y  |	d	 d
di}t
|d|dS )N)rU   rW   rX   r   rI  r=   r  rx  openpyxlengineStockPerformanceF)
sheet_nameindexr   zContent-Dispositionz+attachment; filename=stock_performance.xlsxAapplication/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
media_typeheaders)r  r  r  r  r  ioBytesIOExcelWriterto_excelseekr+   )rU   rW   rX   r   rI  r=   r   r  r  rV   r  dfoutputwriterr  r>   r>   r?    download_stock_performance_excel  s*   



r  c                 C   s   t  }| D ]!}|D ]}|| v r'| D ]\}}||kr&||  nqqqi }|  D ]\}	}
i }|
 D ]\}}||vrE|||< q9|||	< q/|S )z\
    Filter out dependency KPIs from the response, keeping only calculated/derived KPIs
    )r  r   r  r   )rD  kpi_dependenciesr   r  r  rR  r   r[   filtered_datarV   r   filtered_kpi_datar  r   r>   r>   r?   r    s(   

r  z/kpi-options/c                 C   s   |  t }dd |D S )Nc                 S   s    g | ]}|j |jp|j d qS )r   r   r\   r>   r>   r?   r_          z#get_kpi_options.<locals>.<listcomp>)r   r   r   )r=   r   r>   r>   r?   get_kpi_options  s   r  z/symbol-search/r   c                 C   s8   | ttjd|  dd }dd |D S )N%
   c                 S   r   )rV   r   r  r  r>   r>   r?   r_      ri   z!symbol_search.<locals>.<listcomp>)r   r   r   rV   ilikelimitr   )r   r=   resultsr>   r>   r?   symbol_search  s   r  positivec                 C   s   dd | D }t ||k rdS |dkrt||||S |dkr#t||S |dkr.t||||S |dkr8t|||S |dkrBt|||S dS )	Nc                 S   s   g | ]
}|j d ur|j qS r:   r   r]   rs  r>   r>   r?   r_   $  r   z)check_trend_condition.<locals>.<listcomp>Fconsecutive_growthnegative_to_positive
yoy_growthpost_transition_growthabsolute_threshold)r   check_consecutive_growthcheck_negative_to_positivecheck_yoy_growthcheck_post_transition_growthcheck_absolute_threshold)r   
trend_typequarters	threshold	direction	data_listr>   r>   r?   check_trend_condition#  s   
r  c                    s4   |dkrt  fdd| D S t  fdd| D S )Nr  c                 3   s     | ]}|d uo| kV  qd S r:   r>   r]   r   r  r>   r?   	<genexpr>A      z+check_absolute_threshold.<locals>.<genexpr>c                 3   s     | ]}|d uo| kV  qd S r:   r>   r  r  r>   r?   r  C  r   )any)r  r  r  r>   r  r?   r  ?  s   r  c                    sx   t | k rdS dd t| | dd  D |dkr!fdd nfdd t fd	d
tt  d D S )NFc                 S   s<   g | ]\}}|d ur|d ur|dkr|| t | d qS )Nr   rg  )abs)r]   prevcurrr>   r>   r?   r_   I  s    z,check_consecutive_growth.<locals>.<listcomp>rj   negativec                    s
   |   kS r:   r>   gr  r>   r?   <lambda>P  s   
 z*check_consecutive_growth.<locals>.<lambda>c                    s   |  kS r:   r>   r  r  r>   r?   r  R      c                 3   s6    | ]}t  fd d|| d  D V  qdS )c                 3   s    | ]} |V  qd S r:   r>   )r]   r  )comparer>   r?   r  U      z5check_consecutive_growth.<locals>.<genexpr>.<genexpr>rj   N)r   r  )r
  growth_listr  r>   r?   r  T  s
    $
z+check_consecutive_growth.<locals>.<genexpr>ro  )r   r   r  range)r  r  r  r  r>   )r
  r  r  r  r?   r  E  s   r  c                 C   s   t | |k rdS tt | | d D ]+}| |||  }|d }tdd |d | D r=tdd ||d  D r= dS qdS )NFrj   ro  c                 s   s     | ]}|d uo|dk V  qd S Nr   r>   r]   xr>   r>   r?   r  a  r   z-check_negative_to_positive.<locals>.<genexpr>c                 s   s     | ]}|d uo|dkV  qd S r  r>   r  r>   r>   r?   r  a  s    
T)r   r  r  r   )r  r  r  segmenthalfr>   r>   r?   r  Z  s   "
r  c                 C   s   t | |d k r
dS d}tdt | D ]P}| | d urc| |d  d urc| |d  dkrc| | | |d   t| |d   d }|dkrM|| krM|d7 }n|dkrZ||krZ|d7 }nd}||krc dS qdS )N   Fr   rg  r  rj   T)r   r  r  )r  r  r  r  r   r  growthr>   r>   r?   r  h  s   ,(

r  ro  c                 C   s   t t| | d D ]i}| | d urs| | dk rs| |d  d urs| |d  dkrsd}t |d |d | D ]8}| | d u sM| |d  d u sM| |d  dkrQd} n| | | |d   t| |d   d }||k rmd} nq5|rs dS q
dS )Nrj   r   TFrg  )r  r   r  )r  quarters_afterr  r  	growth_okjr  r>   r>   r?   r  |  s    8,(r  c                 C   s   |  tjj|d }|sdS |  tjtjj|ktjj|j	ktjj
|ktjj|ktjj|ktjj  }|rCt|dk rEdS |d}	t|dd}
t|dd}|dd	}t||	|
||}|rvt|d
d d}d|fS dS )z&Apply trend logic for advanced filtersrZ   )FNro  trendr  r  r   r  r  c                 S   s   | j S r:   )r   )rs  r>   r>   r?   r    s    z'apply_advanced_filter.<locals>.<lambda>)keyT)r   r   r   r   r   r   r   r   r   r   rU   r   r   ascr   r   rG   intr  r  max)r=   r   r   filter_datarU   rW   rX   r   r   r  r  r  r  passedlatest_valuer>   r>   r?   apply_advanced_filter  s.   




r   z/filter-stocks/logical_operatorfiltersc              
   C   sf  dd |D }t d| |tj }g }|D ]}	g }
d}|D ]}|d }d|v rLt||	j||| ||\}}|rK|d7 }|
||jt	|j
d q!|d	 }t|d
 }|tjj|d }|seq!|tjtjj|	jktjj|jktjj| ktjj
|ktjj
|k}|dkr|tjj|k}nG|dkr|tjj|k}n9|dkr|tjj|k }n+|dkr|tjj|k}n|dkr|tjj|k}n|dkr|tjj|k}nq!|tjj
  }|r|d7 }|
||jt	|j
d q!| dkr|t|kr||	j|
d q| dkr)|dkr)||	j|
d qt d| d|iS )Nc                 S   s   g | ]}t |qS r>   )rJ   loads)r]   fr>   r>   r?   r_         z%filter_stocks_get.<locals>.<listcomp>zlogical_operator ===>r   r   r  rj   )r   r   r   operatorr   rZ   gtgteltlteeqneAND)rV   
kpi_valuesORmatching_resultsrI  )r   r   r   r   r   r   r   rF  r   r   r   r  r   r   r   r   r   r   r   rU   r   descupperr   rV   )rU   rW   rX   r!  r"  r=   parsed_filtersr   r0  r   instrument_kpi_datafilter_pass_countr$  r   r  kpi_value_objr&  r   r   kpi_values_queryr>   r>   r?   filter_stocks_get  s   





	

r8  z/routesc                   C   s   dd t jD S )Nc                 S   rY   r>   )path)r]   router>   r>   r?   r_     r`   zlist_routes.<locals>.<listcomp>)approutesr>   r>   r>   r?   list_routes  s   r=  uploaded_filereturnc              
   C   sP   | j  }ztjt|dd}W |S  ty' } z	tdd| dd }~ww )Nr  r    zCould not read Excel file: status_codedetail)filereadr  
read_excelr  r  rR   r   )r>  contentsr  er>   r>   r?   read_excel_to_df  s   
rI  c                 C   s,   | d u rd S t |  }| dv rd S |S )N>   rp   nannone)r   rY  rJ  )r   r\  r>   r>   r?   safe_str  s   rL  z/api/upload/market-exchangesrD  c                    s  t | }h d}dd |jD  |t   }|r#tdd| d|j fdd|D d}d	d	g }}}| D ]\}}	zit|	d
}
|
sP|d7 }W q<|	t
j|
d }|r|jsht|	d|_|jsst|	d|_|js~t|	d|_|  W q<t
|
t|	dt|	dt|	dd}|| |  |d7 }W q< ty } z|  |t|t|d W Y d }~q<d }~ww |  d|||dS )N>   r  r  r   exchange_namec                 S      i | ]}|  |qS r>   rJ  r]   cr>   r>   r?   r     r%  z+upload_market_exchanges.<locals>.<dictcomp>r@  zMissing columns: rA  c                       i | ]} | |qS r>   r>   r\   	lower_mapr>   r?   r   #  r%  r  r   r  rj   r  r   rM  r  echange_namer  r  rW  r   r  errorTsuccesscreatedskippedrz  )rI  r  r  keysr   renamer  rL  rG   r   r   r   r   r   rW  r  flushr   rR   rollbackrF  r  r   r   )rD  r=   r  expectedmissingr]  r^  rz  idxr  r[   r   mxrH  r>   rS  r?   upload_market_exchanges  sP   
$rg  z/api/upload/instrumentsc                    sf  t | }h d}dd |jD  |t   }|r#tdd| d|j fdd|D d}d	\}}}| D ]g\}}	t|	d
}
t|	d}t|	d}t|	d}t|	d}t|	d}t|	d}t|	d}|
r|r|	 dkr|d7 }t
d|d|dd q9|tj|
d }|st|
dddd}|| |  d }|r|tj|d }|st|d}|| |  d }|r|tj|d }|st|d}|| |  |ttj|k}|r||ttj|k}|r||ttj|k}| }|rg|j|_|r)|j|_|r0|j|_|rHzt||_W n tyG   d |_Y nw |rN||_|rT||_|rZ||_ |d7 }t
d|d|j q9t|j||||||ru|jnd |r||jnd d}|| z
|  |d7 }W q9 t!y   |"  |d7 }Y q9w |#  d|||d}t
| |S )N>   ricr   r   r   rV   r  
short_codemarket_index_codec                 S   rN  r>   rO  rP  r>   r>   r?   r   R  r%  z&upload_instruments.<locals>.<dictcomp>r@  z$Missing columns (case-insensitive): rA  c                    rR  r>   r>   r\   rS  r>   r?   r   V  r%  rU  )r   r   r   rj  rV   ri  rh  r  r   r   r   rJ  rj   updatedzinst.symbolrV  rp   rX  )r   )market_exchange_idri  rV   rh  r  r   r   r   T)r\  r]  rk  r^  )$rI  r  r  r_  r   r`  r  rL  rG   rJ  r   r   r   r   r   r   ra  r!   r    r   r   rV   unionrh  r  r   rl  r   r   r  ri  rK   r   r(   rb  r   )rD  r=   r  rc  rd  insertedr^  rk  r  r  rj  rV   ri  rh  r  r   r   r   rf  semar   r   rL   r>   rS  r?   upload_instrumentsM  s   








rq  z/market-exchangesc              	   C   sX   |  t }td| g }|D ]}td| ||j|j|j|j|j	d qt
|S )Nr  r   )r   r  r  rW  r   )r   r   r   r   rF  r   r  r  rW  r   r)   r=   r  payloadr   r>   r>   r?   list_market_exchanges  s   


rt  z/instrumentsc                 C   s   |  t }g }|D ]3}||j|jr|jnd |jr|jjnd |j|j	|j
|j|j|jr1|jjnd |jr9|jjnd d
 qt|S )N)
r   rl  rj  ri  rV   rh  r  r   r   r   )r   r   r   rF  r   rl  r  r  ri  rV   rh  r  r   r   r   r)   rr  r>   r>   r?   list_instruments  s    
ru  z/marketsc                 C       |  t }tdd |D S )Nc                 S   r   r   r   r   r>   r>   r?   r_     ri   z list_markets.<locals>.<listcomp>)r   r    r   r)   r=   r  r>   r>   r?   list_markets     rx  z/sectorsc                 C   rv  )Nc                 S   r   r   r   r   r>   r>   r?   r_     ri   z list_sectors.<locals>.<listcomp>)r   r!   r   r)   rw  r>   r>   r?   list_sectors  ry  rz  z/api/upload/marketc                 C   sn  z| j  }tjt|dd}W n ty/ } ztdd| dddW  Y d }~S d }~ww dd	 |jD }t	d
| d|vrJtdddddS |d }ddg }}}	|| 
 D ]S\}
}z1t|rl|d7 }W q\t| }|sz|d7 }W q\t||r|d7 }W q\t|| |d7 }W q\ ty } z|	t|
t|d W Y d }~q\d }~ww d|||	dS )Nr  r  FFailed to read Excel: r\  rZ  r@  rB  c                 S      i | ]	}|   |qS r>   rJ  rY  rP  r>   r>   r?   r     ri   z!upload_market.<locals>.<dictcomp>colsr   (Excel file must contain a 'name' column.r   rj   rY  Tr[  )rD  rE  r  rF  r  r  rR   r)   r  r   r  r  r   rY  r   get_market_by_namecreate_marketrF  r  rD  r=   rG  r  rH  r  name_colr]  r^  rz  re  r  r   r>   r>   r?   upload_market  s@   
$

$r  z/api/upload/sectorc                 C   sd  z| j  }tjt|dd}W n ty/ } ztdd| dddW  Y d }~S d }~ww dd	 |jD }d
|vrEtdddddS |d
 }ddg }}}	|| 	 D ]S\}
}z1t
|rg|d7 }W qWt| }|su|d7 }W qWt||r|d7 }W qWt|| |d7 }W qW ty } z|	t|
t|d W Y d }~qWd }~ww d|||	dS )Nr  r  Fr{  r|  r@  r}  c                 S   r~  r>   r  rP  r>   r>   r?   r     ri   z!upload_sector.<locals>.<dictcomp>r   r  r   rj   rY  Tr[  )rD  rE  r  rF  r  r  rR   r)   r  r  r  r   rY  r   get_sector_by_namecreate_sectorrF  r  r  r>   r>   r?   upload_sector  s>   
$
$r  )DecimalDivisionByZeroInvalidOperation
getcontextr  c              	   C   s2   z|dkr
| | W S d W S  t tfy   Y d S w r  )r  r  )	numeratordenominatorr>   r>   r?   safe_divide1  s
   r  c              	   C   s>   z| d u s	| dkrW d S t t| W S  ttfy   Y d S w )Nrp   )r  r   r  rK   r  r>   r>   r?   
to_decimal7  s   r  c                 C   6   |  d}|  d}d ||fv s|dkrd S t||S )NWC18199WC18198r   rG   r  )rM   net_debtr  r>   r>   r?   calc_net_debt_to_ebitda?  
   


r  c                 C   :   |  d}|  d}d ||fv s|dkrd S t||d S )NWC03040WC01051r   m  r  )rM   accounts_payabler#  r>   r>   r?   calc_dpoF  
   

r  c                 C   sr   d}|  dd}|  d}|  d}|  d}d ||||fv s#|dkr%d S t|t||}|d u r3d S || | S )Nr  WC08126r   WC08131r  r  r  )rM   daysdiodsor  r#  dpor>   r>   r?   calc_cccN  s   


r  c                 C   r  )Nr  WC01001r   rg  r  )rM   r  r  r>   r>   r?   calc_ebitda_margin\  r  r  c                 C   s,   |  d}|  d}d ||fv rd S || S )NWC04860WC04601r   )rM   r  r  r>   r>   r?   calc_fcfd  s
   

r  c                 C   r  )Nr  WC02999r   r  )rM   r  r!  r>   r>   r?   calc_asset_turnoverl  r  r  c                 C   r  )Nr  WC02101r   r  )rM   r#  r$  r>   r>   r?   calc_inventory_turnovert  r  r  c                 C   s\   |  d}|  d}|  dd}|  d}d ||||fv s!|dkr#d S || | }t||S )NWC03995WC02649WC18280Rr   WC05301r  )rM   r'  r(  r)  r*  r  r>   r>   r?   calc_tangible_bvps|  s   



r  c                 C   r  )NDWFCWC07250r   r  )rM   r,  r-  r>   r>   r?   calc_forward_pe  r  r  c                 C   r  )Nrh  WC05508r   r  )rM   r/  r0  r>   r>   r?   calc_price_to_sales  r  r  c                 C   r  )NWC01100r  r   rg  r  )rM   r2  r  r>   r>   r?   calc_gross_margin  r  r  c                 C   sN   |  d}|  d}|  d}d |||fv s|dkrd S || }t||d S )Nr  r  WC08001r   rg  r  )rM   r  r  r5  free_cash_flowr>   r>   r?   calc_fcf_yield  s   


r  c                 C   s:   |  d}|  d}d ||fv s|dkrd S t|d |S )NSIDUVOr   i  r  )rM   r7  r8  r>   r>   r?   calc_short_interest_ratio  r  r  c                 C   sF   |  d}|  d}|  d}d |||fv s|dkrd S t|| |S )NMSDPDRYVOLr   r  )rM   r;  r<  r=  r>   r>   r?   calc_sharpe_ratio  s   


r  c                 C   s>   |  d}|  d}d ||fv s|dkrd S t|| d |S )NNOSHOUWC05475r   rg  r  )rM   rA  rB  r>   r>   r?    calc_institutional_ownership_pct  s
   

r  c                 C   r  )NVODWCFr   rg  r  )rM   r?  r*  r>   r>   r?   calc_insider_ownership_pct  r  r  c                    s|  | i }i d fddd fddd fddd fd	dd
 fddd fddd fddd fddd fddd fddd fddd fddd fddd fddd fddd  fd!d}t d"|  d#|  | D ]-\}}z| }|d$ur|||< W q ty } zt d%| d&|  W Y d$}~qd$}~ww t d'|  d#|  |S )(z3
    Calculate KPI values for the given period
    r3  c                         t  S r:   )r  r>   current_datar>   r?   r    r	  z%calculate_kpi_value.<locals>.<lambda>r  c                      r  r:   )r  r>   r  r>   r?   r    r	  r  c                      r  r:   )r  r>   r  r>   r?   r    r	  r  c                      r  r:   )r  r>   r  r>   r?   r    r	  r  c                      r  r:   )r  r>   r  r>   r?   r    r	  r  c                      r  r:   )r  r>   r  r>   r?   r    r	  r   c                      r  r:   )r  r>   r  r>   r?   r    r	  r"  c                      r  r:   )r  r>   r  r>   r?   r    r	  r%  c                      r  r:   )r  r>   r  r>   r?   r    r	  r+  c                      r  r:   )r  r>   r  r>   r?   r    r	  r.  c                      r  r:   )r  r>   r  r>   r?   r    r	  r1  c                      r  r:   )r  r>   r  r>   r?   r    r	  r6  c                      r  r:   )r  r>   r  r>   r?   r    r	  r9  c                      r  r:   )r  r>   r  r>   r?   r    r	  r>  c                      r  r:   )r  r>   r  r>   r?   r    r	  r@  c                      r  r:   )r  r>   r  r>   r?   r    r	  zcalc_map for period z ===> NzError calculating : zresults for period )r   r  rR   )
period_keyr  r  calc_mapr  r,   r  rH  r>   r  r?   calculate_kpi_value  sb   	
r  c              	   C   s  |d u rd S |dkr|\}}	}
|dd|	dd|
d}n|dkr0|\}}	|dd|	dd}nr|dkrd|\}}|dkrC|dd	}n_|d
krN|dd}nT|dkrY|dd}nI|dkrc|dd}n>|dkrs|d }|dd	}n/|\}}|dkr|dd	}n |d
kr|dd}n|dkr|dd}n
|dkr|dd}t d t d| ||||d|  t d ||vrg ||< || |t|dd |S )Nr   04d-02dr   z-01r   rj   -01-01ro  -04-01   -07-01r  -10-01r   r   z[==================================stock data start========================================
zsaving stock data:zfrequency: zY==================================stock data end========================================
rp   r   )r   rF  r  )stockr   r  r   r   rV   rU   r   yearmonthdayquarterr>   r>   r?   save_stock_data  sT   
r  c                 C   s   |  D ]X\}}z5| j||g|||d}	|	 }	|	jrW qg }
|	 D ]\}}|
|dt|| dd q#|
||< W q ty\ } zt	d| d| d|  W Y d }~qd }~ww |S )N)rb  rc  freqr   rp   r   zError fetching z for r  )
r  r  r  r  r  rF  strftimer  rR   r   )rS   kpi_code_datarW   rX   rU   rV   kpi_data_listr^   rs  r  r   r   r  rH  r>   r>   r?   get_kpi_calculation_data!  s(   

r  c              
   C   sf  i }ddddd}| |d}	i dddd	d
ddddddddddddddddddddddddddd d!d"d#d$d%d&d'd(d)d*d+d,d-d.dd/d0ddd1d2d3d4d5d6d7d8d9d:d;d<d=d>d?d@dAdBdC}
i }|D ]}||
v r|
| }t }t|||||||}q{tdD| | D ]g\}}|D ]`}t|dE dF}|dGkr|j|j|j	f}n5|dHkr|j|jf}n*|dIkr|jdJ dK dJ }|j|f}n|dLkr|jf}n|jdJ dK dJ }|j|f}||vri ||< t
t|dM || |< qqtdN| | D ]\}}t||}| D ]\}}t||||| || qqtdO|  | S )PNr   r   r   r   r   r  r  r  )r  r  r  r  r  )AccountsPayableCostOfGoodsSoldr  r  r  )r   r   r  r  r  r  r  )r  r  r  r  )r  r  r   r  )r  r!  r"  r  )r#  r$  r%  r  r  r  r  r&  r+  r  r  )r,  r-  r.  rh  r  )r/  r0  r1  r  )r2  r  r3  r  r4  r6  r  r  )r7  r8  r9  r  r  r  r:  r>  r  r  )r?  r*  r@  r  r  )rA  rB  zkpi_data_list:r   r   r   r   r   rj   r  r   r   zstructured_data:zFinal kpi_data====>)rG   rT   r  r   r  r   r   r  r  r  r  r   r  r  )r   r   rV   rW   rX   rQ  rU   quarterly_datar   r   kpi_data_mappingsr  r  r  rS   entriesr   dtr  r  r   
calculatedr   r>   r>   r?   rH  5  s   






	











rH  z(/stock_quarterly_performance_calculationstart_period
end_periodr   c                 C   sJ   t d| tdd | D } tt| } t d|  t| |||||d}|S )Nzkpis received ===>c                 S   rT  rU  rW  rZ  r>   r>   r?   r     r]  z>get_stock_quarterly_performance_calculation.<locals>.<setcomp>rI  )rI  r  r  r   r   r=   )r   r  r  'stock_quarterly_performance_calculation)rI  r  r  r   r   r=   quarterly_resultsr>   r>   r?   +get_stock_quarterly_performance_calculation  s   
	
r  start_quarterend_quarterc                 C   s   ddddd}|  d\}}t|}|| }t||dd}| d\}}t|}|| d }	t||	d }
t||	|
d}||fS )	Nrj   r     r  )Q1Q2Q3Q4r  r   ro  )rX  r  r   r  calendar
monthrange)r  r  quarter_mapq_start
year_startmonth_startrW   q_endyear_end	month_endlast_dayrX   r>   r>   r?   quarter_to_dates  s   r  c           	      C   s   |  d\}}| d\}}t|dd  t|t|dd  t|f\}}}}g }||}}||k s;||kr`||kr`|d| d|  |d7 }|dkrTd}|d7 }||k s;||kr`||ks;|S )Nr  rj   r   r  )rX  r  rF  )	r  r  start_qstart_yend_qend_yr  r  r  r>   r>   r?   generate_quarter_range  s   4
r  r  c                 C   sR   |  d\}}t|d }t|}|d }|}|dkr!d}|d8 }d| d| S )Nr  rj   r   r  r   rX  r  )r  qr  q_num
prev_q_num	prev_yearr>   r>   r?   get_previous_quarter  s   r  c                    st  | ttj| }| }dd |D }tdd |D }	t|dd }
t|d }|d }|
}|dkr?d}|d8 }d	| d| }d
d }||\}}||\}}|}t	||}g }d	}t
||||| |\}}}}}}i }|D ]s}| tj|d }| ttj|jktj|ktj|ktj } | rtdd | D d}!t|!j|!_d |!j_n*t }"|"j|dg||d }!|!js|! D ]\}#}$|t|j|#|$d q|  |!js|!||< qui }%|	D ]v}| tj|d }&| t t j!|&jkt j|kt j|kt j }'|'r0tdd |'D d}!t|!j|!_d |!j_n*|"j|&j"dg||d }!|!jsZ|! D ]\}#}$|t |&j|#|$d qE|  |!jsc|!|%|&j"< q|# D ]'\}}!|!$d% }(|(j&d d df ' |(| d< |(j(d	|(_|(||< qh|%# D ]'\}}!|!$d% }(|(j&d d df ' |(| d< |(j(d	|(_|(|%|< qdd })dt)dt)fdd}*|*||*|}+},i }-|D ]N}.|.j}/|.j*j"}0|.j*j+}1d }2d }3|/|v r|)||/ d|+|,}2|0|%v r|)|%|0 d|+|,}3|2d urt,|2d d}2|3d urt,|3d d}3|2|3|1d |-|/< q|D ],}4|d! # D ]"\}5}6|6-d"g D ]}7|7d# |4krN|4|-v rN|7.|-|4  q9q/q'i }8|D ]]}.|.j}/|.j*j"}0|-|/}9|%-|0}:|9d ur|:d ur|9|/ d  };|:|0 d  }<t/d$|; t/d%|< tj0|;|<gdd&d'}=d(d)g|=_1|=j2 |=_t	||}>d*d |>D }?d+d |=jD |=d,< |=|=d, 3|? }=|=js|=d( 4 nd }@|=js|=d) 4 nd t/d-|@d. t5|=dkr+|=d( j6dd/}At/d0|A |=d) j6dd/}Bt/d1|B |=d(d)g 7 j&d2 }Ct/d3|C t89|A}Dt/d4|D t89|B}Et/d5|E nd  }A }B}C|Cd ur?|Bd6vr?|C|B nd }Ft/d7|F d  }G }H }I}Jg }K|Fd urt5|=dkr|=d( j:}L|=d) j:}Md urvt;fd8d9|MD nd }I|Id ur|Idkrt<t5|LD ]}4|F|M|4  }N|L|4 |N }O|K=|O qt;d:d9 |KD }Pt5|K}Q|Qdkr|P|Qd  }J|Idkrt89|J|I nd }H|Hr|Hdkr|F|H nd }Gd  }R}S|Gd urt5|=dkrt5|=d }!ddt>?t@|G|!  }R|Rd;krd<nd=}Sd    }T}U|Kr1t5|K}Qt;|K|Q  |Qdkr1t89t; fd>d9|KD |Qd  }T|Tdkr/ |T nd }U|Ad ur;t,|Adnd |Dd urFt,|Ddnd |Jd urQt,|Jdnd |Hd ur\t,|Hdnd |Cd urgt,|Cdnd |Fd urrt,|Fdnd  d ur}t, dnd |Td urt,|Tdnd |Ud urt,|Udnd |Gd urt,|Gdnd |Rd urt,|Rdnd |Sd?|8|/< t/d@|8 qXdAdB t	||}>dCd |>D }?d,|?i}Vi }W|D ]a}/| tj|/d }|j*r|j*j"nd }0i }X|-|/}9|9d ur|9A }Y|Yj2 |Y_fdDd|YjD |Yd,< |Yd,dg jBd|/ dEidF}Yntjd,|/ dEgdF}Y|%-|0}:|:d urT|:A }Z|Zj2 |Z_fdGd|ZjD |Zd,< |Zd,dg jBd|/ dHidF}Zntjd,|/ dHgdF}ZtjC|Y|Zd,dIdJ}[|/ dE}\|\|[j1v rg }]|?D ]*}^|[jD|[d, |^k|\f }$|$js|$j:d ntEjF}$|]=tG|$rt,|$dnd  qw|]|X|\< |/ dH}\|\|[j1v rg }]|?D ]*}^|[jD|[d, |^k|\f }$|$js|$j:d ntEjF}$|]=tG|$rt,|$dnd  q|]|X|\< |9d ur|9|/ d  }_|_j2 |__g }`g }ag }b|?D ]}^|^ }ct|cd d }dt|cd }etjH|e|ddK ddL}f|_|_j|fk }gt5|gdkr?d|g I d }h|`=t,|hd d n|`=d  |:d ur|:|0 d  }i|ij2 |i_|i|ij|fk }jt5|jdkryd|j I d }k|a=t,|kd d n|a=d  |`dM d ur|adM d ur|`dM |adM  }l|b=t,|ld q |b=d  q |a=d  |b=d  q |`|X|/ dN< |:d ur|a|X|/ dO< |b|X|/ dP< |-|/i }m|m# D ]g\}n}ot|o}p|pd J|pd,< |pd,dQg jBdQ|/ dR|n idF}ptjC|[|pd,dIdJ}[|/ dR|n }\g }]|?D ]*}^|[jD|[d, |^k|\f }$|$js"|$j:d ntEjF}$|]=tG|$r2t,|$dnd  q|]|X|\< q|9d ur|9|/ d  };|;j2 |;_td,dSd |;jD |/ dT|;j:i}qtjC|[|qd,dIdJ}[|/ dT}\g }]|?D ]*}^|[jD|[d, |^k|\f }$|$js|$j:d ntEjF}$|]=tG|$rt,|$dnd  qv|]|X|\< |:d ur|:|0 d  }<|<j2 |<_td,dUd |<jD |/ dV|<j:i}rtjC|[|rd,dIdJ}[|/ dV}\g }]|?D ]*}^|[jD|[d, |^k|\f }$|$js|$j:d ntEjF}$|]=tG|$rt,|$dnd  q|]|X|\< |/|8v r,|8|/ # D ]\}s}t|tgt5|? |X|/ dR|s < q|X|W|/< qg }u|D ]9}/|W-|/i }vdWd |vK D }w|wrn|wd }xdXd |v|x D }y|yrnt;|y}z|zt5|y }{|{dkrn|u=|/ q6|uD ]}/|W|/ # D ]	\}|}]|]|V||< qzqrt|V}}t/dY t/|}jLdZd[ |}Md\jNd]d^|d_< t5| }~t5|}tO|~|d`}||da< t/| |S )bNc                 S   rY   r>   r   r   r>   r>   r?   r_     r`   z;stock_quarterly_performance_calculation.<locals>.<listcomp>c                 S   s   h | ]	}|j r|j jqS r>   )r  r  r   r>   r>   r?   r     ri   z:stock_quarterly_performance_calculation.<locals>.<setcomp>r  rj   r   r  r   c                 S   s   |  d\}}t|}t|d }|dkr| d| dfS |dkr-| d| dfS |d	kr;| d
| dfS |dkrI| d| dfS d S )Nr  rf  rj   r  z-03-31ro  r  z-06-30r  r  z-09-30r  r  z-12-31r	  )	qtr_labelr
  r  r  r>   r>   r?   new_quarter_to_dates  s   zEstock_quarterly_performance_calculation.<locals>.new_quarter_to_datesr   c                 S   r   ))r   rh  r_  r   r>   r>   r?   r_     ri   r   rh  ra  ri  rV  c                 S   r   ))r   r`  r_  r   r>   r>   r?   r_     ri   r`  rd  QE_Returnc                 S   s@   z| j ||f }| j ||f }|| | W S  ty   Y d S w r:   )locrR   )r  r  r  r  r  r  r>   r>   r?   period_change>  s   z>stock_quarterly_performance_calculation.<locals>.period_changeq_strr?  c                 S   s   |  d\}}| | S )Nr  )rX  )r  r
  r  r>   r>   r?   convert_quarter_formatF  s   zGstock_quarterly_performance_calculation.<locals>.convert_quarter_formatrg  ro  )zQuarterly Stock Change %zQuarterly Index Change %rp  r  rx  rk  zStock Returns:
zIndex Returns:
inner)axisjoinr  r  c                 S      g | ]}| d dqS r   r  r]   r
  r>   r>   r?   r_     r   c                 S       g | ]}d |j  d|j qS r   r  r  r  r]   re  r>   r>   r?   r_     r  Quarterzstock_q_mean:z---> index_q_mean:)ddofzstock_variance ===>zindex_variance ===>)r   rj   zcovariance ===>	stock_std	index_stdr  z	beta ===>c                 3       | ]	}|  d  V  qdS ro  Nr>   r  )index_q_meanr>   r?   r        z:stock_quarterly_performance_calculation.<locals>.<genexpr>c                 s   s    | ]}|d  V  qdS r(  r>   r   r>   r>   r?   r    r  g?YesNoc                 3   r'  r(  r>   r   )alphar>   r?   r    r*  )Stock_Variancer%  residual_variancese_slope
Covariancer  r  zTracking ErrorzInformation Ratiozt-statisticzp-valueSignificantzstats_results:c                 S   s.   t | }|jd d d }d| d|j S )Nrj   r  r   r  )r  r  r  r  )date_strdate_objr
  r>   r>   r?   date_to_quarter  s   
z@stock_quarterly_performance_calculation.<locals>.date_to_quarterc                 S   r  r  r  r  r>   r>   r?   r_     r   c                    r  r>   r>   rx   r5  r>   r?   r_     rz   z PricerU  c                    r  r>   r>   rx   r6  r>   r?   r_     rz   z Indexouter)onhowr  )r  r  r  rf  z Stock Performance %z Index Performance %z Excess Return %r   r  c                 S   r  r   r!  r"  r>   r>   r?   r_   a  r  z Returnc                 S   r  r   r!  r"  r>   r>   r?   r_   r  r  z Index Returnc                 S   s   g | ]	}| d r|qS )zExcess Return %)r  )r]   r  r>   r>   r?   r_     ri   c                 S   s   g | ]}|d ur|qS r:   r>   r  r>   r>   r?   r_     r   zH
===== Quarterly Performance Summary (Positive Excess Return Only) =====F)r  rp   r   rv  quarterly_performance_summary)total_stocksselected_kpiswarning_message)Pr   r   r   rV   r   r   r  r  rX  r  rS  r   r   r#   rj  r   r   r   r  r  r  r  r  r   rT   r  r  r  r  r   r   r   r"   re  r  r  resamplelastr  
pct_change	to_periodr   r  r  r  rG   rE  r   concatr  to_timestampisinmeanr   varcovr  sqrtr   sumr  rF  r1   cdfr  copyr`  merger  nprJ  notna	Timestampprodapplyr_  	to_stringr  r  excel_sheet_manager)rI  r  r  r   r   r=   r   r  stocksindex_symbols
start_year
start_qnum	prev_qnumr  prev_quarterr  
prev_startr  rX   rW   period_listperiod_datarU   rD  rM  rL  r   rJ  rK  	df_stocksrV   r   r  r  rS   ry   r  
df_indexesr  r  df_qr  r  	start_fmtend_fmtr  r   stock_symbolr  r  stock_changeindex_changer  r  
stock_datar  stats_resultsr  r  stock_returnsindex_returnsaligned_returnsr  formatted_quartersstock_q_meanstock_varianceindex_variance
covariancer%  r&  betat_statr0  sum_sq_xr/  	residualsstock_q_valuesindex_q_values	predictedresidresiduals_sumnp_valuesignificanttracking_error
info_ratiotablestock_data_dictstock_columns
df_stock_q
df_index_qdf_merger  
col_valuesr
  stock_returns_fullstock_perf_valuesindex_perf_valuesexcess_return_valuesq_partsr  q_yearq_datestock_returns_subsetcumulative_stock_returnindex_returns_fullindex_returns_subsetcumulative_index_returnexcessr   r  r  kpi_dfstock_returns_dfindex_returns_df	stat_name
stat_valuepositive_stocks
stock_colsexcess_cols
excess_colexcess_valuestotal_excess
avg_excesscol_name
df_summaryr;  r<  r   r>   )r-  r5  r)  r?   r    s  








  


















 


$

$





 

 
$
$






"$

$

$
 


r      @  c                 C   s   || }| | }||krd S || }| | d | }t d||  | }|| }	d| d|  d| d| d| d|  d| d	|	 d
}
|
S )Nrj   u   ⚠️ You have selected z
 KPIs and u[    Stocks. Based on Excel’s 16,384 column limit, this configuration requires approximately u)    sheets — each sheet can contain up to u    stocks.

🧭 Please reduce the number of stocks or KPIs for the best experience.

💡 Suggested Approaches:
1️⃣ Reduce KPIs per stock to ≤ z to fit all u*    stocks in one sheet.
2️⃣ Keep KPIs = u    but limit stocks to ≤ z per sheet.)r  )r;  r<  fixed_columnsexcel_limittotal_columns_per_stocktotal_columns_neededmax_stocks_per_sheetsheets_neededkpi_suggestionstock_suggestionrL   r>   r>   r?   rS    s&   rS  z/download_stock_analysis_excel/c              	   C   sj  t | d }d}t|j}dg}g }g }|dd  D ]}|| |dr.|| g }q|r6|| g }| }	|D ]}
t|	t|
 |krP|	|
 q>||	 | |
 }	q>|	rc||	 t	ddd=}t j
|jd	d
"}t|ddD ]\}}d| }|| j|d|d qyW d    n1 sw   Y  |j}W d    n1 sw   Y  t|dddS )Nr:  r  r#  rj   r2  Fz.xlsx)deletesuffixr  r  )r   Sheet)r  r  z#Quarterly_Performance_Summary2.xlsxr  )r9  filenamer  )r  r  r  r  rF  r  rK  r   r  r-   r  r   	enumerater  r*   )r   r  MAX_COLUMNS_PER_SHEETall_columnsbase_columnsstock_blocks
temp_blockr  sheetscurrent_sheet_colsblocktmpr  r  r  r  	file_pathr>   r>   r?   download_stock_analysis_excel  sJ   







r  )r  )ro  r   r:   )r  r  )r  r  rF   fastapir   r   r   r   r   r   r   r	   typingr
   r   r   r   r   r   
sqlalchemyr   sqlalchemy.ormr   r   r   app.dbr   r   r   app.utils.datastream_apir   r   r   r   r   app.db.modelsr   r   r   r   r    r!   r"   r#   app.db.databaser$   fastapi.middleware.corsr%   app.apir&   app.schemasr'   osrJ   r  sqlalchemy.excr(   fastapi.responsesr)   r*   r+   pydatastreamrP   pandasr  r,   tempfiler-   app.middlewarer.   	tracebackr/   starlette.middleware.baser0   loggingnumpyrM  scipy.statsr1   Basemetadata
create_allr  r;  add_middlewareinclude_routerroutergetenvrB   rC   r@   rH   rO   rT   postr   r   rG   r   r   r   r   rS  r  r  r  r  r  r  r  r  r  r  r  r   r8  r=  r  rI  rL  rg  rq  rt  ru  rx  rz  r  r  decimalr  r  r  r  precr  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  r  rH  r  r  r  r  r  rS  r  r  r>   r>   r>   r?   <module>   s   ((


,J
w



 !

 `

)"





X
	"3"m" " 
	
&1J


    
