Challenge Overview
Challenge Objectives
-
Design API specification (Swagger)
-
Create database ER diagram
Project Background
-
We are building a Smart Investment Advisor platform that empowers users of financial/brokerage sites to back-test, find new ideas for, optimize and trade their portfolios, based on proven theories pioneered by Nobel Prize-winning economists. Traditional financial/brokerage sites only allow users to see price charts and other data of individual stocks, but do not provide a holistic view of the portfolio.
Typical "robo-advisors" in the market offer only generic financial budgeting and one-size-fits-all products, but do not offer any portfolio analytics tailored to the user’s own stock ideas.
On the other hand, professional asset managers enjoy the advantage of analytical tools that help them understand the interactions of stocks in a portfolio context, thereby creating an optimal portfolio structure that achieves the best risk-adjusted return. However, such capabilities have not been available to retail investors.
We aim to bring this power to retail investors, whether they are users of financial news portals or account holders of online brokerages. We will make these tools intuitive, simple and enjoyable to use.
-
In this challenge we’re designing application backend API and database models.
-
In the follow-up challenges we will develop the backend API, build the frontend, data ingestion, integrate with the frontend and third party APIs and build a test suite.
Technology Stack
-
Swagger Open API Specification
-
ERD
-
MongoDB
Resources
Application designs are available at https://marvelapp.com/4a82hf9/screen/58068265 . See the below description for individual screen features. In the forums you’ll find a document explaining the input data and necessary calculations for each of the screens.
Short app overview
NOTE: This section just gives a high level overview of the app features. For the actual requirements, see the next section.
Application will show the users their portfolio (list of securities), compare it to the current market benchmark, provide market risk info, generate suggestions for the user (discover & trade securities) and optimize the user portfolio based on user preferences. Market data comes from a 3rd party vendor (available securities, prices, indexes, etc). App doesn’t manage users, but integrates with partner websites (ex user is redirected to the app from the partner website/app) and the current user portfolio is not managed by the app - it is queried from the partner API(s). Once the user confirms suggested trades list, it is forwarded to the partner using their API.
Admin users can configure various settings that affect portfolio optimization and user inputs.
All user actions will be audited (both on API level and the app actions - clicks) and this data will be available in various reports.
App will create various notifications for the users - ex securities price changes, executed trades, etc
Individual requirements
We’re aiming to create three things in this challenge:
-
Data model (ER diagram)
-
REST API specification document (swagger)
-
Screen API calls mapping - a document detailing which API endpoints should be called on each screen
-
Input data
There are 4 types of data that will be imported to the system. Each of those is released daily in the form of a csv file and our data import endpoints will just overwrite the previously loaded data. Note that the data in the forums is provided as an xlsx file - you can assume the data to be imported will be 4 csv files:-
Price data - monthly closing price of each security in the market universe
-
Factor data - extended market info for each of the securities
-
Market data - market metric data that’s used for calculating market risk
-
Benchmark data - specifying securities in the benchmark index - the one used to compare user portfolio data to
-
App data will be imported by admin users (from a separate admin portal, yet to be developed) - design the api endpoints and data model to persist the imported data - normalize the attributes and data relations where needed, don’t just create a 1-1 mapping of import data to db tables. Create lookup tables or indexes where needed
-
User authentication
App users info will come from the partner website redirect. For now we can assume the redirect will contain a “token” field that our app can use to call the partner website and get the user info (user id, first/last name). This info should be stored to the app database and a JWT token would be returned to the user to use for all other API calls.
Upon authentication, the backend will query the user portfolio (list of securities ticker / market code and number of shares) from the partner website and store it in the database (overwriting any previous user portfolio data). See the sample portfolio data in the TestPortfolio sheet in the provided dataset.
Note that the app will support multiple partner websites so the partner identifier should be stored in the user info as well - partner list can be a fixed enumerated list for now.
Admin users will be authenticated with email/password combination and the admin users list will be managed by the app (we need CRUD API for admin users) and the login flow for the admin users should be completely separate from the regular app users (admin users can’t use the app and vice versa) -
Factor configuration
Factors list is configured by admin users in a separate admin portal page. Each factor belongs to one category (see the Factor type column in the Factor list sheet) and admin user will have the option to add new categories (we only have Momentum and Fundamental for now) -
App settings
App settings will be configured by admin user and will be the same for all app users. These settings include:-
Minimum number of securities in the portfolio
-
Maximum and minimum weight per security
-
Maximum number of securities for “Generate ideas” module
-
-
Factor calculation
Factor calculation is performed on all securities upon data import (and stored to the database) - see the FactorCalc sheet in the provided dataset. It is up to you to decide if this data will be stored along the factor & price data or in a separate DB table. -
Market risk calculation
Market risk calculation is performed on market data upon import (and stored to the database) - see the MarketCalc sheet in the provided dataset. It is up to you to decide if this data will be stored along the market data or in a separate DB table. -
Investment universe
This is normalized securities data compiled from the imported price, factor and benchmark data - it should be recalculated and persisted after each data import -
Security percentages
After each data import the app will recalculate and persist the security return price percentages - see the SecPxChg sheet -
Benchmark percentages
After each data import the app will recalculate and persist the percentage price return for each benchmark index -
Monthly & annual portfolio metrics
This data is recalculated and persisted to database on each user login based on the latest user portfolio and securities data -
Market Risk, Generate ideas, View exposure
This data is calculated on the fly and is not persisted to database. Generate ideas module has two views - showing all securities, or showing just the securities in any of the four quadrants (value=50 for each axis is the center point), and the number of “ideas” returned by the api should be limited (setting in the admin portal) -
Adjust portfolio
This feature allows the user to update the desired securities list and their weights in the portfolio. It should be saved to the user portfolio data in the database (it is used for portfolio optimization and is not sent to the partner site).
The app will track 3 types of portfolios for each user: Current (loaded from the partner API), Revised (modifications done to the Current portfolio by the user) and Optimized (optimized version of the last Revised portfolio).
Current portfolio won’t change during the user session, it’s only updated when logging out and back in.
Revised portfolio keeps all the modifications to the previous portfolio - we need to keep history of the Revised portfolio as the app has Undo/Redo options to show the previous portfolio versions.
Optimized portfolio is the optimized version of a Revised portfolio (or Current portfolio if the user didn’t make any changes). We can consider optimized portfolio as yet another version of a Revised portfolio.
It is up to you to decide how to store the portfolio versions in the db (separate collections, or one collection with ‘type’ flags), but the app will need to get complete portfolio history and add new versions of Revised/Optimized/Current portfolio. -
Portfolio optimization
Portfolio is optimized based on user inputs - desired volatility level and expected return. See the “Result” sheet in the provided dataset for the outputs of the portfolio optimization that needs to be stored. Optimization is an asynchronous process so we need to provide API endpoints to query the optimization status. -
Trade
List of securities to trade is provided by the optimization module and can be modified by the user. Confirming the trade will forward the trade list to the partner website and store the information to the app database. Note that we do need to store trade status - trades aren’t instantaneous so the status should support multiple states ex trade pending, sent to partner, error sending to partner, trade failed, trade completed and trade status changes should be audited. -
Reporting
Reporting endpoints will provide 3 reports with filtering options-
Securities history (trades list) - filtering by dates, ticker name, amount range, buy/sell, country, market, asset class
-
Visited screens - only dates filtering (data should be persisted to app database)
-
Modules click - only dates filtering (data should be persisted to app database)
-
-
Auditing
All the user actions (api calls) should be audited into a database table -
Notifications
User notifications will be generated asynchronously (ex during data import, executing trades list, etc) - create the notifications listing endpoint.
Admin users will have the option to configure rules for creating notifications, ex “percentile ranking of a stock in user portfolio has changed over a threshold” or “price of a stock in user portfolio has changed over a threshold” and those thresholds should be configured in the portal and saved to the database. -
Lookup data
Create lookup endpoints for common app data used in the screens (ex securities, countries, markets, asset classes, etc)
Make sure to design the data model with sufficient details to track all the relations required for the calculations (ex ticker name can be used as identifier for a security, user_id would identify the user, etc).
General for all requirements
Use camelCase for all JSON and data model attribute names, TitleCase for entity/table names. Pick names that are short yet descriptive and unambiguous.
What To Submit
Submit a Swagger API specification
Submit the ER diagram
Submit api calls to screen mapping diagram