Please use the required programming languages to finish the following test and submit your code and results.
Part 1. SQL
Table1: Financial_statement company_id period_end
3679 12/31/2012 3679 12/31/2012 3679 12/31/2012 3679 12/31/2012 3679 12/31/2012 2418 12/31/2012 2418 12/31/2012 2418 12/31/2012 2418 12/31/2012
…. company_id: id for companies in database, int
period_end: financial statement period end, date field_id: financial statement data field id, int field_value: financial statement data value, float
field_value
3 106 5 1244 1 0 4 966 2 35 3 77 4 1002 5 2099 1 0.03
Table2: data_filed_information id name
Equity Total Asset Total Liability
id: financial statement data field id, int
name: financial statement data field, nvarchar
a) Table1 provides data in sparse table format. Please pivot the data so that each row contains total asset, total liabilities, and equity for each company and period end (assume all data fields available). It is highly encouraged to finish this part using 2 different methods and talk about which one is better in your opinion
Desired output
company_id
period_end
6/30/2021 12/31/2021
total asset
total liability equity
4021 811 6482 2161
b) The data source is not very reliable that we need to validate data before we use. Based on the output table in a), illustrate your idea on what to validate and how to validate. (hint: based on the rules of Balance Sheet)
Table 3: Company information
company_id
company_name
abc Corp. def Inc.
prime_exchange_id
domicile_country_id
Energy Technology
… company_id: id for companies in database, int
company_name: company name, nvarchar
prime_exchange_id: exchange (prime exchange where the company lists) id, int domicile_country_id: country (where the company is) id, int
industry: industry the company belongs, nvarchar
Table 4: Exchange information
Shanghai Stock Exchange Shenzhen Stock Exchange Singapore Stock Exchange
country_id
id: exchange id, int
name: exchange name, nvarchar
country_id: country (where the exchange is) id, int
Table 5: Country_information
… ISO_Alpha2_code&ISO_Alpha3_code: country codes, nvarchar
Australia China Hong Kong India
ISO_Alpha2_code ISO_Alpha3_code
AU AUS CN CHN HK HKG IN IND
id: country id, int
name: country name, nvarchar
c) Please select all companies (company id + name) whose domicile country is not US but listed on US exchanges
d) Please find all technology companies around the world and retrieve NET INCOME in their latest financial statement
desired output
company_id company_name
period_end net income 9/30/2022
e) Please find the company with largest CURRENT ASSET on 2021/12/31 in each exchange and each industry (given currency unified within each exchange, and all data available)
desired output
exchange_name industry company_id company_name The New York Stock Exchange Energy 3212 fds Corp.
Part 2. Matlab & Julia Please finish this part in both MATLAB and Julia
a. financialStatement.mat: This file contains the financial statement data and its structure of some companies
b. FX: This file contains the historical FX rate data of USD/CNY, the first column is date and the second column is value
Questions:
1. Data cleaning
(1) To improve the quality of the financial statement data, please remove those rows with missing or non-positive BS_TOT_ASSET and BS_TOT_LIAB2
(2) To satisfy the rules of Balance Sheet data, please remove the rows of which BS_CUR_LIAB > BS_TOT_LIAB2
Programming Help
2. Adjust Missing Current Liabilities
For some firms, especially financial firms, the current liabilities data is unavailable, please replace the missing values using the following rule:
If there is no BS_CUR_LIAB, and BS_ST_BORROW, BS_SEC_SOLD_REPO_AGRMNT, BS_OTHER_ST_LIAB, BS_CUST_ACCPT_LIAB_CUSTDY_SEC are not all NaN, then use the sum of these 4 fields as BS_CUR_LIAB.
If there is no BS_CUR_LIAB, and BS_ST_BORROW, BS_SEC_SOLD_REPO_AGRMNT, BS_OTHER_ST_LIAB, BS_CUST_ACCPT_LIAB_CUSTDY_SEC are all NaN, then BS_CUR_LIAB is NaN.
3. Currency Conversion
To unify the companies¡¯ data globally, we need to transfer the currency to USD.
All of the FS data in the mat file is in CNY, please use the FX rate given in the FX.mat to convert the currency to USD according to period end time.
程序代写 CS代考 加QQ: 749389476
E.g. For FS data with 20111231 PE, you need to find the FX rate on 2011-12-31, if there is no data on this day, please use the closest historical day and value, which is 6.3049 on 2011-12-30.
Code Help, Add WeChat: cstutorcs