Ë
|
O>jý1 ã ó d Z ddlmZ ddlZddlZddlZddlZddlZddlZddl Z ddl
|
Z
|
ddl m Z m Z ddl mZ ee« j! « Zej$ d Z eej( j+ dd« « Zdd Zddd
|
Zdd Zdd Zd d Zd dZ d dZ d dZ d dZ d!dZ d"dZ!d#dZ"d$d%dZ#e$dk( r e% e#« « y)&aB Scan local A-share K-line/profile data for market reverse-gap candidates.
|
|
The script uses local MySQL read-only tables:
|
|
- a_share_profile_snapshot
|
- a_share_daily_price
|
|
It writes market manifestation audit artifacts into the industry case layout.
|
Use the result as a gap-finding input, not as an investment conclusion.
|
é )Ú annotationsN)ÚdatetimeÚtimezone)ÚPathé ÚTIANXIA_MYSQL_EXEz M:/mysql/server/bin/mysql.exec ó t j t j « j « j d¬« S )NÚseconds)Útimespec)r Únowr ÚutcÚ
|
astimezoneÚ isoformat© ó úAE:\mb-ms-doc\project-info\ana-data\tools\market_gap_scan_mysql.pyÚnow_isor s, Ü << Ó %× 0Ñ 0Ó 2× <Ñ <ÀiÐ <Ó PÐPr c ó t j dd| j « « } | j d« } | xs |d | S )Nz[^A-Za-z0-9._-]+Ú_z._-)ÚreÚsubÚstrip)ÚvalueÚfallbackÚmax_lens r Ú safe_namer # s; Ü FFÐ&¨¨U¯[©[«]Ó ;EØ KKÓ EØ Ò Xx Ð (Ð(r c ón | yt | « }d|j dd« j dd« z dz S )NÚNULLÚ'Ú\ú\\z'')ÚstrÚreplace)r Útexts r Ú sql_literalr% ) s; Ø }ØÜ u:DØ d FÓ+×3Ñ3°C¸Ó>Ñ >ÀÑ DÐDr c óf t j | j d« « j « S )Núutf-8)ÚhashlibÚsha256ÚencodeÚ hexdigest)r s r Ú sha256_textr, 0 s# Ü >>%,, wÓ/Ó 0× :Ñ :Ó <Ð<r c óÜ | j « j t j « « j « S # t $ r! | j « j « cY S w xY w©N)ÚresolveÚ relative_toÚ PROJECT_ROOTÚas_posixÚ
|
ValueError)Úpaths r Úproject_relativer5 4 sT ð)Ø||~×)Ñ)¬,×*>Ñ*>Ó*@ÓA×JÑJÓLÐLøÜ ò )Ø||~×&Ñ&Ó(Ò(ð)ús >A Á'A+Á*A+c óþ g }| D ]f }d|j dd« j dd« j dd« z dz }t |« }|j d| d| d | d
|
| d | d « h d j |« S )NÚ%r r! z\%r z\_z(p.industry_l1 LIKE z# ESCAPE '\\' OR p.industry_l2 LIKE z$ ESCAPE '\\' OR p.concepts_raw LIKE z) ESCAPE '\\' OR p.main_business_raw LIKE z" ESCAPE '\\' OR p.stock_name LIKE z ESCAPE '\\')z OR )r# r% ÚappendÚjoin)ÚkeywordsÚclausesÚkeywordÚpatternÚlits r Úkeyword_predicater? ; s« ØGÛب¨fÓ5×=Ñ=¸cÀ5ÓI×QÑQÐRUÐW\Ó]Ñ]Ð`cÑcÜ'Ó"Øð "Ø"% ð '"Ø"% ð '#Ø#& %ð ((Ø(+ uð -!Ø!$ ð &ð õ
|
ð ð ;;wÓ Ð r c óP | j s t d« t | j « }t | j « }t | j
|
« }t | j « }t | j « }t | j « }dj g d| d| d| d| d| d| d | d
|
t | j « d t | j « d | d | d| d | d| d| d« j « S )Nzpass at least one --keywordÚ z
|
WITH latest_profile AS (
|
SELECT MAX(snapshot_date) AS snapshot_date
|
FROM a_share_profile_snapshot
|
WHERE snapshot_date <= a
|
),
|
universe AS (
|
SELECT
|
p.symbol,
|
p.stock_name,
|
p.industry_l1,
|
p.industry_l2,
|
p.concepts_raw,
|
p.main_business_raw
|
FROM a_share_profile_snapshot p
|
JOIN latest_profile lp ON lp.snapshot_date = p.snapshot_date
|
WHERE p.status = 'OK'
|
AND (a )
|
),
|
price_with_prev AS (
|
SELECT
|
d.symbol,
|
d.trade_date,
|
d.open_price,
|
d.high_price,
|
d.low_price,
|
d.close_price,
|
d.volume,
|
d.amount,
|
LAG(d.close_price) OVER (PARTITION BY d.symbol ORDER BY d.trade_date) AS prev_close,
|
AVG(d.amount) OVER (
|
PARTITION BY d.symbol
|
ORDER BY d.trade_date
|
ROWS BETWEEN 20 PRECEDING AND 1 PRECEDING
|
) AS avg_amount_20
|
FROM a_share_daily_price d
|
JOIN universe u ON u.symbol = d.symbol
|
WHERE d.trade_date BETWEEN DATE_SUB(z, INTERVAL 30 DAY) AND a
|
),
|
window_rows AS (
|
SELECT
|
u.stock_name,
|
u.industry_l1,
|
u.industry_l2,
|
u.concepts_raw,
|
u.main_business_raw,
|
p.*,
|
CASE
|
WHEN p.prev_close IS NULL OR p.prev_close = 0 THEN NULL
|
ELSE (p.close_price / p.prev_close - 1) * 100
|
END AS daily_return_pct,
|
CASE
|
WHEN p.avg_amount_20 IS NULL OR p.avg_amount_20 = 0 THEN NULL
|
ELSE p.amount / p.avg_amount_20
|
END AS amount_ratio_20
|
FROM price_with_prev p
|
JOIN universe u ON u.symbol = p.symbol
|
WHERE p.trade_date BETWEEN z AND aØ
|
),
|
ranked AS (
|
SELECT
|
w.*,
|
ROW_NUMBER() OVER (PARTITION BY w.symbol ORDER BY w.trade_date) AS rn_first,
|
ROW_NUMBER() OVER (PARTITION BY w.symbol ORDER BY w.trade_date DESC) AS rn_last
|
FROM window_rows w
|
),
|
agg AS (
|
SELECT
|
symbol,
|
MAX(stock_name) AS company_name,
|
MAX(industry_l1) AS industry_l1,
|
MAX(industry_l2) AS industry_l2,
|
MAX(concepts_raw) AS concepts_raw,
|
MAX(main_business_raw) AS main_business_raw,
|
MIN(trade_date) AS window_start,
|
MAX(trade_date) AS window_end,
|
MAX(CASE WHEN rn_first = 1 THEN close_price END) AS first_close,
|
MAX(CASE WHEN rn_last = 1 THEN close_price END) AS last_close,
|
COUNT(*) AS trading_days,
|
SUM(CASE WHEN daily_return_pct >= a THEN 1 ELSE 0 END) AS strong_up_days,
|
MAX(daily_return_pct) AS max_daily_return_pct,
|
MAX(amount_ratio_20) AS max_amount_ratio_20,
|
MAX(amount) AS max_amount
|
FROM ranked
|
GROUP BY symbol
|
),
|
scored AS (
|
SELECT
|
*,
|
CASE
|
WHEN first_close IS NULL OR first_close = 0 THEN NULL
|
ELSE (last_close / first_close - 1) * 100
|
END AS period_return_pct
|
FROM agg
|
)
|
SELECT
|
z AS scan_id,
|
a¤ AS industry_case,
|
symbol,
|
company_name,
|
industry_l1,
|
industry_l2,
|
window_start,
|
window_end,
|
trading_days,
|
ROUND(first_close, 4) AS first_close,
|
ROUND(last_close, 4) AS last_close,
|
ROUND(period_return_pct, 4) AS period_return_pct,
|
strong_up_days,
|
ROUND(max_daily_return_pct, 4) AS max_daily_return_pct,
|
ROUND(max_amount_ratio_20, 4) AS max_amount_ratio_20,
|
CASE
|
WHEN period_return_pct >= z2 OR strong_up_days >= 1 OR max_amount_ratio_20 >= zù
|
THEN 'STRONG_MANIFESTATION'
|
ELSE 'WEAK_OR_NORMAL'
|
END AS manifestation_type,
|
'REVIEW_REQUIRED' AS scope_type,
|
'REVIEW_REQUIRED' AS suspected_missing_track,
|
'MARKET_REVERSE_GAP_SCAN' AS gap_type,
|
CASE
|
WHEN period_return_pct >= aX
|
THEN 1
|
ELSE 0
|
END AS supplement_required_flag,
|
'Review core business against industry scheme before adding to evidence queue' AS supplement_question,
|
'DRAFT_FOR_REVIEW' AS review_status,
|
LEFT(concepts_raw, 500) AS concepts_excerpt,
|
LEFT(main_business_raw, 500) AS main_business_excerpt
|
FROM scored
|
WHERE period_return_pct >= z8
|
OR strong_up_days >= 1
|
OR max_amount_ratio_20 >= zp
|
ORDER BY supplement_required_flag DESC, period_return_pct DESC, strong_up_days DESC, max_amount_ratio_20 DESC;
|
)r< Ú
|
SystemExitr? r% Ú
|
start_dateÚend_dateÚfloatÚmin_return_pctÚstrong_day_pctÚ amount_ratior9 Úscan_idÚ industry_caser )ÚargsÚpredÚstartÚendÚ
|
min_returnrG rH s r Ú build_sqlrP L sX Ø <<ÜÐ6Ó7Ð7Ü T\\Ó *DÜ Ó (EÜ
|
dmmÓ
|
$CÜt×*Ñ*Ó+JÜ4×.Ñ.Ó/NÜ×*Ñ*Ó+L÷E ó E ð ð E ð ð E ð
|
ð E ð" ð#E ð"'ð#E ðJ (- gðKE ðJ .EðKE ðJ FIÀEðKE ðJJ ðKE ðr $WðsE ðr %*ðsE ðr +.¨ðsE ðr/'ðsE ð^ (6Ð&6ð_E ð^7ð_E ô@ t||Ó Ð ðAE ð@ ðAE ôB t×!Ñ!Ó"Ð#ðCE ðB$ ðCE ð` *lðaE ð` +]ðaE ð` ^jÐ\jðaE ð`k ðaE ðp *lðqE ðp +]ðqE ðp ^jÐ\jðqE ðp k ðqE ðB '<ðCE ðB( ðCE ðF +^ðGE ðF,ôGE ÷J
|
EGðKE r c ó~ t j j d« }|s t d« t | j
|
« }|j « st d| « t |« dddddd | j d
|
| j d | j | j d |g }t j j « }||d < t j |dd|d¬« }|j dk7 r?t |j j# « xs |j$ j# « « |j$ S )NÚTIANXIA_MYSQL_PASSWORDz5TIANXIA_MYSQL_PASSWORD is required in the environmentz mysql executable not found: z--protocol=TCPz--batchz--rawz--quickz --default-character-set=utf8mb4z--host=z--port=z--user=z-eÚ MYSQL_PWDTF)r$ Úcapture_outputÚenvÚcheckr )ÚosÚenvironÚgetrB r Ú mysql_exeÚexistsr" ÚhostÚportÚuserÚdatabaseÚcopyÚ
|
subprocessÚrunÚ
|
returncodeÚstderrr Ústdout)rK ÚsqlÚpasswordrZ ÚcmdrU Úprocs r Ú run_mysqlrj Ý s Üzz~~Ð 6Ó7HÙ ÜÐPÓQÐQÜT^^Ó$IØ × Ñ Ô ÜÐ7¸ °{ÐCÓDÐDä IØØØØØ)Ø
|
$))Ð Ø
|
$))Ð Ø
|
$))Ð Ø Ø Ø ð Cô **//Ó
|
CØ C ÑÜ >># D¸À3ÈeÔ TDØ !ÒÜ×*Ñ*Ó,ÒC°· ± ×0AÑ0AÓ0CÓDÐDØ ;;Ðr c ó\ |j j dd¬« d}|j ddd¬« 5 }t j |« }t | j « « D ]0 \ }}|j |j d« « |dkD s,|d z }2 d d d « |S # 1 sw Y |S xY w)
|
NT©ÚparentsÚexist_okr Úwú utf-8-sigrA ©ÚencodingÚnewlineÚ é ) ÚparentÚmkdirÚopenÚcsvÚwriterÚ enumerateÚ
|
splitlinesÚwriterowÚsplit)ÚtsvÚout_pathÚ row_countÚfhrz Ú
|
line_indexÚlines r Úwrite_csv_from_tsvr
ú s Ø OO×Ñ $°ÐÔ6ØIØ s [¸" Ô =ÀÜB Ü )¨#¯.©.Ó*:Ö ;Ñ JØ OODJJ tÓ ,Ô -ØA~ØQ ñ !<÷
|
>ð Ð÷
|
>ð Ðús ´AB!ÂB!Â!B+c óR | j « }| j j dd¬« | j ddd¬« 5 }t j
|
|t |j « « ¬« }|s|j « |j |« d d d « y # 1 sw Y y xY w)NTrl Úarp rA rq )Ú
|
fieldnames)
|
r[ rv rw rx ry Ú
|
DictWriterÚlistÚkeysÚ writeheaderr} )r4 Úrowr[ r rz s r Úappend_manifestr sz Ø [[]FØKK×Ñd¨TÐÔ2Ø 3 °bÔ 9¸RÜ ¬t°C·H±H³JÓ/?Ô@ÙØ × Ñ Ô ØÔ ÷
|
:× 9Ñ 9ús ÁAB  B&c ód | j s t | j d« d| _ t dz dz | j z }|dz }|dz }|j dd¬« |j dd¬« t | j d« }t | « }t |« }|d | d
|
|d d d z }|d | d
|
|d d dz }|d| d
|
|d d dz } |d| d z }
|
t | |« } t | |« } | j |dz d¬« i d| j d| j d| j d| j d| j d| j d| j d | j d | j d | j" d | j$ d | d!t' |« d"|d#t) « d$d%} |j t+ j, | d&d'¬(« d¬« t/ |
|
| j | j | j | j | j | j | j d)j1 | j « |t' | « t' |« t' |« t3 | « | d# d*d+« t5 d,t' |« d-| « t5 d.t' |« « y/)0Nrb Ú_MARKET_GAP_SCANzana-dataÚcasesÚ
|
supplementÚmanifestTrl Ú market_manifestation_gap_audit_r é z.csvÚ!market_manifestation_gap_summary_z.jsonÚ market_manifestation_gap_query_z.sqlÚ"market_manifestation_gap_manifest_Ú
|
r' )rr rI Úcase_idÚbatch_idÚrun_idrJ rC rD r: rF rG rH r Ú
|
audit_pathÚ
|
sql_sha256Ú generated_atÚreadoutÚ'GAP_SCAN_ONLY_NOT_INVESTMENT_CONCLUSIONFr )Ú ensure_asciiÚindentÚ|ÚDRAFT_FOR_REVIEW)rI r r r rJ rC rD r: r Úsql_relative_pathÚaudit_relative_pathÚsummary_relative_pathr r Ú review_statuszaudit=z rows=zsummary=r )rI r r r1 rJ rw rP r, rj r
Ú
|
write_textr r rC rD r< rF rG rH r5 r ÚjsonÚdumpsr r9 r" Úprint)rK Ú industry_rootÚsupplement_dirÚ manifest_dirÚrun_saferf Úsql_hashr Ú summary_pathÚsql_pathÚ manifest_pathr r Úsummarys r Úscanr· s& Ø <<Ü# D§K¡K°Ó7Ð8Ð8HÐI Ü :Ñ-°Ñ7¸$×:LÑ:LÑLMØ" \Ñ1NØ :Ñ-LØ×Ñ °ÐÔ5Ø×Ñt¨dÐÔ3ä eÓ,HÜ
|
D/CÜ3Ó HØ Ð$CÀHÀ:ÈQÈxÐX[ÐY[È}ÈoÐ]aÐ"bÑbJØ!Ð&GÈÀzÐQRÐS[Ð\_Ð]_ÐS`ÐRaÐafÐ$gÑgLØÐ ?À¸zÈÈ8ÐTWÐUWÈ=È/ÐY]Ð ^Ñ^HØ Ð%GÈÀzÐQUÐ#VÑVMä
|
D#Ó
|
CÜ" 3¨
|
Ó3IØ ×Ñd
|
¨WÐÔ5ðØ4<<ðà4<<ðð DMMðð $++ð ð
|
×+Ñ+ð ð dooð ð DMMðð DLLðð $×-Ñ-ðð $×-Ñ-ðð ×)Ñ)ðð Yðð Ô& zÓ2ðð hð ð ð ð Ð<ð!Gð$ ×ÑDJJ w¸UÈ1Ô MÐX_ÐÔ`ÜØà||Ø||Ø ØkkØ !× /Ñ /Ø //Ø Ø § ¡ Ó.Ø"Ü!1°(Ó!;Ü#3°JÓ#?Ü%5°lÓ%CÜ Y Ø # NÑ 3Ø /ñ
|
ôô(
|
FÔ# JÓ/Ð0°°y°kÐ
|
BÔCÜ HÔ% lÓ3Ð4Ð
|
5Ô6Ø r c óÞ t j t ¬« }|j dd¬« |j dd¬« |j dd¬« |j dd¬« |j d« |j d d¬« |j d
|
d¬« |j d d d¬ « |j dt d¬« |j dt d¬« |j dt d¬« |j dt t « ¬« |j dt j j dd« ¬« |j dt j j dd « ¬« |j d t j j d d « ¬« |j d t j j d!d"« ¬« |j | « S )#N)Ú descriptionz--industry-caseT)Úrequiredz --case-idz
|
--batch-idz--run-idz --scan-idz --start-datez
|
--end-datez --keywordr8 )Úactionrº z--min-return-pctg 4@)ÚtypeÚdefaultz--strong-day-pctg #@z--amount-ratiog @z --mysql-exe)r½ z--hostÚTIANXIA_MYSQL_HOSTz 127.0.0.1z--portÚTIANXIA_MYSQL_PORTÚ3306z--userÚTIANXIA_MYSQL_USERÚrootz
|
--databaseÚTIANXIA_MYSQL_DBÚtianxia) ÚargparseÚArgumentParserÚ__doc__Ú add_argumentrE r" ÚDEFAULT_MYSQL_EXErW rX rY Ú
|
parse_args)ÚargvÚparsers r rÊ rÊ P s¡ Ü × $Ñ $´Ô 9FØ
|
×ÑÐ)°DÐÔ9Ø
|
×Ñ ¨dÐÔ3Ø
|
×Ñ ¨tÐÔ4Ø
|
×Ñ
|
¨TÐÔ2Ø
|
×Ñ Ô$Ø
|
×Ñ°ÐÔ6Ø
|
×Ñ ¨tÐÔ4Ø
|
×Ñ ¨H¸tÐÔDØ
|
×ÑÐ*´ÀÐÔEØ
|
×ÑÐ*´ÀÐÔDØ
|
×ÑÐ(¬u¸cÐÔBØ
|
×Ñ ¬sÔ3DÓ/EÐÔFØ
|
×Ѭ"¯*©*¯.©.Ð9MÈ{Ó*[ÐÔ\Ø
|
×Ѭ"¯*©*¯.©.Ð9MÈvÓ*VÐÔWØ
|
×Ѭ"¯*©*¯.©.Ð9MÈvÓ*VÐÔWØ
|
×Ñ ¬b¯j©j¯n©nÐ=OÐQZÓ.[ÐÔ\Ø × Ñ TÓ "Ð"r c óX t | xs t j dd « }t |« S )Nru )rÊ ÚsysrË r· )rË rK s r ÚmainrÏ e s% Ü dÒ*chh q rlÓ +DÜ :Ðr Ú__main__)Úreturnr" )Úartifacté` )r r" r r" r ÚintrÑ r" )r ÚobjectrÑ r" )r r" rÑ r" )r4 r rÑ r" )r: ú list[str]rÑ r" )rK úargparse.NamespacerÑ r" )rK r× rf r" rÑ r" )r r" r r rÑ rÔ )r4 r r zdict[str, str]rÑ ÚNone)rK r× rÑ rÔ )rË rÖ rÑ r× r. )rË zlist[str] | NonerÑ rÔ )&rÇ Ú
|
__future__r rÅ ry r( r« rW r ra rÎ r r Úpathlibr Ú__file__r/ Ú SCRIPT_PATHrm r1 rX rY rÉ r r r% r, r5 r? rP rj r
r r· rÊ rÏ Ú__name__rB r r r Ú<module>rÞ sÏ ðñ õ #ã Û
|
Û Û Û Û Û Û
|
ß 'Ý ñ 8n×$Ñ$Ó& Ø×"Ñ" 1Ñ% Ù Ð(;Ð=\Ó]Ó^Ð óQô)ó Eó=ó)ó ó"N óbó: ó ó= ó@#ô*ð
|
zÒÙ
|
TVÓ
|
Ð ð r
|
