def get_cache_parquet(file_path, da):
if os.path.isfile(file_path):
df = pd.read_parquet(file_path)
if df.iloc[0]['max_da'] == da: return df
return []
class NumpyEncoder(json.JSONEncoder):
def default(self, obj):
if isinstance(obj, np.ndarray):
return obj.tolist()
return json.JSONEncoder.default(self, obj)
def fund_ind(market, order, limit):
sql_str = "SELECT MAX(da) FROM fund_indicators"
items = dbfetchallDict(market, sql_str, "db_fund")
da = items[0]['max']
file_path = 'fund_summary.parquet'
df = get_cache_parquet(file_path, da)
if len(df) > 0:
nav_list = []
is_desc = False if order == 'desc' else True
df = df.sort_values(['score'],ascending=is_desc)
for col in df.columns:
if df[col].dtype == object:
if col == 'nav':
nav_list = df['nav'].tolist()
df['nav'] = df['code']
res = df.to_dict(orient='records')
for i in range(len(res)):
res[i]['nav'] = json.loads(json.dumps(nav_list[i], cls=NumpyEncoder))
return res
sql_str = f"""
SELECT M.code, REPLACE(M.code, ' Equity', '') AS ticker, M.name, E.score, MaxDates.max_da, P.ar, P.sr, P.mdd,
ROUND(CAST(P.sr/2 AS numeric), 2) as sr,
(
SELECT JSON_AGG(JSON_BUILD_OBJECT('da', da::text, 'beta_equity', beta_equity))
FROM stock_ind
WHERE code = e.code
AND da BETWEEN NOW() - INTERVAL '180 days' AND NOW()
) AS nav
INNER JOIN (
SELECT code, MAX(da) AS max_da
FROM stock_ind_e
WHERE code like '00%%' group by code) AS MaxDates
ON E.code = MaxDates.code AND E.da = MaxDates.max_da
AND M.name IS NOT NULL
AND E.da = (SELECT MAX(da) FROM indicators_e)
ORDER BY E.score {order}
LIMIT {limit};
"""
market = "jp"
items = dbfetchallDict(market, sql_str, "fund_ind")
df = pd.DataFrame(items)
df.to_parquet(file_path)
return items
Register as a new user and use Qiita more conveniently
- You get articles that match your needs
- You can efficiently read back useful information
- You can use dark theme