Chapter 12 Investments, basic knowledge of Excel, and XBRL. Goal of the project: Identify which banks...

Transcribed Image Text:

"Chapter 12 Investments", basic knowledge of Excel, and XBRL. Goal of the project: Identify which banks had the most decrease in available-for-sale securities and most increase in held-to-maturity securities using XBRL. Then, explain why these banks experienced an increase in such classifications of investment securities by searching financial statements, news articles, or other sources. Instructions: 1. The raw data comes from: https://www.sec.gov/dera/data/financial-statement-data- sets.html However, the dataset is too large to open in Excel. I have selected about 40 banks and constructed a sample of all their "SUB" and "NUM" datasets for 2015-2021. You can download from here: https://www.dropbox.com/s/8m0jvfavp9rcro2/xbrl_dataset.xlsx?dl=0 2. The fields included in the "SUB" and "NUM" datasets are described here: https://www.sec.gov/files/aqfs.pdf The "adsh" field is a unique identifier for each SEC filing. The "cik" field is a unique identifier for each SEC filer. Merge the "NUM" dataset with the "SUB" dataset using the "adsh" to obtain the CIK (central index key) and name of each bank. Use VLOOKUP (https://exceljet.net/functions/vlookup-function) The CIK can be used to search companies on SEC EDGAR. Select 10 banks. 3. The "ddate" field represents the date of the balance. Because it is not in a date format, use the (https://exceljet.net/excel-functions/excel-left-function LEFT ), ), MID (https://exceljet.net/excel-functions/excel-mid-function and RIGHT (https://exceljet.net/excel-functions/excel-right-function) functions to transform it into date format. "Chapter 12 Investments", basic knowledge of Excel, and XBRL. Goal of the project: Identify which banks had the most decrease in available-for-sale securities and most increase in held-to-maturity securities using XBRL. Then, explain why these banks experienced an increase in such classifications of investment securities by searching financial statements, news articles, or other sources. Instructions: 1. The raw data comes from: https://www.sec.gov/dera/data/financial-statement-data- sets.html However, the dataset is too large to open in Excel. I have selected about 40 banks and constructed a sample of all their "SUB" and "NUM" datasets for 2015-2021. You can download from here: https://www.dropbox.com/s/8m0jvfavp9rcro2/xbrl_dataset.xlsx?dl=0 2. The fields included in the "SUB" and "NUM" datasets are described here: https://www.sec.gov/files/aqfs.pdf The "adsh" field is a unique identifier for each SEC filing. The "cik" field is a unique identifier for each SEC filer. Merge the "NUM" dataset with the "SUB" dataset using the "adsh" to obtain the CIK (central index key) and name of each bank. Use VLOOKUP (https://exceljet.net/functions/vlookup-function) The CIK can be used to search companies on SEC EDGAR. Select 10 banks. 3. The "ddate" field represents the date of the balance. Because it is not in a date format, use the (https://exceljet.net/excel-functions/excel-left-function LEFT ), ), MID (https://exceljet.net/excel-functions/excel-mid-function and RIGHT (https://exceljet.net/excel-functions/excel-right-function) functions to transform it into date format.

Answer rating: 100% (QA)

To accomplish your project goal follow these steps Access XBRL Data Obtain XBRL data for the banks youre interested in analyzing XBRL eXtensible Busin... View the full answer