suminb / finance Goto Github PK
View Code? Open in Web Editor NEW경제적 자유로의 여정
License: BSD 4-Clause "Original" or "Old" License
경제적 자유로의 여정
License: BSD 4-Clause "Original" or "Old" License
Need to provide a tool to manage Asset
records. For example, when we issue a command like this,
finance request_import_stock_values AAPL
we must be able to assure that AAPL
exists or create one by fetching some data from a designated data source such as Yahoo Finance.
Sample data: view-source:https://finance.yahoo.com/quote/AAPL/profile?p=AAPL (root.App.main
)
Verb | Source | Target |
---|---|---|
fetch |
Remote structured/unstructured data | Local structured data |
parse |
Local structured/unstructured data | CSV |
import |
Local structured data | Database |
insert |
Local structured/unstructured data | Database |
This is going to be a non-trivial task, as we are going to have to replace Flask-SQLAlchemy
with pure SQLAlchemy
.
Data can be obtained from
We are currently using a numeric(20, 4)
type to store monetary values, but numeric(10, 4)
should be enough. We would like to see such a change will decrease the amount of storage space needed and potentially increase the overall performance.
https://www.postgresql.org/docs/9.1/static/datatype-numeric.html
WITH x AS (
SELECT count(*) AS ct
, sum(length(t::text)) AS txt_len -- length in characters
, 'asset_value'::regclass AS tbl -- provide (qualified) table name here
FROM asset_value t -- ... and here
)
, y AS (
SELECT ARRAY [pg_relation_size(tbl)
, pg_relation_size(tbl, 'vm')
, pg_relation_size(tbl, 'fsm')
, pg_table_size(tbl)
, pg_indexes_size(tbl)
, pg_total_relation_size(tbl)
, txt_len
] AS val
, ARRAY ['core_relation_size'
, 'visibility_map'
, 'free_space_map'
, 'table_size_incl_toast'
, 'indexes_size'
, 'total_size_incl_toast_and_indexes'
, 'live_rows_in_text_representation'
] AS name
FROM x
)
SELECT unnest(name) AS what
, unnest(val) AS "bytes/ct"
, pg_size_pretty(unnest(val)) AS bytes_pretty
, unnest(val) / ct AS bytes_per_row
FROM x, y
UNION ALL SELECT '------------------------------', NULL, NULL, NULL
UNION ALL SELECT 'row_count', ct, NULL, NULL FROM x
UNION ALL SELECT 'live_tuples', pg_stat_get_live_tuples(tbl), NULL, NULL FROM x
UNION ALL SELECT 'dead_tuples', pg_stat_get_dead_tuples(tbl), NULL, NULL FROM x;
See https://dba.stackexchange.com/questions/23879/measure-the-size-of-a-postgresql-table-row for more details.
what | bytes/ct | bytes_pretty | bytes_per_row
-----------------------------------+----------+--------------+---------------
core_relation_size | 4669440 | 4560 kB | 104
visibility_map | 8192 | 8192 bytes | 0
free_space_map | 24576 | 24 kB | 0
table_size_incl_toast | 4702208 | 4592 kB | 105
indexes_size | 4227072 | 4128 kB | 94
total_size_incl_toast_and_indexes | 8929280 | 8720 kB | 199
live_rows_in_text_representation | 5046614 | 4928 kB | 112
------------------------------ | | |
row_count | 44668 | |
live_tuples | 44668 | |
dead_tuples | 1186 | |
(11 rows)
asset_value
) as asset_value_cloned
.numeric(20, 4)
columns as numeric(10, 4)
.The entire table has been cloned as
CREATE TABLE asset_value_cloned AS SELECT * FROM asset_value
Interestingly, the table size has been shrunk. It's probably because there is no dead_tuples
in the cloned table.
List of relations
Schema | Name | Type | Owner | Size | Description
--------+--------------------+-------+--------+------------+-------------
public | asset_value | table | | 4592 kB |
public | asset_value_cloned | table | | 4408 kB |
As a result, the average row size got smaller. (104
-> 101
bytes)
what | bytes/ct | bytes_pretty | bytes_per_row
-----------------------------------+----------+--------------+---------------
core_relation_size | 4513792 | 4408 kB | 101
visibility_map | 0 | 0 bytes | 0
free_space_map | 0 | 0 bytes | 0
table_size_incl_toast | 4513792 | 4408 kB | 101
indexes_size | 0 | 0 bytes | 0
total_size_incl_toast_and_indexes | 4513792 | 4408 kB | 101
live_rows_in_text_representation | 5046614 | 4928 kB | 112
------------------------------ | | |
row_count | 44668 | |
live_tuples | 44668 | |
dead_tuples | 0 | |
(11 rows)
We altered the type of the numeric columns as follows.
ALTER TABLE asset_value_cloned ALTER open TYPE numeric(10, 4);
ALTER TABLE asset_value_cloned ALTER high TYPE numeric(10, 4);
ALTER TABLE asset_value_cloned ALTER low TYPE numeric(10, 4);
ALTER TABLE asset_value_cloned ALTER close TYPE numeric(10, 4);
No luck. The table size remains unaffected.
what | bytes/ct | bytes_pretty | bytes_per_row
-----------------------------------+----------+--------------+---------------
core_relation_size | 4513792 | 4408 kB | 101
visibility_map | 0 | 0 bytes | 0
free_space_map | 0 | 0 bytes | 0
table_size_incl_toast | 4513792 | 4408 kB | 101
indexes_size | 0 | 0 bytes | 0
total_size_incl_toast_and_indexes | 4513792 | 4408 kB | 101
live_rows_in_text_representation | 5046614 | 4928 kB | 112
------------------------------ | | |
row_count | 44668 | |
live_tuples | 44668 | |
dead_tuples | 0 | |
(11 rows)
numeric(10, 4)
).CREATE TABLE asset_value_cloned (
id bigint NULL,
asset_id bigint NULL,
base_asset_id bigint NULL,
evaluated_at timestamp without time zone NULL,
source asset_value_source NULL,
granularity granularity NULL,
open numeric(10,4) NULL,
high numeric(10,4) NULL,
low numeric(10,4) NULL,
close numeric(10,4) NULL,
volume integer NULL);
INSERT INTO asset_value_cloned SELECT * FROM asset_value;
what | bytes/ct | bytes_pretty | bytes_per_row
-----------------------------------+----------+--------------+---------------
core_relation_size | 4513792 | 4408 kB | 101
visibility_map | 0 | 0 bytes | 0
free_space_map | 24576 | 24 kB | 0
table_size_incl_toast | 4538368 | 4432 kB | 101
indexes_size | 0 | 0 bytes | 0
total_size_incl_toast_and_indexes | 4538368 | 4432 kB | 101
live_rows_in_text_representation | 5046614 | 4928 kB | 112
------------------------------ | | |
row_count | 44668 | |
live_tuples | 44668 | |
dead_tuples | 0 | |
(11 rows)
No changes in terms of bytes_per_row
after the alteration. We will put this one on halt and re-visit in the future.
Instead of directly using datetime.utcnow()
. In that way, if we ever decide to use a different data type (e.g., float
or int
), our job becomes a bit easier.
Load AssetValue
from a CSV file.
Travis CI support 'stages'. We could have a stage to run mypy
for type checking.
Currently defined as:
-- auto-generated definition
create unique index asset_value_asset_id_evaluated_at_granularity_key
on asset_value (asset_id, evaluated_at, granularity);
We're going to need source
as the fourth element in the unique key as:
(asset_id, evaluated_at, granularity, source)
We could use a prefix for environment variables in use. Perhaps SBF_
or something like that.
We need to insert a large quantity of AssetValue
records, but it's too slow. Takes more than 60sec to insert tens of rows.
This may be helpful...
Or we could use a thread pool to make things faster. We also could send all the insertion queries in a single batch (within a single transaction) but that will greatly increase the likelihood of failures, as we will try to insert data over overlapping periods most of the time.
화면번호 9465
yyyy/mm/dd
A daily sequential number
???
USD, KRW, ...
Looks like some internal code
KR7005380001 현대차
KR7232080002 TIGER 코스닥150
US8552441094 스타벅스
Number of shares
Unit price in the specified currency
전자공시 데이를 가져와서 구조화된 형태로 저장하고 관리함으로써 나중에 필요한 형태로 가공하거나, 필터링 해서 알림을 줄 수 있는 시스템을 만든다.
curl 'http://m.dart.fss.or.kr/md3002/search.st?currentPage=2&maxResultCnt=15&corporationType=&textCrpNm=%EC%82%BC%EC%84%B1%EC%A0%84%EC%9E%90&textCrpCik=00126380&startDate=20160907&endDate=20170307&publicType=&publicOrgType=&reportName=&textPresenterNm=&finalReport=&lastRcpNo=20170303800956&totalPage=&textTerm=&_=1488893683281' -H 'Cookie: JSESSIONID=xkpZNbiUquaV4eRUBcjdaEa2OYEYZJhDKRMWmOrKoM6ixFJogocZBG5UhVgpY2lz.dart2_servlet_engine1' -H 'Accept-Encoding: gzip, deflate, sdch' -H 'Accept-Language: en-US,en;q=0.8,ko;q=0.6' -H 'User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/56.0.2924.87 Safari/537.36' -H 'Content-Type: application/x-www-form-urlencoded' -H 'Accept: application/json, text/javascript, */*; q=0.01' -H 'Referer: http://m.dart.fss.or.kr/html_mdart/MD1005.html?startDate=20160907&endDate=20170307&textCrpCik=00126380&screenid=md1001&stype=md1001¤tPage=1&corporationType=&textCrpNm=%EC%82%BC%EC%84%B1%EC%A0%84%EC%9E%90&textCrpNmAuto=%EC%82%BC%EC%84%B1%EC%A0%84%EC%9E%90' -H 'X-Requested-With: XMLHttpRequest' -H 'Connection: keep-alive' --compressed
{
"rlist": [{
"curr_page": 3,
"row_num": 31,
"bsn_yr": null,
"crc_gubun": "N",
"crc_nm": null,
"crp_rgs_cls": "P",
"dsm_crp_cik": "00126380",
"dcm_id": null,
"dcm_no": null,
"dcm_nm": null,
"dcm_int_tp": "N",
"flr_cik": "00725534",
"ifm_nm": "삼성전자",
"ifm_nm2": "권오현",
"rpt_id": "00634",
"rpt_nm": "임원ㆍ주요주주특정증권등소유상황보고서",
"rcp_no": "20161222000173",
"rcp_dm": "2016.12.22",
"sub_com_tp": "N",
"rpt_title": null,
"dcm_tp": "0",
"rptNm": "임원ㆍ주요주주특정증권등소유상황보고서 "
}, {
NOTE: 여기서 20161222000173
와 같은 값은 일종의 식별자로 보고서로 바로 넘어갈 수 있는 키가 된다.
http://m.dart.fss.or.kr/html_mdart/MD1007.html?rcpNo=20161222000173
문서의 세부 항목은 다음과 같이 접근 가능하다.
curl 'http://m.dart.fss.or.kr/report/main.do' -H 'Cookie: JSESSIONID=xkpZNbiUquaV4eRUBcjdaEa2OYEYZJhDKRMWmOrKoM6ixFJogocZBG5UhVgpY2lz.dart2_servlet_engine1' -H 'Origin: http://m.dart.fss.or.kr' -H 'Accept-Encoding: gzip, deflate' -H 'Accept-Language: en-US,en;q=0.8,ko;q=0.6' -H 'User-Agent: Mozilla/5.0 (Windows NT 10.0; WOW64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/56.0.2924.87 Safari/537.36' -H 'Content-Type: application/x-www-form-urlencoded' -H 'Accept: text/html, */*; q=0.01' -H 'Referer: http://m.dart.fss.or.kr/html_mdart/MD1007.html?rcpNo=20161222000173' -H 'X-Requested-With: XMLHttpRequest' -H 'Connection: keep-alive' --data 'rcpNo=20161222000173&dcmNo=5390164&eleId=2' --compressed
여기서 eleId
가 세부 항목의 아이디로 보여진다.
Current:
1514903400, 195.77999877929688, 199.5, 194.5, 199.35000610351562, 8890400, yahoo
What we want:
2018-01-01 10:00:00, 195.77999877929688, 199.5, 194.5, 199.35000610351562, 8890400, yahoo
See ISO 8601.
For each row of AssetValue
, we need to record the source.
화면번호 0650
(거래내역조회), '한줄로 보기'
Date format: yyyy/mm/dd
Example: 2016/12/29
An one-based daily sequential number
Looks like some kind of secondary index
lines = open('transaction_types.txt').readlines()
types = set([x.strip() for x in lines])
for t in types:
print('- {}'.format(t))
Total transaction amount (including fees and taxes)
Some examples:
수량 가격 예수금금잔 수수료 변제금액 미수발생금액 유가잔고 지점명 매체구분 의뢰인명/메모 제세금 단가 상대기관 상대계좌명 입금메모 출금메모
종목 이름
, CPR 코드
매핑이 필요하다.
e.g., 삼성전자, 00126380
Oh, hell no. It ain't gonna happen.
We could dedicate one record in DynamoDB to store DB_URL
, but is it worth to introduce additional complexity within our architecture just to accomplish this?
Pretty much the same idea as Option 2, but in S3.
Embed DB_URL
when assembling a Lambda .zip
package.
terraform apply -var db_url=$DB_URL
Move sample files under tests/data
to tests/sample
.
제목이 곧 내용.goport
브랜치에서는 이미 DB_URL
을 사용하고 있다.
See https://pandas-datareader.readthedocs.io/en/latest/remote_data.html#yahoo-finance-quotes
And get rid of all home-brewed data fetching stuff.
See feature/dart-web
branch.
It seems that Google Finance no longer provides historical financial data: http://www.google.com/finance/historical
We need at least two sources (or one reliable source; i.e., a paid one) in order to build a robust system.
Account.net_worth()
should be able to aggregate all asset values and categorize them into base assets.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.