Update Market Data Process for Financial Prices
Typescript | NodeJS | Timescale | PostgreSQL
Project Overview
We need a Typescript NodeJS process to update our market data price Timescale PostgreSQL database. The requirement consists of programming a process that sources market data from various APIs platforms, and the process should handle real-time and batch updates efficiently.
Detailed Requirements
1. Data Source
- The following APIs platorms must be considered:
- Alpha Vantage: [Alpha Vantage](https://www.alphavantage.co)
- Finnhub: [Finnhub](https://finnhub.io)
- Polygon.io: [Polygon.io](https://polygon.io)
- Tiingo: [Tiingo](https://www.tiingo.com)
- CryptoCompare: [CryptoCompare](https://min-api.cryptocompare.com)
- Open Exchange Rates: [Open Exchange Rates](https://openexchangerates.org)
- CCXT (CryptoCurrency eXchange Trading Library): [CCXT](https://github.com/ccxt/ccxt)
- The developer must review each provider’s API documentation for specifics on rate limits, data formats, and usage guidelines.
- The developer must choose those platforms who provide the market data we need: the top 50 most liquid crypto currencies, the top 1000 most liquid US stocks, and the top 10 most liquid Forex pairs
- The data must be extracted on at least a per-minute daily resolution, but we would like to be real tick data. Some analysis must be performed to understand what can be obtained.
2. Data Format
- The data will be obtained through the APIs the platforms offer.
- The database must be populated on SQL TimescaleDB.
- The developer must program a TypeORM interface for this.
3. Update Frequency
- Maximum daily updates, but due to the size and capacities of each platform.
- It may need to be split on an hourly basis or even less.
- For CCXT, consider a small but efficient refresh
4. Database Schema
- The schema for storing tick data from stock markets, crypto, and forex is as follows:
-- instruments table
CREATE TABLE instruments (
instrument_id SERIAL PRIMARY KEY,
symbol VARCHAR(20) NOT NULL,
name VARCHAR(100) NOT NULL,
type VARCHAR(20) NOT NULL, -- 'stock', 'crypto', 'forex'
exchange VARCHAR(50),
currency VARCHAR(10),
created_at TIMESTAMPTZ DEFAULT current_timestamp,
UNIQUE (symbol, exchange)
);
-- tickdata hypertable
CREATE TABLE tickdata (
time TIMESTAMPTZ NOT NULL,
instrument_id INTEGER NOT NULL,
price NUMERIC(19,4) NOT NULL,
volume NUMERIC(19,4) NOT NULL,
FOREIGN KEY (instrument_id) REFERENCES instruments(instrument_id)
);
-- Convert tickdata to a hypertable
SELECT create_hypertable('tickdata', 'time', 'instrument_id', 2);
-- Create an index on the instrument_id for faster queries
CREATE INDEX ON tickdata (instrument_id, time DESC);
- The instrument catalog must be populated with the top 50 most liquid crypto currencies, the top 1000 most liquid US stocks, and the top 10 most liquid Forex pairs. This will serve as a starting point, and the data retrieval must follow this static catalog. The catalog may be updated on an EOD basis.
5. Error Handling
- The process should handle errors by retrying and alerting via NestJS logging facilities.
6. Authentication
- Authentication requirements for accessing the data source or the database must be parametrized. During development, a testing environment in the cloud with user/password will be provided.
7. Environment
- The NodeJS process will run on AWS cloud in production, likely on a serverless/lambda setup due to its batch nature. The production database will also be on AWS.
- During development, it will run on a virtual server in the cloud on a conventional Linux OS.
8. Dependencies
- The freelancer should use the following technologies:
- Typescript
- NodeJS
- NestJS
- CCXT
- TypeORM
- Other NodeJS libraries, with a preference for long-lived libraries.
9. Testing
- Process should be tested using a subset of the catalog with some symbols per type (forex, crypto, stock).
- The test will involve leaving the automatic data retrieval running for a couple of days (via NestJS scheduling or cron) and comparing the data manually.
10. Documentation
- The freelancer should provide the following documentation:
- Vision of understandings of the requirement
- General architecture
- Workplan
- Testing scenarios
If there are any additional details or specifics you'd like to know, please let me know!