Amazon Cloud Services and Investment Portfolio Analysis

Recently, there has been high volatility in stock markets, when, for example, a stable paper of a well-known company can lose several percent at once on the news of sanctions against its leadership or, on the contrary, fly to the skies on a positive report and investors' expectations about super-profit dividends.

How to determine whether the ownership of this security brought in income or only loss and disappointment?

(A source)
')
In this article I will tell you how to determine and visualize the adjusted financial result for securities.

Using the example of Client Reporting Opening Broker, we consider parsing and consolidating brokerage reports for the stock market, building a cloud reporting system architecture followed by simple and convenient analysis in AWS Quicksight.

Task Description

Many trainings and training lessons tell us about the need to maintain a trading journal, which records all the parameters of the transaction for further analysis and summarizing the work of the trading strategy. I agree that such an approach to work on the Exchange allows you to discipline a trader, to increase his awareness, but it can also be great to tire out the tedious process.

I admit, at first I tried carefully to follow the advice of keeping a journal, meticulously wrote down every transaction with its parameters in an Excel spreadsheet, built some reports, summary diagrams, planned future deals, but ... I quickly got tired of it all.


Often, brokerage companies are high-tech organizations that provide their clients with fairly high-quality analytics on virtually all issues of interest. It is fair to say that with each update this reporting gets better and better, but even the most advanced of them may not have the customization and consolidation that demanding and inquisitive customers want to see.

For example, Opening Broker allows you to receive brokerage reports in XML format in your account, but if you have an IIS and a normal brokerage account on the Moscow Stock Exchange (MOEX), these will be two different reports, and if you still have an account on St. Petersburg Petersburg Stock Exchange (SPB), then one more will be added to the first two.

So, to get a consolidated investor log, you will need to process three files in XML format.

The above reports on MOEX and SPB differ slightly in their formats, which will need to be taken into account in the implementation of the data mapping.

The architecture of the developed system

The diagram below shows the architecture model of the system being developed:

Parser implementation

We will receive reports on all three accounts in the My Account for the maximum possible period (can be divided into several reports for each year), save them in XML format and put them in one folder. As a test data for the study, we will use a fictional client portfolio, but with the most approximate parameters to market realities.


Suppose that the investor Mr. X we are considering has a small portfolio of five securities:

1. The report on the stock exchange SPB will be two papers: Apple and Microsoft;
2. In the report on the stock exchange MOEX (brokerage) one paper: FGC UES;
3. The report on the stock exchange MOEX (IIS) two papers: MMK and OFZ 24019;

According to our five papers, there may be buy / sell transactions, dividend and coupon payments, prices may change, etc. We want to see the current situation, namely: the financial result, taking into account all payments, transactions and current market value.

And here comes Python, we read information from all reports into one array:

my_files_list = [join('Data/', f) for f in listdir('Data/') if isfile(join('Data/', f))] my_xml_data = [] #     for f in my_files_list: tree = ET.parse(f) root = tree.getroot() my_xml_data.append(root) 

For analytics, we need several entities from the reports, namely:

• Positions of securities in the portfolio;
• Concluded transactions;
• Non-trading operations and other movements on the account;
• Average prices of open positions

In order to prepare the sample, we will use four dictionaries to describe the above sets.

 dict_stocks = {'stock_name': [], 'account': [], 'currency': [], 'current_cost': [], 'current_cost_rub': [], 'saldo' : []} dict_deals = {'stock_name': [], 'account': [], 'date_oper': [], 'type_oper': [], 'quantity': [], 'price': [], 'currency': [], 'brokerage': [], 'result': []} dict_flows = {'stock_name': [], 'account': [], 'date_oper': [], 'type_oper': [], 'result': [], 'currency': []} dict_avg_price = {'stock_name': [], 'account': [], 'avg_open_price' : []} 

A few words about what these dictionaries are.





We process an array of XML documents and fill these dictionaries with the appropriate data:

 #       for XMLdata in my_xml_data: #      exchange_name = 'SPB' if XMLdata.get('board_list') == ' ' else 'MOEX' client_code = XMLdata.get('client_code') account_name = get_account_name(exchange_name, client_code) #   current_position, deals, flows, stock_name, \ saldo, ticketdate, price, brokerage, \ operationdate, currency, \ current_cost, current_cost_rub, \ stock_name_deal, payment_currency, currency_flows = get_allias(exchange_name) #      get_briefcase(XMLdata) df_stocks = pd.DataFrame(dict_stocks) df_stocks.set_index("stock_name", drop = False, inplace = True) #    get_deals(XMLdata) df_deals = pd.DataFrame(dict_deals) df_avg = pd.DataFrame(dict_avg_price) #       get_nontrade_operation(XMLdata) df_flows = pd.DataFrame(dict_flows) 

All processing is looped through all the XML data from the reports. Information about the trading platform, client code is the same in all reports, so you can safely extract it from the same tags without using mapping.

But then you have to apply a special construction that will provide the necessary alias for the tag based on the report (SPB or MOEX), since the same data in these reports are called differently.



The get_allias function returns the name of the required tag for processing, taking as input the name of the trading platform:


The function get_briefcase is responsible for processing information about the state of the client portfolio:


Further, using the get_deals function, information about transactions is extracted:


In addition to processing an array with information about the parameters of the transaction, it also calculates the average price of an open position and the PNL realized by the FIFO method. Responsible for this calculation is the PnlSnapshot class, for the creation of which, with minor modifications, the code presented here is taken as the basis: P & L calculation

And finally, the most difficult to implement - the function of obtaining information about non-trading operations - get_nontrade_operation . Its complexity lies in the fact that in the report block used for non-trading operations, there is no clear information about the type of operation and the security to which this operation is linked.


Accordingly, it will be difficult to do without regular expressions, therefore we will use them to the full. The other side of the question is that the name of the company does not always coincide with the name in the portfolio or in the transactions. Therefore, the received name of the issuer from the purpose of payment should be additionally correlated with the dictionary. As a dictionary, we will use an array of transactions, since There is the most comprehensive list of companies.

The function get_company_from_str extracts the name of the issuer from the comment:


The function get_company_from_briefcase brings the name of the company to the dictionary, if it finds a match among the companies that participated in the transactions:


And finally, the final data collection function for non-trading operations is get_nontrade_operation :


The result of collecting data from reports will be three DataFrame, which are approximately the following:

1. DataFrame with information on average prices of open positions:
   
   
2. Transaction Information DataFrame:
   
   
3. DataFrame with information about non-trading operations:
   
   
   
   

So, all we have to do is perform the external merging of the table of transactions with the table of information about the portfolio:

 df_result = pd.merge(df_deals, df_stocks_avg, how='outer', on=['stock_name', 'account', 'currency']).fillna(0) df_result.sample(10) 

And, finally, the final part of the data array processing is the merging of the data array obtained at the previous step with the DataFrame for non-trade transactions.
The result of the work done is one large flat table with all the necessary information for the analysis:

 df_result_full = df_result.append(df_flows, ignore_index=True).fillna(0) df_result_full.sample(10).head() 

The resulting data set (Final Report) from the DataFrame is easily downloaded to CSV and can then be used for detailed analysis in any BI system.

 if not exists('OUTPUT'): makedirs('OUTPUT') report_name = 'OUTPUT\my_trader_diary.csv' df_result_full.to_csv(report_name, index = False, encoding='utf-8-sig') 

Loading and processing data in AWS

Progress does not stand still, and now cloud services and serverless computing models are gaining great popularity in processing and storing data. This is largely due to the simplicity and cheapness of this approach, when building an architecture for complex computing or processing big data does not require buying expensive equipment, and you only rent power for the cloud you need and deploy the necessary resources quickly enough for a relatively small fee. .

One of the largest and most well-known cloud technology providers on the market is Amazon. Consider the example of the Amazon Web Services (AWS) environment, building an analytical system for processing data on our investment portfolio.

AWS has an extensive selection of tools, but we will use the following:

• Amazon S3 - object storage, which allows you to store almost unlimited amounts of information;
• AWS Glue is the most powerful ETL cloud service that can itself determine the structure and generate the ETL code from the given source data;
• Amazon Athena is a serverless online SQL query service that allows you to quickly analyze data from S3 without much preparation. He also has access to the metadata that AWS Glue prepares, which makes it possible to access the data immediately after passing the ETL;
• Amazon QuickSight is a serverless BI service, you can build any visualization, analytical reports on the fly, etc.

With Amazon's documentation, everything is in order, in particular, there is a good article Best Practices When Using Athens with AWS Glue , which describes how to create and use tables and data using AWS Glue. Let us and we will use the main ideas of this article and apply them to create our own architecture of the analytical reporting system.

CSV files prepared by our report parser will be added to the S3 bucket. It is planned that the corresponding folder on S3 will be replenished every Saturday - at the end of the trading week, so we can not do without data partitioning by the date of the formation and processing of the report.
In addition to optimizing the work of SQL queries to such data, this approach will allow us to conduct additional analysis, for example, to obtain the dynamics of changes in the financial result for each paper, etc.



Consider the generated table in more detail.
If you click on the name of the created table, we will go to the page with the description of the metadata. At the bottom there is a table diagram and the most recent is a column that was not in the original CSV file - date_report. Glue creates this AWS column automatically based on the definition of the source data sections (in the S3 batch, we specifically named folders - date_report = YYYY-MM-DD, which made it possible to use them as partitions divided by date).

Data analysis

Having at our disposal downloaded processed data, we can easily proceed to their analysis. First, let's look at the features of Amazon Athena as the easiest and fastest way to perform analytical queries. To do this, go to the Amazon Athena service, select the database we need (financial) and write the following SQL code:

 select d.date_report, d.account, d.stock_name, d.currency, sum(d.quantity) as quantity, round(sum(d.result), 2) as result from my_trader_diary d group by d.date_report, d.account, d.stock_name, d.currency order by d.account, d.stock_name, d.date_report; 

This query will give us the net financial result for each paper for all reporting dates. Since we have downloaded the same report three times for different dates, and the result will not change, which, of course, in the real market conditions will be different:


But what if we want to visualize the data in the form of flexible tables or charts? This is where Amazon QuickSight comes to the rescue, with which you can set up flexible analytics almost as quickly as writing a SQL query. Let's go to Amazon QuickSight (if you haven't registered there yet, then registration is required).

Click on the button New analyses -> New dataset and in the appeared source selection window for dataset, click on Athena:



We will invent a name for our data source, for example, “PNL_analysis” and click on the “Create data source” button.

Next, the Choose your table window will open, where you need to select a database and a data source table. Choose a database - financial, and a table in it: my_traider_diary. By default, the entire table is used, but when “Use custom SQL” is selected, you can customize and fine-tune the data selection you need. For example, let's use the entire table and click on the Edit / Preview Data button.

A new page will open where you can make additional settings and process the available data.

Now it is necessary to add additional calculated fields to our dataset: quarter and year of the operation. An attentive reader may notice that such manipulations were easier to do on the side of the parser before saving the Final Report to CSV. Undoubtedly, my goal now is to demonstrate the capabilities and flexibility of setting up a BI system on the fly. Continue the creation of calculated fields by clicking on the "New field" button.


To select the year of the operation and the quarter, simple formulas are used:



When the calculated fields are successfully created and added to the sample, we give the name to our dataset, for example, “my_pnl_analyze” and click on the “Save and visualize” button.

After that, we transfer to the main Amazon QuickSight board and the first thing we need to do is set up a filter for the report date (taking into account that the same data was collected from three sections). Select the key date 2018-10-01 and click on the Apply button and go to the Visualize tab.


Now we can visualize the result for a portfolio in any plane, for example, for each security within a trading account, and divided in turn by currency (since the result is not comparable in different currencies) and types of transactions. Let's start with the most powerful tool of any BI - pivot tables. To save space and display flexibility, I rendered currencies into a separate control (analogue of the slice in MS Excel)

The table above shows that if an investor decides to sell all shares of FGC UES now, he will thereby fix the loss, since dividends paid in the amount of 1 509.91 p. do not cover its costs (1 763.36 p. - negative exchange difference and 174 p. - NDFL for dividends). It makes sense to wait and wait for the best times on the Exchange.

The following graph is a bar graph:


And now we will create a table that will show us how much we have invested in each paper, how many days it is in our portfolio and what is the yield for the entire period of ownership. To do this, add two new calculated fields: sum_investment and count_days.



The final table is shown in the screenshot below:

Conclusions and summary

We have reviewed with you the implementation of the report parser and how to analyze the data prepared by it “on the fly” using Amazon services. They also touched upon some business and fundamental aspects of the analysis of the investment portfolio, since this topic is almost immense and it is quite difficult to fit it in one article, I think it makes sense to put it in a separate publication or even a cycle of publications.

As regards the use of the broker's report processing tool and the approaches and algorithms involved in it, they can be used (with a corresponding modification) for processing reports of other Brokers. In any case, if you are thinking of adapting the code to your needs, I am ready to give some tips, so do not hesitate to ask questions - I will definitely try to answer them.

I am sure that this system will find its application and will have further development. For example, it is planned to add to the calculation of the full PNL portfolio for accounting for depository and other commissions (for example, withdrawal of funds), as well as redemption of bonds, etc ... The calculated fields on the Quicksight side were used for the demonstration purpose, all these additional columns will be transferred to Python and will be calculated on the side of the parser.

As an architect and main business customer of this solution, I see further modernization as follows: well, I don’t want to manually manually request these XML reports every time! Of course, there is no other possibility so far, but the Broker's API with the transfer of a token and a sampling range would be ideal for weekly receiving raw reports. The subsequent complete automatic processing on the Amazon side: from triggering an ETL-job on AWS Glue to getting finished results in the form of graphs and tables in Amazon QuickSight will allow you to fully automate the process.

The full source code can be found in my GitHub repository.