satoshi_sd
New Member
- Joined
- Nov 5, 2021
- Messages
- 25
- Office Version
- 365
- Platform
- Windows
Is there any formula to read source file and generate a simplified data?
These are the raw data I downloaded from the system, the number of rows are not consistent and as you can see below when there are data, the system will automatically increase a row to show the data (It gets very long when dealing with more options and currencies). It also comes with 4 main sections (bold and underline) but I can't figure out the right formula to read the main section to generate the data accordingly:
Legend (To shorten long name)
Desired Result:
These are the raw data I downloaded from the system, the number of rows are not consistent and as you can see below when there are data, the system will automatically increase a row to show the data (It gets very long when dealing with more options and currencies). It also comes with 4 main sections (bold and underline) but I can't figure out the right formula to read the main section to generate the data accordingly:
| Items | Purpose Code | Institutional Sector | Country | Currency Code |
| Direct Investment (Exposure with Related Counterparty) | ||||
| a. Equity Capital | ||||
| - | - | - | - | |
Subtotal | ||||
| b. Retained Earnings | ||||
| - | - | - | - | |
Subtotal | ||||
| c. Bonds and Notes | ||||
| - | - | - | - | |
Subtotal | ||||
| d. Money Market Instruments | ||||
| - | - | - | - | |
Subtotal | ||||
| e. Non-Participating RPS | ||||
| - | - | - | - | |
Subtotal | ||||
| f. Real Estates | ||||
| - | - | - | - | |
Subtotal | ||||
| g. Swaps | ||||
| - | - | - | - | |
Subtotal | ||||
| h. Forwards | ||||
| - | - | - | - | |
Subtotal | ||||
| i. Futures | ||||
| - | - | - | - | |
Subtotal | ||||
| j. Options | ||||
| - | - | - | - | |
Subtotal | ||||
| k. Other Financial Derivatives | ||||
| - | - | - | - | |
Subtotal | ||||
| l. Loans | ||||
| - | - | - | - | |
Subtotal | ||||
| m. Suppliers Credit | ||||
| - | - | - | - | |
Subtotal | ||||
| n. Deposits | ||||
| - | - | - | - | |
Subtotal | ||||
| o. Insurance, Pension and Standardised Guarantee Schemes | ||||
| - | - | - | - | |
Subtotal | ||||
| p. Others | ||||
| 39900 | FA | JP | USD | |
| 39900 | NF | AU | USD | |
| 39900 | NF | HK | USD | |
| 39900 | NF | KY | USD | |
| 39900 | NF | SG | USD | |
Subtotal | ||||
| Portfolio Investment | ||||
| a. Equity Securities | ||||
| - | - | - | - | |
Subtotal | ||||
| b. Bonds and Notes | ||||
| - | - | - | - | |
Subtotal | ||||
| c. Money Market Instruments | ||||
| - | - | - | - | |
Subtotal | ||||
| d. Non-Participating RPS | ||||
| - | - | - | - | |
Subtotal | ||||
| Financial Derivatives | ||||
| a. Swaps | ||||
| - | - | - | - | |
Subtotal | ||||
| b. Forwards | ||||
| - | - | - | - | |
Subtotal | ||||
| c. Futures | ||||
| - | - | - | - | |
Subtotal | ||||
| d. Options | ||||
| - | - | - | - | |
Subtotal | ||||
| e. Other Financial Derivatives | ||||
| - | - | - | - | |
Subtotal | ||||
| f. Employee Stock Options | ||||
| - | - | - | - | |
Subtotal | ||||
| Other Investment (Exposure with Non-related Counterparty) | ||||
| a. Currency | ||||
| - | - | - | - | |
Subtotal | ||||
| b. Loans | ||||
| - | - | - | - | |
Subtotal | ||||
| c. Suppliers Credit | ||||
| 31220 | IC | AE | AED | |
| 31220 | IC | AE | EUR | |
| 31220 | IC | AE | SAR | |
Subtotal | ||||
| d. Deposits | ||||
| - | - | - | - | |
Subtotal | ||||
| e. Insurance, Pension and Standardised Guarantee Schemes | ||||
| - | - | - | - | |
Subtotal | ||||
| f. Others | ||||
| - | - | - | - | |
Subtotal | ||||
TOTAL |
Legend (To shorten long name)
| Main | Short form |
Direct Investment (Exposure with Related Counterparty) | DI |
| Portfolio Investment | PI |
| Financial Derivatives | FD |
| Other Investment (Exposure with Non-related Counterparty) | OT |
Desired Result:
| Short form | Purpose Code | Institutional Sector | Country | Currency Code |
| DI | 39900 | FA | JP | USD |
| DI | 39900 | NF | AU | USD |
| DI | 39900 | NF | HK | USD |
| DI | 39900 | NF | KY | USD |
| DI | 39900 | NF | SG | USD |
| OT | 31220 | IC | AE | AED |
| OT | 31220 | IC | AE | EUR |
| OT | 31220 | IC | AE | SAR |
