+
    @i5                     *   R t ^ RIt^ RIt^ RIt^ RIt^ RIt^ RIHt ^ RI	H	t	H
t
 ^ RIt^ RIt]P                  ! ]4      tRt. ROt^tR tRR R lltR R	 ltR
 R ltR R ltR R ltR R ltRR R lltRR R lltR t]R8X  d
   ]! 4        R# R# )u  
fetch_jp2y_robust.py
=====================
Robust JP2Y (Japan 2-year yield) data fetcher from Ministry of Finance Japan.

BUGS IDENTIFIED FROM EURUSD EXPERIENCE — ALL FIXED HERE:
=========================================================
EU Bug 1: SSL certificate verification fails on Windows Python 3.14
  Fix: Detect Windows + use ssl.create_default_context() with verify disabled.
       Also try requests library if available (verify=False).

EU Bug 2: Column index hardcoded — breaks if MoF changes CSV structure
  Fix: Read header row first, find '2Y' column by name. Fallback to index 5.

EU Bug 3: Date format YYYY/MM/DD vs YYYY-MM-DD causes parse failures
  Fix: Try multiple date formats. MoF uses YYYY/MM/DD specifically.

EU Bug 4: Empty rows / missing values mid-CSV cause crashes
  Fix: try/except per row, skip on parse error, don't abort entire download.

EU Bug 5: Network timeout with no retry
  Fix: 3 attempts with exponential backoff (5s, 10s, 20s).

EU Bug 6: Stale cached data not detected (DE2Y had this issue)
  Fix: Check last row date — if > 5 business days old, log warning + refetch.

EU Bug 7: BOM characters in CSV header cause column name mismatch
  Fix: Strip BOM with encoding='utf-8-sig' or explicit .lstrip('\ufeff').

EU Bug 8: Server returns HTML error page instead of CSV (MoF outages)
  Fix: Validate first line starts with 'Reference Date' or a date pattern.

EU Bug 9: Duplicate dates in output (re-running appends existing data)
  Fix: Always write fresh file from full download, deduplicate by date.

EU Bug 10: VPS can't reach external URLs (firewall / proxy)
  Fix: Try two mirror approaches, fallback gracefully with clear error message.

DATA SOURCE:
  Ministry of Finance Japan — JGB Benchmark Rates (daily, free, no API key)
  URL: https://www.mof.go.jp/english/policy/jgbs/reference/interest_rate/historical/jgbcme_all.csv
  Format: CSV, header = "Reference Date,0.1Y,0.25Y,0.5Y,1Y,2Y,3Y,..."
  Update: T+1 trading day (after Tokyo close ~18:30 JST = 09:30 UTC)

Run standalone:
  python src/ingestion/fetch_jp2y_robust.py

Integrate into auto_rates_loader.py:
  from ingestion.fetch_jp2y_robust import fetch_jp2y
  fetch_jp2y(output_path)
N)Path)datetime	timedeltaz[https://www.mof.go.jp/english/policy/jgbs/reference/interest_rate/historical/jgbcme_all.csvc                 h    \         P                  ! 4       p RV n        \         P                  V n        V # )zNCreate SSL context that bypasses verification (needed on Windows Python 3.14).F)sslcreate_default_contextcheck_hostname	CERT_NONEverify_mode)ctxs    TC:\Users\Administrator\OneDrive\fx_macro_intraday\src\ingestion\fetch_jp2y_robust.py_make_ssl_ctxr   N   s(    

$
$
&CCCOJ    c                <    V ^8  d   QhR\         R\        R\         /# )   urltimeoutreturnstrint)formats   "r   __annotate__r   V   s&     )N )Ns )NS )N# )Nr   c                   Rp \        4       p\        P                  P                  V RR/R7      p\        P                  P	                  WAVR7      ;_uu_ 4       pVP                  4       pR F  p VP                  V4      u uuRRR4       # 	  VP                  RRR7      uuRRR4       #   \        \        3 d     KT  i ; i  + '       g   i     M5; i  \         d%   pTp\        P                  R	T 24        Rp?MRp?ii ; i ^ RIp	T	P                  YR
R7      pTP                  4        TP                  #   \          d     M1\         d%   pTp\        P                  RT 24        Rp?MRp?ii ; i\#        RT  RT 24      h)z
Download URL to string. Tries:
  1. urllib with SSL bypass (Windows-safe)
  2. requests with verify=False (if installed)
Returns raw text or raises RuntimeError.
Nz
User-Agentz)Mozilla/5.0 (Windows NT 10.0; Win64; x64))headers)r   contextutf-8replace)errorszurllib attempt failed: F)r   verifyzrequests attempt failed: z!All download attempts failed for z: )z	utf-8-sigr   z	shift-jiszlatin-1)r   urllibrequestRequesturlopenreaddecodeUnicodeDecodeErrorLookupError	Exceptionlogdebugrequestsgetraise_for_statustextImportErrorRuntimeError)
r   r   last_errr   reqresprawencer+   s
   &&        r   _download_rawr7   V   sw    H1onn$$!#NO % 
 ^^##C##FF$))+CE::c?* GF F
 ::gi:8 GF +K8  GF  1		+A3/001
	3||C|?yy  3		-aS1223 :3%r(L
MMs   AC C1B-C
C C"
C -C	>CC	CC	C C D#DD2D> >E9E9E9E44E9c                0    V ^8  d   QhR\         R\        /# r   r.   r   )r   bool)r   s   "r   r   r      s        r   c                .   V P                  4        Uu. uF  qP                  4       '       g   K  VNK  	  ppV'       g   R# V^ ,          P                  4       P                  R4      pRV9   d   R# \        V4      ^8  dU   V^,          P                  4       p\        V4      ^
8  d.   VR,          P	                  4       '       d   V^,          R9   d   R# VP                  4       P                  R4      '       g   RVP                  4       9   d   R# R# u upi )	zt
Validate the downloaded text is actually a CSV and not an HTML error page.
MoF returns HTML on maintenance/outage.
F   ﻿zReference DateT:N   Nz<!z<html)/-)
splitlinesstriplstriplenisdigitlower
startswith)r.   llinesfirstseconds   &    r   _validate_csv_contentrK      s    
 )7)1WWYQQ)E7!HNN##H-E5 
5zA~q!v;"!3!3!5!5&)z:Q{{}%%EKKM)A 8s
   DDc                0    V ^8  d   QhR\         R\        /# )r   header_liner   r   )r   s   "r   r   r      s      # # r   c                |   V P                  R4      P                  4       pVP                  R4       Uu. uF!  q"P                  4       P                  R4      NK#  	  pp\        P	                  RVR,           24       \
         F9  pWC9   g   K  VP                  V4      p\        P                  RV RV 24       Vu # 	  \        V4       F@  w  rgVP                  RR	4      \
        9   g   K"  \        P                  RV RV R
24       Vu # 	  \        P                  RVR,           R\         24       \        # u upi )zf
Parse the header row to find which column index contains 2Y yield.
Returns column index (0-indexed).
r<   ,"zCSV columns: :N
   NzFound 2Y column 'z' at index   z (partial match)z$Could not find 2Y column in header: :N   Nz. Falling back to index )rB   rA   splitr)   r*   KNOWN_2Y_LABELSindexinfo	enumerater   warningFALLBACK_2Y_IDX)rM   headerccolslabelidxicols   &       r   _parse_header_for_2y_colrc      s    )//1F,2LL,=>,=qggiooc",=D>IId3i[)* !=**U#CHH({3%@AJ	 ! D/;;sB?2HH([;KLMH "
 KK
.tBxj 9!!0 1	3 ) ?s   'D9c                0    V ^8  d   QhR\         R\        /# )r   date_strr   )r   r   )r   s   "r   r   r      s     9 9# 9( 9r   c                    V P                  4       P                  R4      pR F  p \        P                  ! W4      u # 	  \        RV : 24      h  \         d     K;  i ; i)u:   Parse MoF date string — tries YYYY/MM/DD and YYYY-MM-DD.rP   zCannot parse date: )z%Y/%m/%d%Y-%m-%dz%d/%m/%Yz%m/%d/%Y)rA   r   strptime
ValueError)re   sfmts   &  r   _parse_daterl      sc    s#A?	$$Q,, @
 *8,7
88  		s   AAAc                D    V ^8  d   QhR\         R\        P                  /# r9   )r   pd	DataFrame)r   s   "r   r   r      s     + +S +R\\ +r   c                6   V P                  4        Uu. uF  qP                  4       '       g   K  VNK  	  ppV'       g   \        R4      h^ p\        VR,          4       F3  w  rERVP	                  4       9   g   RVP	                  4       9   g   K1  Tp M	  W#,          p\        V4      p. p^ p	W#^,           R  F  pVP                  R4       U
u. uF!  qP                  4       P                  R4      NK#  	  pp
\        V4      V8:  d   V	^,          p	KZ   \        V^ ,          4      pW,          P                  4       pV'       d   VR9   d   V	^,          p	K  \        V4      pVP                  RVP                  R4      R	V/4       K  	  V	^ 8  d   \        P                  R
V	 R24       \        P                   ! V4      pVP#                  R4      P%                  R4      P'                  RR7      pV# u upi u up
i   \        \        3 d    T	^,          p	 EK`  i ; i)zq
Parse MoF CSV text into clean jp2y DataFrame.
Handles: BOM, missing values, header detection, column detection.
zEmpty CSV content:N   Ndate	referenceNrO   rP   rg   jp2yzSkipped z rows during parsingT)drop)r?   zN/Azn/arS   )r@   rA   ri   rY   rE   rc   rU   rC   rl   floatappendstrftime
IndexErrorr)   r*   rn   ro   drop_duplicatessort_valuesreset_index)r.   rG   rH   
header_idxra   linerM   
col_2y_idxrowsskippedppartsdtval_strvaldfs   &               r   
_parse_csvr      s   
 )7)1WWYQQ)E7,-- JU2Y'TZZ\![DJJL%@J (
 #K*;7JDG1no&/3zz#?!%?u:#qLG
	eAh'B'--/Gg)@@1.CKKZ!8&#FG '" {		HWI%9:;	d	B			F	#	/	/	7	C	C	C	NBIM 8" @ J' 	qLG	s)   G/G/'G4A G9/G99HHc                <    V ^8  d   QhR\         P                  RR/# )r   r   r   N)rn   ro   )r   s   "r   r   r      s"     \ \ \$ \r   c                   V P                   '       d   R# \        P                  ! V R,          P                  R,          4      p\        P                  P                  4       P                  4       p\        P                  ! VP                  4       VP                  4       4      pV^8  d-   \        P                  RVP                  4        RV R24       R# \        P                  RVP                  4        RV R24       R# )	z.Warn if data is stale (> 5 business days old).Nrr   z%JP2Y data may be stale: last date is z (z> business days ago). MoF Japan may be down or URL has changed.z JP2Y data is current: last date z bdays ago))emptyrn   to_datetimeiloc	Timestamptoday	normalizenpbusday_countrr   r)   rZ   rX   )r   	last_dater   bdayss   &   r   _check_stalenessr      s    	xxxr&zr23I""$..0E	 0%**,?Eqy3INN4D3E Fw 89	
 	3INN4D3ERwkZ[r   c          	      j    V ^8  d   QhR\         R\        R\        R\        P                  R,          /# )r   output_pathmax_retriesretry_delayr   N)r   r   rv   rn   ro   )r   s   "r   r   r     s?     D DDD D \\D	Dr   c                "   \        V 4      p V P                  P                  RRR7       TpRp\        ^V^,           4       EF+  p \        P                  RV RV R24       \        \        ^-R7      p\        V4      '       g   \        R4      h\        V4      p\        V4      ^d8  d   \        R	\        V4       R
24      h\        V4       VP                  V RR7       \        P                  R\        V4      R RVR,          P                  ^ ,           RVR,          P                  R,           RVR,          P                  4       R RVR,          P!                  4       R R24       Vu # 	  \        P+                  RV RV R\         24       R#   \"         dp   pTp\        P%                  RT RT 24       YQ8  dA   \        P                  RTR R24       \&        P(                  ! T4       T^,          p Rp?EK   Rp?EK  Rp?ii ; i) ae  
Download JP2Y from MoF Japan with full error handling and retry logic.

Args:
    output_path: Where to save jp2y.csv
    max_retries: Number of download attempts
    retry_delay: Seconds between retries (doubles each attempt)

Returns:
    DataFrame with columns [date, jp2y] or None on failure.
    If None, existing file is preserved (not overwritten).
T)parentsexist_okNzJP2Y download attempt r>   z...)r   zYDownloaded content does not look like MoF CSV (possible HTML error page or server outage)zToo few rows (u"   ) — possible incomplete downloadFrW   zJP2Y saved: rO   z rows | rr       → z | range: [rt   .3fz%, z%]zJP2Y attempt z	 failed: zRetrying in z.0fzs...zJP2Y download failed after z attempts: u   
  → Existing jp2y.csv preserved (if it exists)
  → Model will use last known rates (safe — rates change slowly)
  → Check: internet connectivity, MoF Japan site status
  → URL: r   )r   parentmkdirranger)   rX   r7   MOF_URLrK   ri   r   rC   r   to_csvr   minmaxr(   rZ   timesleeperror)	r   r   r   delayr1   attemptr4   r   r6   s	   &&&      r   
fetch_jp2yr     s   " {#KTD9EHK!O,"	HH-gYa}CHI4C(-- B 
 CB2w} $SWI-OP  R  IIkI/HHs2wqkf:??1%&eBvJOOB,?+@ Af:>>+C0BvJNN4DS3IM
 I7 -L II
%k]+hZ H Y		  !  	HKK-y	!=>$<c{$78

5!
 %	s   D&FHAH		Hc                <    V ^8  d   QhR\         R\        R\        /# )r   r   max_stale_daysr   )r   r   r:   )r   s   "r   r   r   H  s!      d C  r   c                   \        V 4      p V P                  4       '       Ed    \        P                  ! V R.R7      pVP                  '       g   \        P
                  ! VR,          P                  R,          4      p\        P                  P                  4       P                  4       p\        P                  ! VP                  4       VP                  4       4      pWQ8:  d-   \        P                  RVP                  4        RV R24       R# \        P                  RV R24       \#        V 4      pVRJ#   \         d$   p\        P!                  R	T R
24        Rp?L8Rp?ii ; i)u   
Only re-download if the existing file is missing or > max_stale_days old.
Designed for daily scheduler — avoids unnecessary hits to MoF server.

Returns True if data is current (either already was, or just updated).
rr   )parse_dateszJP2Y already current (z, u!    bdays old) — skipping downloadTzJP2Y is u    bdays old — refreshing...z"Could not read existing jp2y.csv: u    — re-downloadingNr   )r   existsrn   read_csvr   r   r   r   r   r   r   r   rr   r)   rX   r(   rZ   r   )r   r   existingr   r   	bdays_oldr6   results   &&      r   update_jp2y_if_staler   H  s(    {#K	U{{;VHEH>>>NN8F+;+@+@+DE	LL..0::<OOINN,<ejjlK	.HH01A0B"$+%FH  HH"9+-IJ $F	  	UKK<QC?RSTT	Us$   *D6 B=D6 D6 6E$EE$c                    \         P                  ! \         P                  RR7       \        \        4      P                  4       P                  ^,          p V R,          R,          R,          R,          p\        R4       \        R4       \        RV 24       \        R4       \        V^R	7      pVEe   \        R\        V4      R R24       \        RVR,          P                  ^ ,           RVR,          P                  R,           24       \        RVR,          P                  4       R RVR,          P                  4       R R24       \        RVR,          P                  R,          R RVR,          P                  R,           24       \        R4       \        VP                  ^4      P                  RR7      4       R
# \        R4       VP                  4       '       d   \        RV 24       \         P"                  ! ^4       R
# )u6   Standalone run — download JP2Y and show diagnostics.z'%(asctime)s [%(levelname)s] %(message)s)levelr   datar4   rateszjp2y.csvu*   JP2Y FETCHER — Ministry of Finance JapanzOutput: )r   Nu   
  ✅ Downloaded rO   z rowsz  Date range: rr   r   z  JP2Y range: rt   r   u   % → %z  Latest:     z% on z
  Sample (last 5 rows):Fr   u0   
  ❌ Download failed — check log for detailsz  Existing file preserved: z<============================================================r   )loggingbasicConfigINFOr   __file__resolver   printr   rC   r   r   r   tail	to_stringr   sysexit)baseoutr   s      r   mainr   j  s   ll8
 >!!#++A.D&=5 7*Z7C	&M	
67	HSE
	&M	CQ	'B	~#CGA;e45r&zq12%6
8K7LMNr&z~~/4F2f:>>;KC:PPQRSr&zr237uRZ__R=P<QRS)+bggaj"""/0AC::<</u56r   __main__)2Y2yz2.0Y2yr)   )   g      @)r   )__doc__r   r   r   r   urllib.requestr    pathlibr   r   r   pandasrn   numpyr   	getLogger__name__r)   r   rV   r[   r   r7   rK   rc   rl   r   r   r   r   r    r   r   <module>r      s   2h  
     (  !   /)NX.<9+\\"DND> zF r   