Small footprint SQLite / PHP financial risk time series and validation database application

What is is

• an SQLite database with PHP REST frontend for financial time series and other data
• a simple HTTP / JavaScript web frontend for configuration, data management and analysis
• a number of fully automatised financial validation scripts

Features

• auditable storage of time series data
• seamless handling of typical requirements around financial time series (non-business days, data gaps, multiple support points/maturities)
• automatic import of time series data provided by third-party applications

Application structure

index.html : Configuration, data management and analysis frontend

index.php : PHP REST interface (main entry point)

app/* : PHP REST interface program files

data/data.sqlite : SQLite database file

test/* : Files for testing

How to test

Just run ./serve.sh and access API via browser under 127.0.0.1 for testing.

How to deploy

Includes Apache .htaccess file that redirects all API calls to the main index.php file.

REST API

Endpoints

Endpoint	HTTP Method	Parameters	Description
/	GET	none	returns app version number and creates database file under /data/data.sqlite if it does not exist.
/def	GET	FORMAT (json|csv) where json is the default	Query all stored time series definitions as JSON or CSV filet
/def/NAME	POST	FILE as multipart/form-data encoded attribute	Create or update time series definition with file
/def/NAME	DELETE	none	Delete time series definition and data for name NAME
/dates	GET	FROM (YYYY-MM-DD), TO (YYYY-MM-DD), ASOF (YYYY-MM-DDTHH:MM:SS while THH:MM:SS is optional)	Query all dates where data is available
/data	GET	FROM (YYYY-MM-DD), TO (YYYY-MM-DD), ASOF (YYYY-MM-DDTHH:MM:SS while THH:MM:SS is optional)	Query all time series data of all time series between FROM and TO, exclude data updated after ASOF
/data	POST	FILE as multipart/form-data encoded attribute	Post new time series data in csv format file
/data/NAME	GET	FROM (YYYY-MM-DD), TO (YYYY-MM-DD), ASOF (YYYY-MM-DDTHH:MM:SS.SSS while THH:MM:SS.SSS is optional)	Query all time series data of time series NAME between FROM and TO, exclude data updated after ASOF
/data/NAME	POST	FILE as multipart/form-data encoded attribute	Post new time series data in csv format

Format specification

Time series data is uploaded via POST requests and downloaded via GET requests in a tabular csv format as specified below.

• Record separator: ;
• Line separator: \r\n (i.e., Windows line ending)
• Date format: YYYY-MM-DD
• Decimal separator: . (dot)

Table structure:

NAME	TAG1	TAG2	...
DATE1	VALUE DATE1 TAG1	VALUE DATE1 TAG2	...
DATE2	VALUE DATE2 TAG1	VALUE DATE2 TAG2	...
...	...	...	...

Example:

    YIELD_CURVE;1D;1W;1M;3M
    2019-09-03;0.01;0.015;0.016;0.017
    2019-09-04;0.011;0.015;0.017;0.018
    2019-09-05;0.012;0.016;0.016;0.018

When uploading new data on the endpoint /data/NAME, the NAME info in the first csv field is overrided and, consequently, may be left empty.