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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 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.m/Z/m0Z0 d dl1m2Z2 d dl3m4Z4 d dl5m6Z6 d dl7m8Z8 d dl9Z9d dl:Z:d dl;Z;d dl<m=Z= d dl>m?Z?m@Z@mAZAmBZB d dlCZDd dlEZFd dlmGZG d dlHmIZI d dlJmKZK d dlLmMZMmNZNmOZOmPZP d dl7mQZQmRZR d dlSmTZT d dl:Z:d dlUZUd dlmZmVZVmZm	Z	 d dl>m?Z? d dlWmXZX d dlmZ d dlmZ d dlYZYd dlZZ[d dl\m]Z] ej^j_j`ejad e ZbebceK ebde6je ebjce4dgddgdgd  e9fd!Zge9fd"Zhd#d$ Ziej Zkebld%eeifd&eQd'efd(d)Zmebld*d+enfd,d-Zod.d$ Zid/Zpd0d1 Zqd2d3 Zrd d4lmZ ebld5eeifd+end6end7end8end9end'efd:d;Zsebtd<ed=ed>ed>edeeifd7end6end8end9end?eeen  d'efd@dAZuebtdBdCdD ZvebtdEdFdG ZwebtdHdIdJ Zxeeifd'efdKdLZyebtdMed=ed>ed>ededeeifd6end8end9end?eeen  dNeeen  d'efdOdPZzebtdQed=ed>ed>ededeeifd6end8end9end?eeen  dNeeen  d'efdRdSZ{dTdU Z|ebtdVeeifd'efdWdXZ}ebtdYed>eeifdZend'efd[d\Z~dd^d_Zdd`daZddbdcZddde ZddfdgZddidjZdkdl Zebtdmed>eeifd6end8end9endnendoeen d'efdpdqZebtdrdsdt ZduedveFjfdwdxZdydz Zebld{ed>eeifd|ed'efd}d~Zeblded>eeifd|ed'efddZebtdeeifd'efddZebtdeeifd'efddZebtdeeifd'efddZebtdeeifd'efddZeblded>eeifd|ed'efddZeblded>eeifd|ed'efddZd dlmZmZmZmZ de _dd Zdd Zdd Zdd Zdd Zdd Zdd Zdd Zdd Zdd Zdd Zdd Zdd Zdd Zdd Zdd Zdd Zdd Zdd ZdddZdd Zdd ZddÄ Zebldġeded>ed>e
dedeeifdNeeen  dendendeeenef  d?eeen  d'efddɄZdendenfdd̈́Zddτ Zdendvenfdd҄ZddԄ Zeeifd'efddքZdddڄZebldۡdefdd݄Zebtdޡeeifd'efddZG dd deTZebjtdee deeifd'efddZdS )    N)	FastAPIDependsQuery
UploadFileFileHTTPExceptionBodyloggerstatus)ListOptionalDictAny)datetime	timedelta)and_)Sessionsessionmakerrelationship)modelsdatabasecrud)get_user)fetch_kpi_dataparse_dotnet_dategenerate_date_rangefind_continuous_rangesstore_kpi_response)

InstrumentKPIKPIValueMarketExchngesMarketSectorIndexPriceValueInstrumentPriceValue	AdminUserApiLog)SessionLocal)CORSMiddleware)admin_routes)	KPIFilter)IntegrityError)JSONResponseFileResponseStreamingResponsePlainTextResponse)func)NamedTemporaryFile)!ComprehensiveAPILoggingMiddleware)verify_passwordcreate_token
SECRET_KEY	ALGORITHM)LoginRequestTokenResponse)	BaseModel)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(   closedb rJ   +/var/www/html/stock_analysis/be/app/main.pyget_db@   s
   rL   z/logindatarI   c                 C   s   t || j}|stdddS |jstdddS t| j|js$tdddS | jdkr?|jdkr9|jdkr9t|j}ntdddS t|j}|d	d
S )NzInvalid username or password  status_codezYour admin account is inactive.i  isAdminLoginTz!You do not have admin privileges.bearer)access_token
token_type)	r   usernamer0   	is_activer4   passwordflagis_superuserr5   )rM   rI   usertokenrJ   rJ   rK   loginK   s   


r\   z/logoutr[   c                 C   s^   | st dddS | dst dddS | dd }|tv r$t dddS t| t d	d
dS )NzToken is requiredrN   rO   zBearer z*Invalid token format. Use 'Bearer <token>'    zToken already logged outzLogged out successfully   )r0   
startswithsplitLOGOUT_BLACKLISTadd)r[   	extractedrJ   rJ   rK   logoute   s*   

re   c                  c   s*    t  } z
| V  W |   d S |   w rF   )r   r(   rG   rH   rJ   rJ   rK   rL      s
   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rU   rW   )params
TokenValuezFailed to fetch token: )requestsgetAPI_BASErU   rW   raise_for_statusjson
ValueError)responserM   r[   rJ   rJ   rK   	get_token   s   

rp   c                  C   s*   z
t jttd} | W S  ty   Y d S w )Nrf   )ds
DatastreamrU   rW   	Exception)DSrJ   rJ   rK   get_datastream   s   ru   )r   z/fetch-kpis/	frequencysymbol
start_dateend_datec              
   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 rJ   code.0krJ   rJ   rK   
<listcomp>       zfetch_kpis.<locals>.<listcomp>RequestAllMetadataTc                 S      g | ]	}|d didqS 
ReturnNameTValue
PropertiesrJ   r~   r|   rJ   rJ   rK   r          r^   StartEnd	FrequencyKindr   r    	DataTypesDater   Tagrh   r   DataRequestsz<=============== Response from DataStream API ===============DataResponsesr   c                 S   s   g | ]}t |qS rJ   )r   r~   drJ   rJ   rK   r          DatesDataTypeValuesSymbolValuesSymbolrw   DataTyper   Currencyr{   )instrument_idkpi_idrv   date)r   r   rv   r   valuecurrencymessagezKPI data fetched and stored.)queryr   r   allr   printr   	filter_byfirstrc   commitrefreshrj   zipr    id)r[   rv   rw   rx   ry   rI   	kpi_codesbodyro   rM   datesinstrument_symbol
instrumententrykpi_codevaluesr   kpir   r   existingkpi_valrJ   rJ   rK   
fetch_kpis   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 rJ   namer|   r}   rJ   rJ   rK   
<dictcomp>      zget_kpis.<locals>.<dictcomp>zName to Code Mapping:c                       g | ]}  ||qS rJ   rj   r~   r   name_code_maprJ   rK   r          zget_kpis.<locals>.<listcomp>zKPI Codes to Fetch:r{   r|   r   c                 S   rz   rJ   r{   r}   rJ   rJ   rK   r   )  r   r   c                    @   g | ]}  tjj|d  dur  tjj|d  qS r{   Nr   r   r   r   scalarr   rH   rJ   rK   r   4      c                 S      h | ]}|d  qS r   rJ   r   rJ   rJ   rK   	<setcomp>A  r   zget_kpis.<locals>.<setcomp>zExisting Dates in DB:zFull Date Range:c                       g | ]}| vr|qS rJ   rJ   r   existing_datesrJ   rK   r   E  r   zMissing Dates:zChunks:r   Tc                 S   r   r   rJ   r   rJ   rJ   rK   r   Q  r   r^   r   r   r   r   r   r   c                 S   "   g | ]}t |j|j|jd qS r   r   r   strr   r   r   r~   rrJ   rJ   rK   r   o      )rw   rv   rx   ry   r   )%rp   r   strptimer   rj   r   r   r   r   r   r   rc   r   r   r   r    filterr   r   rv   r   in_group_byhavingr1   countlenr   sortedr   	isoformatr   r   order_byr   r|   r   )rw   rv   rx   ry   r   rI   r[   startendfreq_mapexternal_freqr   r|   r   kpi_idsdb_dates
full_rangemissing_dateschunkschunk_start	chunk_endr   ro   kpi_datar   recordsrJ   rI   r   r   rK   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   rw   r   	market_id	sector_idmarket_namesector_name)r   rw   r   r  r  marketsectorr~   instrJ   rJ   rK   r     s    
zget_stocks.<locals>.<listcomp>)r(   r   r   r   rG   )rI   instrumentsresultrJ   rJ   rK   
get_stocks  s   
r  z/market_list/c                  C   r   )Nc                 S      g | ]	}|j |jd qS r   r   r  )r~   r  rJ   rJ   rK   r         z#get_market_list.<locals>.<listcomp>)r(   r   r"   r   rG   )rI   marketsr
  rJ   rJ   rK   get_market_list     r  z/sector_list/c                  C   r   )Nc                 S   r  r  r  )r~   r  rJ   rJ   rK   r     r  z#get_sector_list.<locals>.<listcomp>)r(   r   r#   r   rG   )rI   sectorsr
  rJ   rJ   rK   get_sector_list  r  r  c           #   
      s2  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 ]}|st	d8 q t
j|d9 }|st	d:|  q fd;d4|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dAd4 |D | | | dBdC|dDd@idEdFdGgdH}t||}t | qmi } |D ]R} tj|dI }!|!sq ttj|jktj|!jktj| ktj|ktj|k tj }"|"rdJd4 |"D | |!j!p|!j"< qd | |!j!p|!j"< qt	dK|  t#| ||||| } ||| i qt	dL| |||
||	fS )MNr   >   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  r6  r7  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_insidersrI  InstitutionalOwnershipPctnoshouwc05475>   r5  rJ  rM  r2  r8  >	   r9  rR  r<  rP  rD  r?  rA  r]  r_  c                 S   r   rJ   r   r}   rJ   rJ   rK   r     r   z-get_stock_kpi_calculation.<locals>.<dictcomp>c                    r   rJ   r   r   r   rJ   rK   r     r   z-get_stock_kpi_calculation.<locals>.<listcomp>falsec                 S   rz   rJ   r{   r}   rJ   rJ   rK   r     r   c                 S   r   rJ   r   r}   rJ   rJ   rK   r     r   z+[Warning] Skipping empty symbol in request.r   z*[Warning] No instrument found for symbol: c                    r   r   r   r   rH   rJ   rK   r     r   c                 S   r   r   rJ   r   rJ   rJ   rK   r   !  r   z,get_stock_kpi_calculation.<locals>.<setcomp>c                    r   rJ   rJ   r   r   rJ   rK   r   #  r   r   Tc                 S   r   r   rJ   r   rJ   rJ   rK   r   -  r   r^   r   r   r   r   r   r   r{   c                 S   r   r   r   r   rJ   rJ   rK   r   K  r   r   stock_wise_kpi_data)$r   r   r   updater   r   r   appendremover   r   r   r   r    r   r   r   rv   r   r   r   r   r1   r   r   r   r   rp   r   r   r   r   r   r   r|   dynamic_calculate_kpis)#rv   rx   ry   r   symbolsrI   r   r   lowerhigherNA_KPISKPI_DEPENDENCIEScalculated_lowercalculated_highercalculated_nacalculation_listr   r|   deprc  rw   r   r   r   r   r   r[   r   r   r   r   ro   r   r   r   rJ   r   rK   get_stock_kpi_calculation  s  
	










"




	

rr  z/stock_performance_calculation/rh  c           @         s  t dd |D }i }t t|}t| ||||\}}}	}
}}t|d }t|d }|D ]}|tj|d	 }|sKt
d| d q2|jsXt
d|j d q2|jj}|jj}|ttj|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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r7|#|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 p{i & D }#t }$|' D ]&}%|%D ] }&|&|
' v r|
& D ]\}'}(|(|&kr|$|'  nqqq|#& 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&||< q2d'd
 |' D }1t|1}0d(}2|0jrdd)}2n|0j.D ]k}3|3/d*r|3d+vrtj0|0|3 d,d-|0|3< |0|3 1  rqg|32dd(})|)|v r|0|3 j3dd.d/4d0|0|3 d1< qg|)|v r|0|3 j3d2d.d/4d0|0|3 d1< qg|0|3 j3d2d.d/4d0|0|3 d1< qgd3|0j.v r|0d3 j3d2d.d/4d0|0d4< nt
d5 |05 D ])\}4}5|5d6 }6|& D ]\}7}8|8d7 d! d6 |6kr|8d7 d! 6|5-  qq fd8d9 t7|||
}9i }:t };|9& D ]\}<}=d:d
 |=& D }>|>rH|>|:|<< |;6|> q/|;rWt
d;d<8t9|; |0jsf|:rdt:|:d=d>nd }2||9|2|:d?}? |?}?t
|? |?S )@Nc                 S   $   h | ]}| d D ]}| q	qS ,ra   stripr~   itemsrJ   rJ   rK   r   j     $ z4get_stock_performance_calculation.<locals>.<setcomp>r   r   z No instrument found for symbol: .zInstrument z# has no associated market_exchange.c                 S   r  r   r   r~  r   rJ   rJ   rK   r     r   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   rJ   rJ   rK   r     r   Pinstrument_for_idr   r   )TickerStart Price	End PriceReturn %zExcess vs ATX %   )	r  r  r  r  
Index NamezIndex Startz	Index EndzIndex Return %Total Score %c                    s   i | ]\}}| v r||qS rJ   rJ   r~   r   v)r   rJ   rK   r     s    z5get_stock_performance_calculation.<locals>.<dictcomp>NAz Score %z Score % RankT)dropr   r   r  zkpi_data-----r   orient)rw   rv   rx   ry   stock_with_performancec                 S   s   g | ]}|d  D ]}|qqS )r  rJ   )r~   rz  r   rJ   rJ   rK   r     s    r   zNo result found.zScore %)r  coerce)errorsdense)	ascendingmethodInt64z RankFr  Ranku;   'Total Score %' column not found — skipping overall rank.r  r  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 rJ   rJ   r  
clean_nansrJ   rK   r   0  r   zIget_stock_performance_calculation.<locals>.clean_nans.<locals>.<dictcomp>c                       g | ]} |qS rJ   rJ   r~   ir  rJ   rK   r   2  r   zIget_stock_performance_calculation.<locals>.clean_nans.<locals>.<listcomp>)
isinstancedictitemslistfloatmathisnan)objr  rJ   rK   r  .  s   

z5get_stock_performance_calculation.<locals>.clean_nansc                 S   s   g | ]
\}}|d u r|qS rF   rJ   r  rJ   rJ   rK   r   Q  r   u   ⚠️ KPIs with missing data:, compare)rX   )stock_performance_datarc  kpi_notewithout_values_kpis);r  setrr  r   r   r   r   r   r   r   r   market_exchangerw   
index_code
index_namer$   r   r  r   r   r   pd	DataFrame	set_indexru   fetchdropnaempty
itertuplesrc   r   rn   ilocr%   r  re  roundrj   r  r   to_datetimesort_valuesreset_indexfillnar   to_dictcolumnsendswith
to_numericisnareplacerankastypeiterrowsrd  $filter_dependency_kpis_from_responsejoinr   get_note_message)@rv   rx   ry   r   rh  rI   response_datarc  rl  rk  r   ri  rj  r   r   rw   r   index_symbolr  index_recordsdf_indexrt   r   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depsrq  r   r|   kpi_namer   df_kpi	start_valend_val
abs_change	kpi_scoreperf_dfrowscombined_messagecol_rowtickersymdetails	kpis_datar  missing_kpisstockkpi_dictmissing_for_stockr
  rJ   )r  r   rK   !get_stock_performance_calculation^  sn  








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







	


&
&"
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)rv   rx   ry   r   rh  rI   r  r  openpyxlengineStockPerformanceF)
sheet_nameindexr   zContent-Dispositionz+attachment; filename=stock_performance.xlsxAapplication/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
media_typeheaders)r  r  extendr  r  ioBytesIOExcelWriterto_excelseekr/   )rv   rx   ry   r   rh  rI   r
  r  r  rw   r  dfoutputwriterr  rJ   rJ   rK    download_stock_performance_excelh  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  rc   )rc  kpi_dependenciesr   r  r  rq  r   r|   filtered_datarw   r   filtered_kpi_datar  r   rJ   rJ   rK   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}   rJ   rJ   rK   r          z#get_kpi_options.<locals>.<listcomp>)r   r   r   )rI   r   rJ   rJ   rK   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  )rw   r   r  r  rJ   rJ   rK   r     r   z!symbol_search.<locals>.<listcomp>)r   r   r   rw   ilikelimitr   )r   rI   resultsrJ   rJ   rK   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 rF   r   r~   r  rJ   rJ   rK   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_listrJ   rJ   rK   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 rF   rJ   r~   r   r#  rJ   rK   	<genexpr>      z+check_absolute_threshold.<locals>.<genexpr>c                 3   s     | ]}|d uo| kV  qd S rF   rJ   r'  r(  rJ   rK   r)    r*  )any)r%  r#  r$  rJ   r(  rK   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   r  )abs)r~   prevcurrrJ   rJ   rK   r     s    z,check_consecutive_growth.<locals>.<listcomp>r^   negativec                    s
   |   kS rF   rJ   gr(  rJ   rK   <lambda>  s   
 z*check_consecutive_growth.<locals>.<lambda>c                    s   |  kS rF   rJ   r0  r(  rJ   rK   r2        c                 3   s6    | ]}t  fd d|| d  D V  qdS )c                 3   s    | ]} |V  qd S rF   rJ   )r~   r1  )r  rJ   rK   r)        z5check_consecutive_growth.<locals>.<genexpr>.<genexpr>r^   N)r   r  )r  growth_listr"  rJ   rK   r)    s
    $
z+check_consecutive_growth.<locals>.<genexpr>r  )r   r   r+  range)r%  r"  r#  r$  rJ   )r  r5  r"  r#  rK   r    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 )NFr^   r  c                 s   s     | ]}|d uo|dk V  qd S Nr   rJ   r~   xrJ   rJ   rK   r)    r*  z-check_negative_to_positive.<locals>.<genexpr>c                 s   s     | ]}|d uo|dkV  qd S r7  rJ   r8  rJ   rJ   rK   r)    s    
T)r   r6  r+  r   )r%  r"  r  segmenthalfrJ   rJ   rK   r    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   r  r/  r^   T)r   r6  r,  )r%  r"  r#  r$  r   r  growthrJ   rJ   rK   r    s   ,(

r  r  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 )Nr^   r   TFr  )r6  r   r,  )r%  quarters_afterr#  r  	growth_okjr=  rJ   rJ   rK   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 filtersr{   )FNr  trendr"  r#  r   r$  r  c                 S   s   | j S rF   )r   )r  rJ   rJ   rK   r2  ?  s    z'apply_advanced_filter.<locals>.<lambda>)keyT)r   r   r   r   r   r    r   r   r   r   rv   r   r   ascr   r   rj   intr  r&  max)rI   r   r   filter_datarv   rx   ry   r   r   r!  r"  r#  r$  passedlatest_valuerJ   rJ   rK   apply_advanced_filter'  s.   




rI  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 rJ   )rm   loads)r~   frJ   rJ   rK   r   N      z%filter_stocks_get.<locals>.<listcomp>zlogical_operator ===>r   r   rA  r^   )r   r   r   operatorr   r{   gtgteltlteeqneAND)rw   
kpi_valuesORmatching_resultsrh  )r   r   r   r   r   rI  r   re  r   r   r   r  r   r   r   r    r   r   r   rv   r   descupperr   rw   )rv   rx   ry   rJ  rK  rI   parsed_filtersr	  rY  r   instrument_kpi_datafilter_pass_countrM  r   rG  kpi_value_objrO  r   r   kpi_values_queryrJ   rJ   rK   filter_stocks_getD  s   





	

ra  z/routesc                   C   s   dd t jD S )Nc                 S   rz   rJ   )path)r~   routerJ   rJ   rK   r     r   zlist_routes.<locals>.<listcomp>)approutesrJ   rJ   rJ   rK   list_routes  s   rf  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  rN   zCould not read Excel file: rP   detail)filereadr  
read_excelr  r   rs   r   )rg  contentsr  erJ   rJ   rK   read_excel_to_df  s   
rp  c                 C   s,   | d u rd S t |  }| dv rd S |S )N>   r   nannone)r   rw  ri  )r   rz  rJ   rJ   rK   safe_str  s   rs  z/api/upload/market-exchangesrk  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 rJ   ri  r~   crJ   rJ   rK   r     rN  z+upload_market_exchanges.<locals>.<dictcomp>rN   zMissing columns: ri  c                       i | ]} | |qS rJ   rJ   r}   	lower_maprJ   rK   r     rN  r  r   r  r^   r  r  rt  r  echange_namer  r  r~  r  r  errorTsuccesscreatedskippedr  )rp  r  r  keysr   renamer  rs  rj   r   r!   r   r   r  r~  r  flushrc   rs   rollbackre  rD  r   r   )rk  rI   r  expectedmissingr  r  r  idxr  r|   r   mxro  rJ   rz  rK   upload_market_exchanges  sP   
$r  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  rw   r  
short_codemarket_index_codec                 S   ru  rJ   rv  rw  rJ   rJ   rK   r     rN  z&upload_instruments.<locals>.<dictcomp>rN   z$Missing columns (case-insensitive): ri  c                    ry  rJ   rJ   r}   rz  rJ   rK   r     rN  r|  )r   r   r   r  rw   r  r  r  r   r  r  rq  r^   updatedzinst.symbolr}  r   r  )r   )market_exchange_idr  rw   r  r  r   r  r  T)r  r  r  r  )$rp  r  r  r  r   r  r  rs  rj   ri  r   r   r!   r   r   rc   r  r#   r"   r   r   rw   unionr  r  r   r  r  r  rD  r  rn   r   r,   r  r   )rk  rI   r  r  r  insertedr  r  r  r  r  rw   r  r  r  r   r  r  r  semar   r  ro   rJ   rz  rK   upload_instruments  s   








r  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  r~  r  )r   r!   r   r   re  r   r  r  r~  r  r-   rI   r  payloadr   rJ   rJ   rK   list_market_exchangesU  s   


r  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   r  r  r  rw   r  r  r   r  r  )r   r   r   re  r   r  r  r  r  rw   r  r  r   r  r  r-   r  rJ   rJ   rK   list_instrumentsf  s    
r  z/marketsc                 C       |  t }tdd |D S )Nc                 S   r  r  r  r   rJ   rJ   rK   r   }  r   z list_markets.<locals>.<listcomp>)r   r"   r   r-   rI   r  rJ   rJ   rK   list_marketsz     r  z/sectorsc                 C   r  )Nc                 S   r  r  r  r   rJ   rJ   rK   r     r    list_sectors.<locals>.<listcomp>)r   r#   r   r-   r  rJ   rJ   rK   list_sectors  r  r  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  r  rN   rO   c                 S      i | ]	}|   |qS rJ   ri  rw  rw  rJ   rJ   rK   r     r   z!upload_market.<locals>.<dictcomp>colsr   (Excel file must contain a 'name' column.r   r^   r  Tr  )rk  rl  r  rm  r  r   rs   r-   r  r   r  r  r   rw  r   get_market_by_namecreate_marketre  rD  rk  rI   rn  r  ro  r  name_colr  r  r  r  r  r   rJ   rJ   rK   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  rN   rO   c                 S   r  rJ   r  rw  rJ   rJ   rK   r     r   z!upload_sector.<locals>.<dictcomp>r   r  r   r^   r  Tr  )rk  rl  r  rm  r  r   rs   r-   r  r  r  r   rw  r   get_sector_by_namecreate_sectorre  rD  r  rJ   rJ   rK   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 r7  )r  r  )	numeratordenominatorrJ   rJ   rK   safe_divide  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 )Nr   )r  r   r  rn   r  rJ   rJ   rK   
to_decimal  s   r  c                 C   6   |  d}|  d}d ||fv s|dkrd S t||S )NWC18199WC18198r   rj   r  )rM   net_debtr=  rJ   rJ   rK   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_payablerB  rJ   rJ   rK   calc_dpo  
   

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  rB  dporJ   rJ   rK   calc_ccc  s   


r  c                 C   r  )Nr  WC01001r   r  r  )rM   r=  r>  rJ   rJ   rK   calc_ebitda_margin  r  r  c                 C   s,   |  d}|  d}d ||fv rd S || S )NWC04860WC04601r   )rM   r:  r;  rJ   rJ   rK   calc_fcf  s
   

r  c                 C   r  )Nr  WC02999r   r  )rM   r>  r@  rJ   rJ   rK   calc_asset_turnover  r  r  c                 C   r  )Nr  WC02101r   r  )rM   rB  rC  rJ   rJ   rK   calc_inventory_turnover  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   rF  rG  rH  rI  r  rJ   rJ   rK   calc_tangible_bvps  s   



r  c                 C   r  )NDWFCWC07250r   r  )rM   rK  rL  rJ   rJ   rK   calc_forward_pe!  r  r  c                 C   r  )Nr  WC05508r   r  )rM   rN  rO  rJ   rJ   rK   calc_price_to_sales)  r  r  c                 C   r  )NWC01100r  r   r  r  )rM   rQ  r>  rJ   rJ   rK   calc_gross_margin1  r  r  c                 C   sN   |  d}|  d}|  d}d |||fv s|dkrd S || }t||d S )Nr  r  WC08001r   r  r  )rM   r:  r;  rT  free_cash_flowrJ   rJ   rK   calc_fcf_yield9  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   rV  rW  rJ   rJ   rK   calc_short_interest_ratioC  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   rZ  r[  r\  rJ   rJ   rK   calc_sharpe_ratioK  s   


r  c                 C   s>   |  d}|  d}d ||fv s|dkrd S t|| d |S )NNOSHOUWC05475r   r  r  )rM   r`  ra  rJ   rJ   rK    calc_institutional_ownership_pctT  s
   

r  c                 C   r  )NVODWCFr   r  r  )rM   r^  rI  rJ   rJ   rK   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
    rR  c                         t  S rF   )r  rJ   current_datarJ   rK   r2  l  r3  z%calculate_kpi_value.<locals>.<lambda>r2  c                      r  rF   )r  rJ   r  rJ   rK   r2  m  r3  r5  c                      r  rF   )r  rJ   r  rJ   rK   r2  n  r3  r8  c                      r  rF   )r  rJ   r  rJ   rK   r2  o  r3  r<  c                      r  rF   )r  rJ   r  rJ   rK   r2  p  r3  r9  c                      r  rF   )r  rJ   r  rJ   rK   r2  q  r3  r?  c                      r  rF   )r  rJ   r  rJ   rK   r2  r  r3  rA  c                      r  rF   )r  rJ   r  rJ   rK   r2  s  r3  rD  c                      r  rF   )r  rJ   r  rJ   rK   r2  t  r3  rJ  c                      r  rF   )r  rJ   r  rJ   rK   r2  u  r3  rM  c                      r  rF   )r  rJ   r  rJ   rK   r2  v  r3  rP  c                      r  rF   )r  rJ   r  rJ   rK   r2  w  r3  rU  c                      r  rF   )r  rJ   r  rJ   rK   r2  x  r3  rX  c                      r  rF   )r  rJ   r  rJ   rK   r2  y  r3  r]  c                      r  rF   )r  rJ   r  rJ   rK   r2  z  r3  r_  c                      r  rF   )r  rJ   r  rJ   rK   r2  {  r3  zcalc_map for period z ===> NzError calculating : zresults for period )r   r  rs   )
period_keyr  r  calc_mapr  r1   r  ro  rJ   r  rK   calculate_kpi_valued  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   r^   -01-01r  -04-01   -07-01r<  -10-01r   r   z[==================================stock data start========================================
zsaving stock data:zfrequency: zY==================================stock data end========================================
r   r   )r   re  r  )r  r   r  r   r   rw   rv   r   yearmonthdayquarterrJ   rJ   rK   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)r  r  freqr   r   r   zError fetching z for r  )
r  r  r  r  r  re  strftimer  rs   r   )rt   kpi_code_datarx   ry   rv   rw   kpi_data_listr   r  r  r   r   r  ro  rJ   rJ   rK   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   r2  r  r  )r3  r4  r5  r  r  )AccountsPayableCostOfGoodsSoldr8  r  r  )r  r  r  r  r9  r  r  )r:  r;  r<  r  )r=  r>  r?  r  )r>  r@  rA  r  )rB  rC  rD  r  r  r  r  rE  rJ  r  r  )rK  rL  rM  r  r  )rN  rO  rP  r  )rQ  r>  rR  r  rS  rU  r  r  )rV  rW  rX  r  r  r  rY  r]  r  r  )r^  rI  r_  r  r  )r`  ra  zkpi_data_list:r   r   r   r   r   r^   r  r   r   zstructured_data:zFinal kpi_data====>)rj   ru   r  r   r  r   r   r  r  r  r  r   r  r  )r   r   rw   rx   ry   rp  rv   quarterly_datar   r   kpi_data_mappingsr
  r  r	  rt   entriesr   dtr  r  r   
calculatedr   rJ   rJ   rK   rg    s   






	











rg  c                 C   s   t |  }tdd |  D }dd }||}||}|dkr+d| d| d}n	d| d	| d}t|tr<|g}td
| tdd| |S )Nc                 S   s   h | ]	}|D ]}|qqS rJ   rJ   )r~   r   r   rJ   rJ   rK   r     r   z#get_note_message.<locals>.<setcomp>c                 S   s^   | sdS t | dkr| d S t | dkr| d  d| d  S d| d d d| d   S )Nr   r^   r   r  z and z , r  )r   r  )r  rJ   rJ   rK   format_list  s    z%get_note_message.<locals>.format_listr  zFor stocks z*, the following KPIs have no data values: r|  zP, the following KPIs have no data values and were not included in the analysis: zMissing KPI Note:zAll missing KPIs:r  )r  r  r   r   r  r   r   r  )r  rX   stocksall_kpisr  stocks_formattedkpis_formattedr  rJ   rJ   rK   r    s   

r  z(/stock_quarterly_performance_calculationstart_period
end_periodr
  c                 C   s   t d| tdd | D } tt| } t d|  |d rD|d  D ]\}}||v r0|| q#d}|d }	t|	|}
|d|
i n|ddi t| |||||d	}|S )
Nzkpis received ===>c                 S   rs  rt  rv  rx  rJ   rJ   rK   r   K  r{  z>get_stock_quarterly_performance_calculation.<locals>.<setcomp>rh  r  quarterly_calculationquarterly_analysis_noter   )rh  r  r  r
  r   rI   )r   r  r  r  rf  r  rd  'stock_quarterly_performance_calculation)rh  r  r  r
  r   rI   r  r@  rX   r  r  quarterly_resultsrJ   rJ   rK   +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 )	Nr^   r<     r  )Q1Q2Q3Q4r  r   r  )ra   rD  r   r  calendar
monthrange)r  r   quarter_mapq_start
year_startmonth_startrx   q_endyear_end	month_endlast_dayry   rJ   rJ   rK   quarter_to_dates_  s   r0  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  r^   r   r<  )ra   rD  re  )	r  r  start_qstart_yend_qend_yr"  r  r  rJ   rJ   rK   generate_quarter_rangem  s   4
r5  r  c                 C   sR   |  d\}}t|d }t|}|d }|}|dkr!d}|d8 }d| d| S )Nr  r^   r   r<  r   ra   rD  )r  qr  q_num
prev_q_num	prev_yearrJ   rJ   rK   get_previous_quarter  s   r;  c                 C   s0   |  dr| d  d| 7  < dS || d< dS )z=Helper to safely append a message to quarterly_analysis_note.r  
Nr   )r
  r   rJ   rJ   rK   add_analysis_note  s   
r=  c                    sL  | 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 ]}| 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_nt }"z=|"j|dg||d }!|!js|! D ]\}#}$|t|j|#|$d q|  nd| d}%t|% t ||% t }!W nQ t!j"y }& zd| d}%t|% t ||% t }!W Y d }&~&n/d }&~&w t#yH }& zd| dt$|& d}%t|% t ||% t }!W Y d }&~&nd }&~&ww |!jsQ|!||< qui }'|	D ]}| 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_nt }"z@|"j|(j(dg||d }!|!js|! D ]\}#}$|t&|(j|#|$d q|  nd| d}%t|% t ||% t }!W nQ t!j"y }& zd| d}%t|% t ||% t }!W Y d }&~&n/d }&~&w t#y- }& zd| dt$|& d}%t|% t ||% t }!W Y d }&~&nd }&~&ww |!js7|!|'|(j(< qV|) D ]'\}}!|!*d+ }*|*j,d d df - |*| d< |*j.d	|*_|*||< q=|') D ]'\}}!|!*d+ }*|*j,d d df - |*| d< |*j.d	|*_|*|'|< qid d! }+d"t$d#t$fd$d%},|,||,|}-}.i }/|D ]N}0|0j}1|0j/j(}2|0j/j0}3d }4d }5|1|v r|+||1 d|-|.}4|2|'v r|+|'|2 d|-|.}5|4d urt1|4d& d'}4|5d urt1|5d& d'}5|4|5|3d(|/|1< q|D ],}6|d) ) D ]"\}7}8|82d*g D ]}9|9d+ |6kr#|6|/v r#|93|/|6  qqqi }:|D ]]}0|0j}1|0j/j(}2|2|1};|'2|2}<|;d ur|<d ur|;|1 d  }=|<|2 d  }>td,|= td-|> tj4|=|>gdd.d/}?d0d1g|?_5|?j6 |?_t	||}@d2d |@D }Ad3d |?jD |?d4< |?|?d4 7|A }?|?js|?d0 8 nd }B|?js|?d1 8 nd td5|Bd6 t9|?dkr |?d0 j:dd7}Ctd8|C |?d1 j:dd7}Dtd9|D |?d0d1g ; j,d: }Etd;|E t<=|C}Ftd<|F t<=|D}Gtd=|G nd  }C }D}E|Ed ur|Dd>vr|E|D nd }Htd?|H d  }I }J }K}Lg }M|Hd urt9|?d'kr|?d0 j>}N|?d1 j>}Od urKt?fd@dA|OD nd }K|Kd ur|Kdkrt@t9|ND ]}6|H|O|6  }P|N|6 |P }Q|MA|Q q]t?dBdA |MD }Rt9|M}S|Sd'kr|R|Sd'  }L|Kdkrt<=|L|K nd }J|Jr|Jdkr|H|J nd }Id  }T}U|Id urt9|?d'krt9|?d' }!d'dtBCtD|I|!  }T|TdCkrdDndE}Ud    }V}W|Mrt9|M}St?|M|S  |Sdkrt<=t? fdFdA|MD |Sd  }V|Vdkr |V nd }W|Cd urt1|Cdnd |Fd urt1|Fdnd |Ld ur&t1|Ldnd |Jd ur1t1|Jdnd |Ed ur<t1|Ednd |Hd urGt1|Hdnd  d urRt1 dnd |Vd ur]t1|Vdnd |Wd urht1|Wdnd |Id urst1|Idnd |Td ur~t1|Tdnd |UdG|:|1< tdH|: q-dIdJ t	||}@dKd |@D }Ad4|Ai}Xi }Y|D ]x}1| tj|1d }|j/r|j/j(nd }2i }Z|2|1};|;d ur|;E }[|[j6 |[_fdLd|[jD |[d4< |[d4dg jFd|1 dMidN}[ntjd4|1 dMgdN}[|'2|2}<|<d ur)|<E }\|\j6 |\_fdOd|\jD |\d4< |\d4dg jFd|1 dPidN}\ntjd4|1 dPgdN}\tjG|[|\d4dQdR}]|1 dM}^|^|]j5v r{g }_|AD ]*}`|]jH|]d4 |`k|^f }$|$jsb|$j>d ntIjJ}$|_AtK|$rrt1|$dnd  qL|_|Z|^< |1 dP}^|^|]j5v rg }_|AD ]*}`|]jH|]d4 |`k|^f }$|$js|$j>d ntIjJ}$|_AtK|$rt1|$dnd  q|_|Z|^< |;d ur|;|1 d  }a|aj6 |a_g }bg }cg }d|AD ]}`|` }et|ed d }ft|ed }gtjL|g|fdS ddT}h|a|aj|hk }it9|idkrd|i M d }j|bAt1|jd& d' n|bAd  |<d urz|<|2 d  }k|kj6 |k_|k|kj|hk }lt9|ldkrNd|l M d }m|cAt1|md& d' n|cAd  |bdU d urs|cdU d urs|bdU |cdU  }n|dAt1|nd' q|dAd  q|cAd  |dAd  q|b|Z|1 dV< |<d ur|c|Z|1 dW< |d|Z|1 dX< |2|1i }o|o) D ]~\}p}qt|q}r|rjsdY|rj5vrtdZ|p d[|1 d\ q|rd N|rd4< |rd4dYg jFdY|1 d]|p idN}rtjG|]|rd4dQdR}]|1 d]|p }^g }_|AD ]*}`|]jH|]d4 |`k|^f }$|$js|$j>d ntIjJ}$|_AtK|$rt1|$dnd  q|_|Z|^< q|;d ur|;|1 d  }=|=j6 |=_td4d^d |=jD |1 d_|=j>i}stjG|]|sd4dQdR}]|1 d_}^g }_|AD ]*}`|]jH|]d4 |`k|^f }$|$jsx|$j>d ntIjJ}$|_AtK|$rt1|$dnd  qb|_|Z|^< |<d ur|<|2 d  }>|>j6 |>_td4d`d |>jD |1 da|>j>i}ttjG|]|td4dQdR}]|1 da}^g }_|AD ]*}`|]jH|]d4 |`k|^f }$|$js|$j>d ntIjJ}$|_AtK|$rt1|$dnd  q|_|Z|^< |1|:v 	r|:|1 ) D ]\}u}v|vgt9|A |Z|1 d]|u < 	q|Z|Y|1< qg }w|D ]9}1|Y2|1i }xdbd |xO D }y|y	rZ|yd }zdcd |x|z D }{|{	rZt?|{}|||t9|{ }}|}dk	rZ|wA|1 	q"|wD ]}1|Y|1 ) D ]	\}~}_|_|X|~< 	qf	q^t|X}tdd t|jPdedf |QdgjRdhdi|dj< t9| }t9|}tS||dk}%|%|dl< t| |S )mNc                 S   rz   rJ   r   r  rJ   rJ   rK   r     r   z;stock_quarterly_performance_calculation.<locals>.<listcomp>c                 S   s   h | ]	}|j r|j jqS rJ   )r  r  r  rJ   rJ   rK   r     r   z:stock_quarterly_performance_calculation.<locals>.<setcomp>r  r^   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  r  r^   r  z-03-31r  r  z-06-30r  r   z-09-30r<  r  z-12-31r6  )	qtr_labelr7  r  r8  rJ   rJ   rK   new_quarter_to_dates  s   zEstock_quarterly_performance_calculation.<locals>.new_quarter_to_datesr   c                 S   r  ))r   r  r~  r   rJ   rJ   rK   r     r   r   r  r  r  z No price data found for symbol 'z'.z Datastream returned no data for z: NO DATA AVAILABLE.z)Unexpected error while fetching data for r  r|  r}  c                 S   r  ))r   r  r~  r   rJ   rJ   rK   r     r   r  r  zNo price data found for index 'QE_Returnc                 S   s@   z| j ||f }| j ||f }|| | W S  ty   Y d S w rF   )locrs   )r  r  r  r  r  r  rJ   rJ   rK   period_change8  s   z>stock_quarterly_performance_calculation.<locals>.period_changeq_strrh  c                 S   s   |  d\}}| | S )Nr  )ra   )rD  r7  r  rJ   rJ   rK   convert_quarter_format@  s   zGstock_quarterly_performance_calculation.<locals>.convert_quarter_formatr  r  )zQuarterly Stock Change %zQuarterly Index Change %r  r  r  r  zStock Returns:
zIndex Returns:
inner)axisr  r  r  c                 S      g | ]}| d dqS r  r]   r  r~   r7  rJ   rJ   rK   r     r   c                 S       g | ]}d |j  d|j qS r   r]   r  r  r~   r  rJ   rJ   rK   r     r  Quarterzstock_q_mean:z---> index_q_mean:)ddofzstock_variance ===>zindex_variance ===>)r   r^   zcovariance ===>	stock_std	index_stdr7  z	beta ===>c                 3       | ]	}|  d  V  qdS r  NrJ   r8  )index_q_meanrJ   rK   r)        z:stock_quarterly_performance_calculation.<locals>.<genexpr>c                 s   s    | ]}|d  V  qdS rU  rJ   r   rJ   rJ   rK   r)    r4  g?YesNoc                 3   rT  rU  rJ   r   )alpharJ   rK   r)    rW  )Stock_VariancerR  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 )Nr^   r  r   r]   )r  r  r  r  )date_strdate_objr7  rJ   rJ   rK   date_to_quarter  s   
z@stock_quarterly_performance_calculation.<locals>.date_to_quarterc                 S   rH  rI  rJ  rK  rJ   rJ   rK   r     r   c                    r  rJ   rJ   r   rb  rJ   rK   r     r   z Pricer|  c                    r  rJ   rJ   r   rc  rJ   rK   r     r   z Indexouter)onhowr  )r  r  r  r  z Stock Performance %z Index Performance %z Excess Return %r   zSkipping KPI 'z' for z (no data found)r]   c                 S   rL  rM  rN  rO  rJ   rJ   rK   r   _	  r  z Returnc                 S   rL  rM  rN  rO  rJ   rJ   rK   r   p	  r  z Index Returnc                 S   s   g | ]	}| d r|qS )zExcess Return %)r  )r~   r  rJ   rJ   rK   r   	  r   c                 S   s   g | ]}|d ur|qS rF   rJ   r  rJ   rJ   rK   r   	  r   zH
===== Quarterly Performance Summary (Positive Excess Return Only) =====F)r  r   r   r  quarterly_performance_summary)total_stocksselected_kpiswarning_message)Tr   r   r   rw   r   r   r  rD  ra   r5  rr  r   r   r%   r  r   r   r   r  r  r  r  r  r   ru   r  r  r  r  rc   r   r   r=  rq   DatastreamExceptionrs   r   r!   r$   r  r  r  resamplelastr  
pct_change	to_periodr  r  r  rj   rd  concatr  to_timestampisinmeanr   varcovr  sqrtr   sumr6  re  r=   cdfr,  copyr  mergerB  nprq  notna	Timestampprodapplyr  	to_stringr  r  excel_sheet_manager)rh  r  r  r
  r   rI   r   r  r  index_symbols
start_year
start_qnum	prev_qnumr:  prev_quarterr?  
prev_startr  ry   rx   period_listperiod_datarv   rc  rl  rk  r   ri  rj  	df_stocksrw   r   r  r  rt   r   r  r   ro  
df_indexesr  r  df_qrC  rE  	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
covariancerR  rS  betat_statr]  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_valuesr7  stock_returns_fullstock_perf_valuesindex_perf_valuesexcess_return_valuesq_partsr8  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_summaryrh  ri  rJ   )rZ  rb  rV  rK   r    s  
















  


















 


$

$





 

 
$
$






"$

$

$
 


r      @  c                 C   s   || }| | }||krd S || }| | d | }t d||  | }|| }	d| d|  d| d| d| d|  d| d	|	 d
}
|
S )Nr^   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.)rE  )rh  ri  fixed_columnsexcel_limittotal_columns_per_stocktotal_columns_neededmax_stocks_per_sheetsheets_neededkpi_suggestionstock_suggestionro   rJ   rJ   rK   r  	  s&   r  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 )Nrg  r  rP  r^   r_  Fz.xlsx)deletesuffixr  r  )r   Sheet)r  r  z#Quarterly_Performance_Summary2.xlsxr  )rb  filenamer  )r  r  r  r  re  r  ry  r   r  r2   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_pathrJ   rJ   rK   download_stock_analysis_excel	  sJ   







r  z
/user-listc                 C   s,   |  ttj  }tdd |D S )Nc                 S   r  )r   rU   r  r   rJ   rJ   rK   r   
  r   r  )r   r&   r   r   rC  r   r-   r  rJ   rJ   rK   r  
  s   c                   @   sp   e Zd ZU eed< eed< eed< eed< eed< eed< edB ed< edB ed	< edB ed
< G dd dZdS )ApiLogSchemar   endpointr  
ip_address
user_agent	timestampNrequest_dataresponse_statuserror_messagec                   @   s   e Zd ZdZdS )zApiLogSchema.ConfigTN)__name__
__module____qualname__from_attributesrJ   rJ   rJ   rK   Config
  s    r  )r  r  r  rD  __annotations__r   r   r  rJ   rJ   rJ   rK   r  
  s   
 r  z/api-log-list)response_modelc                 C   r  )Nc                 S   s6   g | ]}|j |j|j|j|j|j|j|j|jd 	qS )	r   r  r  r  r  r  r  r  r  r  r   rJ   rJ   rK   r   $
  s
    
r  )r   r'   r   r-   r  rJ   rJ   rK   r  !
  s   )r  )r  r   rF   )r  r  )r&  r  ri   fastapir   r   r   r   r   r   r   r	   r
   typingr   r   r   r   r   r   
sqlalchemyr   sqlalchemy.ormr   r   r   app.dbr   r   r   app.db.crudr   app.utils.datastream_apir   r   r   r   r   app.db.modelsr   r   r    r!   r"   r#   r$   r%   r&   r'   app.db.databaser(   fastapi.middleware.corsr)   app.apir*   app.schemasr+   osrm   r  sqlalchemy.excr,   fastapi.responsesr-   r.   r/   r0   pydatastreamrq   pandasr  r1   tempfiler2   app.middlewarer3   app.authr4   r5   r6   r7   r8   r9   pydanticr:   	tracebackr;   starlette.middleware.baser<   loggingnumpyr{  scipy.statsr=   Basemetadata
create_allr  rd  add_middlewareinclude_routerroutergetenvrU   rW   rL   r  rb   postr\   r   re   rk   rp   ru   r   rj   r   r  r  r  rr  r  r  r  r  r  r&  r   r  r  r  r  rI  ra  rf  r  rp  rs  r  r  r  r  r  r  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  rg  r  r  r0  r5  r;  r=  r  r  r  r  r  rJ   rJ   rJ   rK   <module>   s   ,0


,J
w



 +

  

)"


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