Coder Social home page Coder Social logo

lifetrading's Introduction

Life Trading Exercise

John Mee 31-Jul-2019

Data Import Task

  • Your task is to design a general process to import external csv files into a database
  • You will need to specify the database schema, feel free to use what you see as the most appropriate database technology
  • You will need to provide code to process files and import them to the database, feel free to use any language that fits well with your choice of database technology
  • You will need to document how you see the process running in an automated way so that files are processed within 10 minutes of their arrival

The requirements are as follows

  • New file reports are dropped into a local folder at regular times in the day
  • Multiple types of reports may be dropped into this folder
  • Each file should be processed, and a status recorded to the database to say they have arrived
  • For specific report types, the contents of the file should also be imported into the database and the status updated to imported
  • You are free to add any additional status values as appropriate
  • The modification time of the file represents the download date/time, this should be recorded to the database
  • Each report type has a specific filename pattern, this includes the report type but also additional information relating to that file. There will always be a trade date encoded in the filename.
  • Different report types may have different filename patterns
  • Each report type has a different set of columns, all columns should be imported

Bonus requirement

  • Each report may be revised during the day, the prior report should be considered out of date and replaced with this one.

Discussion

Files are processed within 10 minutes of their arrival?

Not guaranteed as it depends how long it takes to process a file on the available resource. I didn't multiprocess it, but had that in mind to do and expect it to be agreeable to that enhancement as the list of work to do is indeed a list. As implemented, this is a not a 'polling' algorithm exactly: instead it sleeps for the defined period when there is nothing to do.

I did play around with watchdog, and got it working easily enough, but discovered that the default unittests didn't play nice with it's multithreading; I couldn't control the thread of the test to set it to watch then programmatically create files to test it. It was sucking up time so I ditched it for the conventional option of 'polling'.

I also ran out of time to fully implement the 'bonus' of file updates. Some of that is there, but to finish it off, I was leaning toward adding a column to the trade activity table which referenced the file the trade came from (or row id in filelog table); thus it would be easy to identify and delete the rows which were being replaced.

I don't know if you'd consider it cheating or resourcefulness, but I discovered and took advantage of the dataset library which implements a simple ORM over SQLAlchemy which provides for many database implementations. I've used sqlite3 for simplicity.

Unittest

The unittest looks something like this.

john@bigbox:~/projects/lifetrading$ python3 -m unittest lifetrading_test.py 
2019-07-31 18:34:50,087:root:DEBUG:CsvFiles: 
2019-07-31 18:34:50,095:alembic.runtime.migration:INFO:Context impl SQLiteImpl.
2019-07-31 18:34:50,095:alembic.runtime.migration:INFO:Will assume non-transactional DDL.
2019-07-31 18:34:50,100:alembic.runtime.migration:INFO:Context impl SQLiteImpl.
2019-07-31 18:34:50,100:alembic.runtime.migration:INFO:Will assume non-transactional DDL.
2019-07-31 18:34:50,112:dataset.table:DEBUG:Column exists: name
2019-07-31 18:34:50,112:dataset.table:DEBUG:Column exists: mtime
2019-07-31 18:34:50,112:dataset.table:DEBUG:Column exists: status
2019-07-31 18:34:50,114:root:DEBUG:[]
2019-07-31 18:34:50,115:root:DEBUG:[]
2019-07-31 18:34:50,120:root:DEBUG:CsvFiles: 
2019-07-31 18:34:50,124:dataset.table:DEBUG:Column exists: name
2019-07-31 18:34:50,124:dataset.table:DEBUG:Column exists: mtime
2019-07-31 18:34:50,124:dataset.table:DEBUG:Column exists: status
2019-07-31 18:34:50,126:dataset.table:DEBUG:Column exists: name
2019-07-31 18:34:50,126:dataset.table:DEBUG:Column exists: mtime
2019-07-31 18:34:50,126:dataset.table:DEBUG:Column exists: status
2019-07-31 18:34:50,126:root:DEBUG:[]
2019-07-31 18:34:50,127:root:DEBUG:[]
2019-07-31 18:34:50,128:root:DEBUG:CsvFiles: TradeActivityReport-LIFETRADING-1-1.csv
2019-07-31 18:34:50,131:dataset.table:DEBUG:Column exists: name
2019-07-31 18:34:50,131:dataset.table:DEBUG:Column exists: mtime
2019-07-31 18:34:50,131:dataset.table:DEBUG:Column exists: status
2019-07-31 18:34:50,132:dataset.table:DEBUG:Column exists: name
2019-07-31 18:34:50,132:dataset.table:DEBUG:Column exists: mtime
2019-07-31 18:34:50,132:dataset.table:DEBUG:Column exists: status
2019-07-31 18:34:50,150:root:INFO:Imported 2 rows from TradeActivityReport-LIFETRADING-1-1.csv
2019-07-31 18:34:50,151:root:DEBUG:[OrderedDict([('id', 1), ('name', 'TradeActivityReport-LIFETRADING-1-1.csv'), ('mtime', 1564562090127623505), ('status', 'IMPORTED')])]
2019-07-31 18:34:50,151:root:DEBUG:[OrderedDict([('id', 1), ('Column1', 'data1'), ('Column2', 'data2'), ('Column3', 'data3')]), OrderedDict([('id', 2), ('Column1', 'data4'), ('Column2', 'data5'), ('Column3', 'data6')])]
2019-07-31 18:34:50,153:root:DEBUG:CsvFiles: TradeActivityReport-LIFETRADING-1-1.csv
2019-07-31 18:34:50,154:dataset.table:DEBUG:Column exists: name
2019-07-31 18:34:50,154:dataset.table:DEBUG:Column exists: mtime
2019-07-31 18:34:50,154:dataset.table:DEBUG:Column exists: status
2019-07-31 18:34:50,163:root:DEBUG:[OrderedDict([('id', 1), ('name', 'TradeActivityReport-LIFETRADING-1-1.csv'), ('mtime', 1564562090127623505), ('status', 'UPDATED')])]
2019-07-31 18:34:50,168:root:DEBUG:[OrderedDict([('id', 1), ('Column1', 'data1'), ('Column2', 'data2'), ('Column3', 'data3')]), OrderedDict([('id', 2), ('Column1', 'data4'), ('Column2', 'data5'), ('Column3', 'data6')])]
.2019-07-31 18:34:50,174:alembic.runtime.migration:INFO:Context impl SQLiteImpl.
2019-07-31 18:34:50,175:alembic.runtime.migration:INFO:Will assume non-transactional DDL.
2019-07-31 18:34:50,178:alembic.runtime.migration:INFO:Context impl SQLiteImpl.
2019-07-31 18:34:50,178:alembic.runtime.migration:INFO:Will assume non-transactional DDL.
2019-07-31 18:34:50,181:dataset.table:DEBUG:Column exists: name
2019-07-31 18:34:50,181:dataset.table:DEBUG:Column exists: mtime
2019-07-31 18:34:50,182:dataset.table:DEBUG:Column exists: status
2019-07-31 18:34:50,183:dataset.table:DEBUG:Column exists: name
2019-07-31 18:34:50,183:dataset.table:DEBUG:Column exists: mtime
2019-07-31 18:34:50,183:dataset.table:DEBUG:Column exists: status
2019-07-31 18:34:50,184:dataset.table:DEBUG:Column exists: name
2019-07-31 18:34:50,184:dataset.table:DEBUG:Column exists: mtime
2019-07-31 18:34:50,184:dataset.table:DEBUG:Column exists: status
.2019-07-31 18:34:50,188:root:DEBUG:CsvFiles: 
2019-07-31 18:34:50,189:root:DEBUG:CsvFiles: 
2019-07-31 18:34:50,189:root:DEBUG:CsvFiles: foo.csv
2019-07-31 18:34:50,189:root:DEBUG:CsvFiles: bar.csv, foo.csv
...
----------------------------------------------------------------------
Ran 5 tests in 0.108s

OK

Run test

A transcript of running it and dropping the test file into the directory looks like this:

john@bigbox:~/projects/lifetrading$ ls
lifetrading.py  lifetrading_test.py  __pycache__  README.md  requirements.txt  testfiles  venv
john@bigbox:~/projects/lifetrading$ mkdir dropdir
john@bigbox:~/projects/lifetrading$ ./lifetrading.py --help
usage: lifetrading.py [-h] db_string download_path poll_seconds

Data Import task for lifetrading.com.au

positional arguments:
  db_string      Database connection string.
  download_path  Path to the download directory.
  poll_seconds   Number of seconds to sleep when nothing to do.

optional arguments:
  -h, --help     show this help message and exit
john@bigbox:~/projects/lifetrading$ ./lifetrading.py sqlite:///database.sqlite dropdir 30 &
[1] 28407
john@bigbox:~/projects/lifetrading$ 2019-07-31 18:47:47,138:root:INFO:Starting: Path "dropdir" DB "sqlite:///database.sqlite" Poll 30
2019-07-31 18:47:47,138:root:DEBUG:CsvFiles: 
2019-07-31 18:47:47,151:alembic.runtime.migration:INFO:Context impl SQLiteImpl.
2019-07-31 18:47:47,151:alembic.runtime.migration:INFO:Will assume non-transactional DDL.
2019-07-31 18:47:47,156:alembic.runtime.migration:INFO:Context impl SQLiteImpl.
2019-07-31 18:47:47,156:alembic.runtime.migration:INFO:Will assume non-transactional DDL.
2019-07-31 18:47:47,167:dataset.table:DEBUG:Column exists: name
2019-07-31 18:47:47,167:dataset.table:DEBUG:Column exists: mtime
2019-07-31 18:47:47,167:dataset.table:DEBUG:Column exists: status
2019-07-31 18:47:47,169:root:DEBUG:[]
2019-07-31 18:47:47,170:root:DEBUG:[]

john@bigbox:~/projects/lifetrading$ cp testfiles/TradeActivityReport-LIFETRADING-20181004-0500561.csv dropdir/

2019-07-31 18:48:17,187:root:DEBUG:CsvFiles: TradeActivityReport-LIFETRADING-20181004-0500561.csv
2019-07-31 18:48:17,193:dataset.table:DEBUG:Column exists: name
2019-07-31 18:48:17,193:dataset.table:DEBUG:Column exists: mtime
2019-07-31 18:48:17,193:dataset.table:DEBUG:Column exists: status
2019-07-31 18:48:17,194:dataset.table:DEBUG:Column exists: name
2019-07-31 18:48:17,194:dataset.table:DEBUG:Column exists: mtime
2019-07-31 18:48:17,194:dataset.table:DEBUG:Column exists: status
2019-07-31 18:48:17,215:root:INFO:Imported 3 rows from TradeActivityReport-LIFETRADING-20181004-0500561.csv
2019-07-31 18:48:17,216:root:DEBUG:[OrderedDict([('id', 1), ('name', 'TradeActivityReport-LIFETRADING-20181004-0500561.csv'), ('mtime', 1564562883820384057), ('status', 'IMPORTED')])]
2019-07-31 18:48:17,216:root:DEBUG:[OrderedDict([('id', 1), ('CounterpartyShor', 'LIFTRLTDSY'), ('TradeID', '20181003-00238'), ('TradeDate', '03-Oct-18'), ('SettlementDate', '05-Oct-18'), ('Ticker', 'CYB.AX'), ('ISIN', 'AU000000CYB7'), ('Name', 'CYBG PLC - CDI'), ('ProductShortName', 'LTASXUSDA'), ('Strategy(ExternalID)', ''), ('Qty', '-28,000'), ('Type', 'Sell-Open'), ('CCYUnderlying', 'AUD'), ('GrossPrice(Local)', '5.6136'), ('FXRate', '0.7179'), ('GrossPriceSwap', '4.0299'), ('Comms', '0.0250%'), ('MarketCharges', '0.0000%'), ('NetPriceSwap', '4.0289'), ('SwapCCY', 'USD'), ('TradedNotionalAmt', '112,807.97'), ('FundingRate', 'USD 1M LIBOR'), ('Spread', '-1.0000%'), ('Performance', 'Monthly'), ('FundingReset', 'Monthly'), ('DividendEntitlement', '100.0000%'), ('InitialMargin', '5.00'), ('ProductType', 'PS'), ('RemainingNotionalAmt', '-365,897.27'), ('Unique TransactionID', '1030240934SP-LTASXAUDACYB.AX'), ('TradeTime', '06:58:04'), ('TotalComm', '-28.209045'), ('TotalMarket', '0.000000'), ('OrderIns', 'Other'), ('InstrumentIdentifier', 'SESTXC'), ('Venue', 'XXXX')]), OrderedDict([('id', 2), ('CounterpartyShor', 'LIFTRLTDSY'), ('TradeID', '20181003-00082'), ('TradeDate', '03-Oct-18'), ('SettlementDate', '09-Oct-18'), ('Ticker', '6758.T'), ('ISIN', 'JP3435000009'), ('Name', 'SONY CORP'), ('ProductShortName', 'LTJPUSDA'), ('Strategy(ExternalID)', ''), ('Qty', '8,600'), ('Type', 'Buy-Open'), ('CCYUnderlying', 'JPY'), ('GrossPrice(Local)', '6,623.6977'), ('FXRate', '113.7760'), ('GrossPriceSwap', '58.2170'), ('Comms', '0.0250%'), ('MarketCharges', '0.0000%'), ('NetPriceSwap', '58.2316'), ('SwapCCY', 'USD'), ('TradedNotionalAmt', '500,791.39'), ('FundingRate', 'USD 1M LIBOR'), ('Spread', '0.4000%'), ('Performance', 'Monthly'), ('FundingReset', 'Monthly'), ('DividendEntitlement', '90.0000%'), ('InitialMargin', '2.50'), ('ProductType', 'PS'), ('RemainingNotionalAmt', '500,791.39'), ('Unique TransactionID', '1030240934SP-LTJPUSDA6758.T'), ('TradeTime', '06:10:02'), ('TotalComm', '125.166555'), ('TotalMarket', '0.000000'), ('OrderIns', 'Other'), ('InstrumentIdentifier', 'SESTXC'), ('Venue', 'XXXX')]), OrderedDict([('id', 3), ('CounterpartyShor', 'LIFTRLTDSY'), ('TradeID', '20181003-00083'), ('TradeDate', '03-Oct-18'), ('SettlementDate', '09-Oct-18'), ('Ticker', '7751.T'), ('ISIN', 'JP3242800005'), ('Name', 'CANON INC'), ('ProductShortName', 'LTJPUSDA'), ('Strategy(ExternalID)', ''), ('Qty', '-7,000'), ('Type', 'Sell-Open'), ('CCYUnderlying', 'JPY'), ('GrossPrice(Local)', '3,642.4657'), ('FXRate', '113.7760'), ('GrossPriceSwap', '32.0144'), ('Comms', '0.0250%'), ('MarketCharges', '0.0000%'), ('NetPriceSwap', '32.0064'), ('SwapCCY', 'USD'), ('TradedNotionalAmt', '224,044.48'), ('FundingRate', 'USD 1M LIBOR'), ('Spread', '-0.5000%'), ('Performance', 'Monthly'), ('FundingReset', 'Monthly'), ('DividendEntitlement', '100.0000%'), ('InitialMargin', '2.50'), ('ProductType', 'PS'), ('RemainingNotionalAmt', '-224,044.48'), ('Unique TransactionID', '1030240934SP-LTJPUSDA7751.T'), ('TradeTime', '06:10:02'), ('TotalComm', '-56.025128'), ('TotalMarket', '0.000000'), ('OrderIns', 'Other'), ('InstrumentIdentifier', 'SESTXC'), ('Venue', 'XXXX')])]
2019-07-31 18:48:17,217:root:DEBUG:CsvFiles: TradeActivityReport-LIFETRADING-20181004-0500561.csv
2019-07-31 18:48:17,218:dataset.table:DEBUG:Column exists: name
2019-07-31 18:48:17,218:dataset.table:DEBUG:Column exists: mtime
2019-07-31 18:48:17,218:dataset.table:DEBUG:Column exists: status
2019-07-31 18:48:17,229:root:DEBUG:[OrderedDict([('id', 1), ('name', 'TradeActivityReport-LIFETRADING-20181004-0500561.csv'), ('mtime', 1564562883820384057), ('status', 'UPDATED')])]
2019-07-31 18:48:17,248:root:DEBUG:[OrderedDict([('id', 1), ('CounterpartyShor', 'LIFTRLTDSY'), ('TradeID', '20181003-00238'), ('TradeDate', '03-Oct-18'), ('SettlementDate', '05-Oct-18'), ('Ticker', 'CYB.AX'), ('ISIN', 'AU000000CYB7'), ('Name', 'CYBG PLC - CDI'), ('ProductShortName', 'LTASXUSDA'), ('Strategy(ExternalID)', ''), ('Qty', '-28,000'), ('Type', 'Sell-Open'), ('CCYUnderlying', 'AUD'), ('GrossPrice(Local)', '5.6136'), ('FXRate', '0.7179'), ('GrossPriceSwap', '4.0299'), ('Comms', '0.0250%'), ('MarketCharges', '0.0000%'), ('NetPriceSwap', '4.0289'), ('SwapCCY', 'USD'), ('TradedNotionalAmt', '112,807.97'), ('FundingRate', 'USD 1M LIBOR'), ('Spread', '-1.0000%'), ('Performance', 'Monthly'), ('FundingReset', 'Monthly'), ('DividendEntitlement', '100.0000%'), ('InitialMargin', '5.00'), ('ProductType', 'PS'), ('RemainingNotionalAmt', '-365,897.27'), ('Unique TransactionID', '1030240934SP-LTASXAUDACYB.AX'), ('TradeTime', '06:58:04'), ('TotalComm', '-28.209045'), ('TotalMarket', '0.000000'), ('OrderIns', 'Other'), ('InstrumentIdentifier', 'SESTXC'), ('Venue', 'XXXX')]), OrderedDict([('id', 2), ('CounterpartyShor', 'LIFTRLTDSY'), ('TradeID', '20181003-00082'), ('TradeDate', '03-Oct-18'), ('SettlementDate', '09-Oct-18'), ('Ticker', '6758.T'), ('ISIN', 'JP3435000009'), ('Name', 'SONY CORP'), ('ProductShortName', 'LTJPUSDA'), ('Strategy(ExternalID)', ''), ('Qty', '8,600'), ('Type', 'Buy-Open'), ('CCYUnderlying', 'JPY'), ('GrossPrice(Local)', '6,623.6977'), ('FXRate', '113.7760'), ('GrossPriceSwap', '58.2170'), ('Comms', '0.0250%'), ('MarketCharges', '0.0000%'), ('NetPriceSwap', '58.2316'), ('SwapCCY', 'USD'), ('TradedNotionalAmt', '500,791.39'), ('FundingRate', 'USD 1M LIBOR'), ('Spread', '0.4000%'), ('Performance', 'Monthly'), ('FundingReset', 'Monthly'), ('DividendEntitlement', '90.0000%'), ('InitialMargin', '2.50'), ('ProductType', 'PS'), ('RemainingNotionalAmt', '500,791.39'), ('Unique TransactionID', '1030240934SP-LTJPUSDA6758.T'), ('TradeTime', '06:10:02'), ('TotalComm', '125.166555'), ('TotalMarket', '0.000000'), ('OrderIns', 'Other'), ('InstrumentIdentifier', 'SESTXC'), ('Venue', 'XXXX')]), OrderedDict([('id', 3), ('CounterpartyShor', 'LIFTRLTDSY'), ('TradeID', '20181003-00083'), ('TradeDate', '03-Oct-18'), ('SettlementDate', '09-Oct-18'), ('Ticker', '7751.T'), ('ISIN', 'JP3242800005'), ('Name', 'CANON INC'), ('ProductShortName', 'LTJPUSDA'), ('Strategy(ExternalID)', ''), ('Qty', '-7,000'), ('Type', 'Sell-Open'), ('CCYUnderlying', 'JPY'), ('GrossPrice(Local)', '3,642.4657'), ('FXRate', '113.7760'), ('GrossPriceSwap', '32.0144'), ('Comms', '0.0250%'), ('MarketCharges', '0.0000%'), ('NetPriceSwap', '32.0064'), ('SwapCCY', 'USD'), ('TradedNotionalAmt', '224,044.48'), ('FundingRate', 'USD 1M LIBOR'), ('Spread', '-0.5000%'), ('Performance', 'Monthly'), ('FundingReset', 'Monthly'), ('DividendEntitlement', '100.0000%'), ('InitialMargin', '2.50'), ('ProductType', 'PS'), ('RemainingNotionalAmt', '-224,044.48'), ('Unique TransactionID', '1030240934SP-LTJPUSDA7751.T'), ('TradeTime', '06:10:02'), ('TotalComm', '-56.025128'), ('TotalMarket', '0.000000'), ('OrderIns', 'Other'), ('InstrumentIdentifier', 'SESTXC'), ('Venue', 'XXXX')])]
2019-07-31 18:48:47,276:root:DEBUG:CsvFiles: TradeActivityReport-LIFETRADING-20181004-0500561.csv
2019-07-31 18:48:47,278:dataset.table:DEBUG:Column exists: name
2019-07-31 18:48:47,278:dataset.table:DEBUG:Column exists: mtime
2019-07-31 18:48:47,278:dataset.table:DEBUG:Column exists: status
2019-07-31 18:48:47,287:root:DEBUG:[OrderedDict([('id', 1), ('name', 'TradeActivityReport-LIFETRADING-20181004-0500561.csv'), ('mtime', 1564562883820384057), ('status', 'UPDATED')])]
2019-07-31 18:48:47,292:root:DEBUG:[OrderedDict([('id', 1), ('CounterpartyShor', 'LIFTRLTDSY'), ('TradeID', '20181003-00238'), ('TradeDate', '03-Oct-18'), ('SettlementDate', '05-Oct-18'), ('Ticker', 'CYB.AX'), ('ISIN', 'AU000000CYB7'), ('Name', 'CYBG PLC - CDI'), ('ProductShortName', 'LTASXUSDA'), ('Strategy(ExternalID)', ''), ('Qty', '-28,000'), ('Type', 'Sell-Open'), ('CCYUnderlying', 'AUD'), ('GrossPrice(Local)', '5.6136'), ('FXRate', '0.7179'), ('GrossPriceSwap', '4.0299'), ('Comms', '0.0250%'), ('MarketCharges', '0.0000%'), ('NetPriceSwap', '4.0289'), ('SwapCCY', 'USD'), ('TradedNotionalAmt', '112,807.97'), ('FundingRate', 'USD 1M LIBOR'), ('Spread', '-1.0000%'), ('Performance', 'Monthly'), ('FundingReset', 'Monthly'), ('DividendEntitlement', '100.0000%'), ('InitialMargin', '5.00'), ('ProductType', 'PS'), ('RemainingNotionalAmt', '-365,897.27'), ('Unique TransactionID', '1030240934SP-LTASXAUDACYB.AX'), ('TradeTime', '06:58:04'), ('TotalComm', '-28.209045'), ('TotalMarket', '0.000000'), ('OrderIns', 'Other'), ('InstrumentIdentifier', 'SESTXC'), ('Venue', 'XXXX')]), OrderedDict([('id', 2), ('CounterpartyShor', 'LIFTRLTDSY'), ('TradeID', '20181003-00082'), ('TradeDate', '03-Oct-18'), ('SettlementDate', '09-Oct-18'), ('Ticker', '6758.T'), ('ISIN', 'JP3435000009'), ('Name', 'SONY CORP'), ('ProductShortName', 'LTJPUSDA'), ('Strategy(ExternalID)', ''), ('Qty', '8,600'), ('Type', 'Buy-Open'), ('CCYUnderlying', 'JPY'), ('GrossPrice(Local)', '6,623.6977'), ('FXRate', '113.7760'), ('GrossPriceSwap', '58.2170'), ('Comms', '0.0250%'), ('MarketCharges', '0.0000%'), ('NetPriceSwap', '58.2316'), ('SwapCCY', 'USD'), ('TradedNotionalAmt', '500,791.39'), ('FundingRate', 'USD 1M LIBOR'), ('Spread', '0.4000%'), ('Performance', 'Monthly'), ('FundingReset', 'Monthly'), ('DividendEntitlement', '90.0000%'), ('InitialMargin', '2.50'), ('ProductType', 'PS'), ('RemainingNotionalAmt', '500,791.39'), ('Unique TransactionID', '1030240934SP-LTJPUSDA6758.T'), ('TradeTime', '06:10:02'), ('TotalComm', '125.166555'), ('TotalMarket', '0.000000'), ('OrderIns', 'Other'), ('InstrumentIdentifier', 'SESTXC'), ('Venue', 'XXXX')]), OrderedDict([('id', 3), ('CounterpartyShor', 'LIFTRLTDSY'), ('TradeID', '20181003-00083'), ('TradeDate', '03-Oct-18'), ('SettlementDate', '09-Oct-18'), ('Ticker', '7751.T'), ('ISIN', 'JP3242800005'), ('Name', 'CANON INC'), ('ProductShortName', 'LTJPUSDA'), ('Strategy(ExternalID)', ''), ('Qty', '-7,000'), ('Type', 'Sell-Open'), ('CCYUnderlying', 'JPY'), ('GrossPrice(Local)', '3,642.4657'), ('FXRate', '113.7760'), ('GrossPriceSwap', '32.0144'), ('Comms', '0.0250%'), ('MarketCharges', '0.0000%'), ('NetPriceSwap', '32.0064'), ('SwapCCY', 'USD'), ('TradedNotionalAmt', '224,044.48'), ('FundingRate', 'USD 1M LIBOR'), ('Spread', '-0.5000%'), ('Performance', 'Monthly'), ('FundingReset', 'Monthly'), ('DividendEntitlement', '100.0000%'), ('InitialMargin', '2.50'), ('ProductType', 'PS'), ('RemainingNotionalAmt', '-224,044.48'), ('Unique TransactionID', '1030240934SP-LTJPUSDA7751.T'), ('TradeTime', '06:10:02'), ('TotalComm', '-56.025128'), ('TotalMarket', '0.000000'), ('OrderIns', 'Other'), ('InstrumentIdentifier', 'SESTXC'), ('Venue', 'XXXX')])]

john@bigbox:~/projects/lifetrading$ kill -9 28407
john@bigbox:~/projects/lifetrading$ ls
database.sqlite  dropdir  lifetrading.py  lifetrading_test.py  __pycache__  README.md  requirements.txt  testfiles  venv
[1]+  Killed                  ./lifetrading.py sqlite:///database.sqlite dropdir 30
john@bigbox:~/projects/lifetrading$ sqlite3 database.sqlite 
SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.
sqlite> .tables
filelog        tradeactivity
sqlite> .dump filelog
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE filelog (
    id INTEGER NOT NULL, 
    name VARCHAR, mtime BIGINT, status VARCHAR, 
    PRIMARY KEY (id)
);
INSERT INTO filelog VALUES(1,'TradeActivityReport-LIFETRADING-20181004-0500561.csv',1564562883820384057,'UPDATED');
CREATE INDEX ix_filelog_91b3e5b34aeef828 ON filelog (name, mtime, status);
COMMIT;
sqlite> .dump tradeactivity
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE tradeactivity (
    id INTEGER NOT NULL, 
    "CounterpartyShor" TEXT, 
    "TradeID" TEXT, 
    "TradeDate" TEXT, 
    "SettlementDate" TEXT, 
    "Ticker" TEXT, 
    "ISIN" TEXT, 
    "Name" TEXT, 
    "ProductShortName" TEXT, 
    "Strategy(ExternalID)" TEXT, 
    "Qty" TEXT, 
    "Type" TEXT, 
    "CCYUnderlying" TEXT, 
    "GrossPrice(Local)" TEXT, 
    "FXRate" TEXT, 
    "GrossPriceSwap" TEXT, 
    "Comms" TEXT, 
    "MarketCharges" TEXT, 
    "NetPriceSwap" TEXT, 
    "SwapCCY" TEXT, 
    "TradedNotionalAmt" TEXT, 
    "FundingRate" TEXT, 
    "Spread" TEXT, 
    "Performance" TEXT, 
    "FundingReset" TEXT, 
    "DividendEntitlement" TEXT, 
    "InitialMargin" TEXT, 
    "ProductType" TEXT, 
    "RemainingNotionalAmt" TEXT, 
    "Unique TransactionID" TEXT, 
    "TradeTime" TEXT, 
    "TotalComm" TEXT, 
    "TotalMarket" TEXT, 
    "OrderIns" TEXT, 
    "InstrumentIdentifier" TEXT, 
    "Venue" TEXT, 
    PRIMARY KEY (id)
);
INSERT INTO tradeactivity VALUES(1,'LIFTRLTDSY','20181003-00238','03-Oct-18','05-Oct-18','CYB.AX','AU000000CYB7','CYBG PLC - CDI','LTASXUSDA','','-28,000','Sell-Open','AUD','5.6136','0.7179','4.0299','0.0250%','0.0000%','4.0289','USD','112,807.97','USD 1M LIBOR','-1.0000%','Monthly','Monthly','100.0000%','5.00','PS','-365,897.27','1030240934SP-LTASXAUDACYB.AX','06:58:04','-28.209045','0.000000','Other','SESTXC','XXXX');
INSERT INTO tradeactivity VALUES(2,'LIFTRLTDSY','20181003-00082','03-Oct-18','09-Oct-18','6758.T','JP3435000009','SONY CORP','LTJPUSDA','','8,600','Buy-Open','JPY','6,623.6977','113.7760','58.2170','0.0250%','0.0000%','58.2316','USD','500,791.39','USD 1M LIBOR','0.4000%','Monthly','Monthly','90.0000%','2.50','PS','500,791.39','1030240934SP-LTJPUSDA6758.T','06:10:02','125.166555','0.000000','Other','SESTXC','XXXX');
INSERT INTO tradeactivity VALUES(3,'LIFTRLTDSY','20181003-00083','03-Oct-18','09-Oct-18','7751.T','JP3242800005','CANON INC','LTJPUSDA','','-7,000','Sell-Open','JPY','3,642.4657','113.7760','32.0144','0.0250%','0.0000%','32.0064','USD','224,044.48','USD 1M LIBOR','-0.5000%','Monthly','Monthly','100.0000%','2.50','PS','-224,044.48','1030240934SP-LTJPUSDA7751.T','06:10:02','-56.025128','0.000000','Other','SESTXC','XXXX');
COMMIT;
sqlite> .quit
john@bigbox:~/projects/lifetrading$

lifetrading's People

Contributors

johnmee avatar

Watchers

avatar avatar avatar

lifetrading's Issues

The status of imported files is being changed to updated.

Expected an imported file to remain at the status of 'imported'.
Observed imported files being changed to status of 'updated'.

On runs subsequent to the import, when it finds there is already a filelog record for the file it, erroneously, changes the status to updated.

Change it so that it only changes to status 'updated' if the mtime has changed.

lifetrading/lifetrading.py

Line 101 in 2467c90

data = dict(name=direntry.name, mtime=mtime, status=FileLog.UPDATED)

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.