The semi-structured holdings Excel files stretch from the present quarter back to 2000, although we have only transformed the data back to 2006Q3 so far.

Data Flows

From 2006Q3 onwards, there are 4.7 million rows in our structured long data table (series multiplied by time). Thus, it is easier for you to use if we split it up and provide separate data flows, as follows.

This is how we filtered on the series key:

fund_level_data <- rawdata %>%
    filter( str_sub(data_key, -4, -4) != "T"    &
            str_sub(data_key, -3, -3) != "T"    &
            str_sub(data_key, -2, -2) != "T"    &
            str_sub(data_key, -1, -1) != "T"    )

# Summary data
summary_data <- rawdata %>%
    filter(str_sub(data_key,  6, 11) %in% c("TTTTTT", "TTTTDC", "FUNDOF"))

# Total holdings by fund
th_by_fund <- fund_level_data %>%
    filter( str_sub(data_key, 1, 4) == "TTTT"   )

# South African fund assets (not used)
za_funds <- fund_level_data %>%
    filter( str_sub(data_key, -4, -1) %in% sectors_unique[grepl("South Africa", names(sectors_unique))] &
            str_sub(data_key, 1, 4) %in% asset_line_unique  )

# South African equity fund assets
za_funds_e <- za_funds %>% filter(str_sub(data_key, -4, -3) == "DE")

# South African General Equity fund asset totals
za_funds_DEGN_T <- za_funds_e %>% filter(str_sub(data_key, -4, -1)== "DEGN" &
                                      ((str_sub(data_key, 3, 4) == "TT" & 
                                        str_sub(data_key, 1, 1) == "E"  )   |
                                        str_sub(data_key, 1, 1) != "E"  )   )

# South African General Equity fund assets")
za_funds_DEGN_a <- za_funds_e %>% filter(str_sub(data_key, -4, -1)== "DEGN" &
                                        str_sub(data_key,  3,  4) != "TT"   & 
                                        str_sub(data_key,  1,  1) == "E"    )

# South African other equity fund assets
za_funds_e_other <- za_funds_e %>% filter(str_sub(data_key, -4, -1)!= "DEGN")

# South African multi-asset / real estate fund assets
za_funds_r <- za_funds %>% filter(str_sub(data_key, -4, -3) %in% c("DA", "DR"))

# South African multi-asset fund assets: High Equity"
za_funds_r_V <- za_funds_r %>% filter(str_sub(data_key, -4, -1) == "DAPV")

# South African multi-asset fund assets: Medium and Low Equity
za_funds_r_LM <- za_funds_r %>% filter(str_sub(data_key, -4, -1) %in% c("DAPL", "DAPM"))

# South African multi-asset fund assets: Flexible, Income, Unclassified, and Real Estate
za_funds_r_FITR <- za_funds_r %>% filter(!(str_sub(data_key, -4, -1) %in% c("DAPL", "DAPM", "DAPV")))

# South African interest bearing / money market fund assets
za_funds_m <- za_funds %>% filter(str_sub(data_key, -4, -3) == "DI")

# Worldwide / Regional / Global
world_funds <- fund_level_data %>%
    filter( str_sub(data_key, -4, -1) %in% sectors_unique[
                grepl("Global|Regional|Worldwide", names(sectors_unique))]  &
            str_sub(data_key, 1, 4) %in% asset_line_unique  )

# Funds-of-funds unit trust weightings
fofs <- fund_level_data %>%
    # This could leave out unit trusts that accidentally have one of the pre-defined asset line codes
    filter(!(str_sub(data_key, 1, 4) %in% asset_line_unique))

Description of Overall Raw Structure

The original Excel files have the following sections:

1. Aggregate concepts by sector (all double-counting removed)
2. Aggregated asset class holdings for subsectors
3. Asset class holdings within individual funds, grouped into subsector sheets
4. Fund-of-funds unit trust weightings

Dimensions

We managed to synthesise such a large and intricate dataset into a series key with merely 14 variable characters. Furthermore, the codes used here are formulated in a highly hierarchical and organised fashion, or correspond with normal industry classifications as much as possible.

1. Asset line item
   Four-character code; details below.
2. Fund
   Six-character code, mostly using standard ASISA fund codes, or an aggregate code of ours. For old funds that do not exist anymore and for which ASISA fund codes were unavailable, we generated abbreviated fund codes, checking the quality to ensure no duplication of fund codes.
3. Sector
   Four-character code, following ASISA convention. This describes the geographical exposure and asset class.

Aggregate Concepts by Sector

Mnemonic

1. Total Assets TTTT
2. Sales SSTT
3. Repurchases RSTT
4. Net Inflow ISTT
   Note: redundant, as this equals sales minus repurchases.
5. Number of Accounts NSTC
6. Average balance per account BSVV
7. Number of Funds NSTF
8. Number of CIS Managers NSTM

Fund Code: TTTTTT

Periods 2013Q2 and earlier feature a series with double-counting (TTTTDC), and the main series with double-counting removed (TTTTTT) only goes back to 2009Q4. A CIS History (with double-counting, TTTTDC) is provided only for the total sector TTTT (listed below) from 2013Q1 back to 1966.

Sectors

We use the following terminology to differentiate amongst the hierarchies:

1. Total (TTTT)
2. Sector (ending in TTT)
3. Major sub-sector (ending in TT, including DIMM but excluding DXTT)
4. Sub-sector (ASISA codes; no T in the code, including DIMM)

Sector Codes

0. Total TTTT
1. South African Funds DTTT
   1. Equity DETT
      1. General funds DEGN
      2. Growth funds DEGH (2012Q4 and earlier)
      3. Value funds DEVL (2012Q4 and earlier)
      4. Large Cap funds DELC
      5. Mid- & Small-Cap funds DESM
      6. Unclassified funds DEVS
      7. Resources funds DERB
      8. Industrial funds DEIN
      9. Financial funds DEFN
   2. Multi Asset DATT
      1. Unclassified funds DATD
         Named “Targeted Absolute and Real Return funds” in 2012Q4 and earlier.
      2. Low Equity funds DAPL
      3. Medium Equity funds DAPM
      4. Prudential High Equity Merges into DAPV after 2012Q4.
      5. High Equity funds DAPV
         Previously named “Prudential Variable Equity” in 2012Q4 and earlier.
      6. Flexible funds DAFL
      7. Income funds DAPI
   3. Real Estate DRTT
      1. General funds DRGN
   4. Interest Bearing DITT
      1. Variable Term funds DIBD
      2. Short Term funds DIIN
      3. Varied Specialist DIVS (2012Q4 and earlier)
   5. Total (excluding Money Market) DXTT
   6. Money Market funds DIMM
2. Worldwide Funds WTTT
   1. Equity WETT
      1. General funds WEGN
      2. Unclassified funds WEVS
      3. Technology funds WETN (discontinued after 2012Q4)
   2. Multi Asset WATT
      1. Flexible funds WAFL
      2. Income funds WAPI (2013Q1 to 2016Q1)
   3. Interest Bearing WITT (2012Q4 back to 2010Q2 inclusive)
      1. Varied Specialist funds WIVS
3. Regional Funds RTTT
   1. Equity RETT
      1. General funds REGN
   2. Multi Asset RATT
      1. Flexible funds RAFL
      2. Income funds RAPI
   3. Real Estate RRTT
      1. Real Estate funds RRGN
   4. Interest Bearing RITT
      1. Variable Term funds RIBD
      2. Short Term funds RIIN
4. Global Funds FTTT
   1. Equity FETT
      1. General funds FEGN
      2. Unclassified funds FEVS
   2. Multi Asset GATT
      1. Flexible funds FAFL
      2. Income funds GAPI
      3. Low Equity funds GAPL
      4. Medium Equity funds GAPM
      5. High Equity funds GAPH
   3. Real Estate GRTT
      1. General funds GRGN
   4. Interest Bearing FITT
      1. Variable Term funds FIBD
         Named “Bond funds” in 2012Q4 and earlier.
      2. Short Term funds GIIN
      3. Varied Specialist FIVS (2012Q4 and earlier)
5. Fund of Funds OTTT
6. Retail Funds (sub total) TTRT
   1. Retail Funds TTRF
   2. Third Party Funds TTR3
7. Institutional Funds (sub total) TTIT
   1. Institutional Funds TTIF
   2. Third Party Funds TTI3
   3. Institutional Assets in Retail Funds TTIR

Asset Class Holdings

The list of asset class categories is broadly structured for either equity funds, multi-asset funds or fund-of-funds. The second dimension should be either the total TTTTTT or a fund code. The third dimension is the same as the sector codes outlined above.

Equity

0. Total assets TTTT
1. Domestic assetsTD--
   1. Liquid assets --LQ
   2. Derivatives --DR
   3. Other assets --OH
2. Foreign: other assets TFOT
3. Total assets held in other CIS CTTT
   1. Domestic Funds CDTT
   2. Foreign Indirect Funds CFIT
   3. Foreign Direct Funds CFDT
4. Total domestic & foreign equities ETTT
   • Domestic Equities EDTTForeign Equities EFTT
     Total categories below should have a TT suffix.
   1. Additional EA--
      1. Collective Investment Schemes CI
      2. Corporate Debt CD
      3. Deposit Notes DN
      4. Exchange Traded Funds EF
      5. Exchange Traded Notes EN
      6. Investment Products IP
      7. Kruger Rands KR
      8. Preference Shares PS
      9. Warrants WR
   2. Basic Materials EB--
      1. Chemicals CM
      2. Forestry & Paper FP
      3. Industrial Metals IM
      4. Mining MN
   3. Consumer Goods EG--
      1. Automobiles & Parts AP
      2. Beverages BG
      3. Food Producers FP
      4. Household Goods HG
      5. Leisure Goods LG
      6. Personal Goods PG
      7. Tobacco TC
   4. Consumer Services ES--
      1. Food & Drug Retailers FR
      2. General Retailers GR
      3. Media MD
      4. Travel & Leisure TL
   5. Financials EM--(M for money)
      1. Banks BK
      2. Equity Investment Instruments EI
      3. General Financial GF
      4. Life Insurance LI
      5. Nonequity Investment Instruments NI
      6. Nonlife Insurance LN
      7. Real Estate RE
   6. Health Care EH--
      1. Health Care Equipment & Services ES
      2. Pharmaceuticals & Biotechnology PB
   7. Industrials EI--
      1. Aerospace & Defense AD
      2. Construction & Materials CM
      3. Electronic & Electrical Equipment EE
      4. General Industrials GI
      5. Industrial Engineering IE
      6. Industrial Transportation IT
      7. Support Services SS
   8. Oil & Gas EP--(P for petrol)
      1. Oil & Gas Producers GP
      2. Oil Equipment & Services ES
   9. Telecommunications EC--
      1. Fixed Line Telecommunications FL
      2. Mobile Telecommunications MB
   10. Technology EN--
       1. Software & Computer Services SC
       2. Technology Hardware & Equipment HE
   11. Utilities EU--
       1. Electricity EL
       2. Gas/Water & Multiutilities GW
   12. Specialist Securities EZTT
   13. Other Equities EOTT

Multi Assets

0. Total assets TTTT
   1. Domestic assetsTD--
      1. Liquid assets --LQ
      2. Derivatives --DR
      3. Other assets --OH
   2. ForeignTF--
      1. Cash --CT
      2. Other assets --OT
1. Total equities, excluding real estate ETTX
   Not in interest-bearing funds.
   1. Total domestic equities, excluding real estate EDTX
      1. Additional EATT
      2. Basic Materials EBTT
      3. Consumer Goods EGTT
      4. Consumer Services ESTT
      5. Financials EMTT
      6. Health Care EHTT
      7. Industrials EITT
      8. Oil & Gas EPTT
      9. Telecommunications ECTT
      10. Technology ENTT
      11. Utilities EUTT
      12. Specialist Securities EZTT
      13. Other Equities EOTT
   2. Total foreign equities EFTT
2. Total Money Market instruments MTTT
   Not in equity or real-estate funds.
   1. Total domestic money market MDTT Below: MD--
      1. Cash on Call CC
      2. Corporate debt – convertible DC
      3. Corporate debt – non-convertible DN
      4. Derivatives DR
      5. Financial institutions MI
      6. Government issued paper GP
      7. Other listed Money Market instruments LI
      8. Other unlisted Money Market instruments UI
      9. Public entity issued paper PP
   2. Total foreign money market MFTT Below: MF--
      1. Foreign Bonds IB
      2. Foreign Cash IC
      3. Foreign Other IO
   3. Capital & Money Market Maturity Spread MCTT Below: MC--
      1. Cash CH
      2. 0-3 Months 3M
      3. 3-6 Months 6M
      4. 6-12 Months 1Y
      5. 1-3 Years 3Y
      6. 3-7 Years 7Y
      7. 7-12 Years DY
      8. >12 Years GY
3. Total Real Estate RTTT
   Not in interest-bearing funds.
   1. Non-listed Real Estate RNTT
   2. Listed Real Estate RLTT
4. Total assets held in other CIS CTTT
   1. Domestic Funds CDTT
   2. Foreign Indirect Funds CFIT
   3. Foreign Direct Funds CFDT
5. Total other assets OTTT

Interest-bearing funds exclude 1. equity and 3. real estate from the categories listed above. Equity funds exclude Money Market, from the categories listed above.

Fund-of-Funds

Asset Class

0. Total assets TTTT
1. Unit Trusts (total) UTTT
   1. Domestic Unit Trusts (total) UDTT
      1. Unit Trust Weighting (value)
         The last four characters of the fund code is the mnemonic code.
   2. Foreign Schemes (total) UFTT
2. Liquid Assets LTTT
3. Derivatives DTTT
4. Other Assets OTTT
5. Total Foreign Assets TFTT
6. Number of accounts NSTC
7. Average balance per account BSVV

Fund Code

• Total: FUNDOF
• Individual FoFs: six-character fund code

Sector Code

Sector code of either the fund-of-funds or the underlying unit trust (the latter may be imprecise as it was fuzzy matched with an incomplete mapping history).

ID: CIS_FOREIGN

Source: https://www.asisa.org.za/statistics/collective-investments-schemes/foreign-fund-statistics/

Series Key

Concept IDs: ASSET . FUND_TYPE . FUND . MANAGER . SECTOR

Informal labels: Measure . Type . Fund . Scheme . Sector

1.1.8.4.1 = 19 characters

1. Accounting line

• Assets A
• Sales S
• Repurchases R
• Net Inflow N

2. Type

• Retail R
• Institutional I
• Total T

3. Fund

Please run

read_registry("codelist", id="CL_FOREIGN_FUND")

to see the list of funds (there are over 1600).

4. Manager (a.k.a “scheme”)

Company that manages the investment fund. Please run

read_registry("codelist", id="CL_MANAGER_FOREIGN")

to see the list of managers (there are just over 100).

5. Sector

• Equity E
• Fixed Interest I
• Asset Allocation A
• Total T

Attributes

• Source dataset: “ASISA Collective Investment Funds: Foreign Funds”
• Label: Type code + accounting line code + fund name
• Currency: denomination (e.g. “Euro”)
• Exchange Rate: Indirect exchange rate at that time period
• Unit of multiplication: Units 0
• Unit of measure: Rands
• Frequency: Quarterly

ID: ASISA_HEDGE (although CIS_HEDGE would have made more sense)

There are very few data points in this dataflow (10 series semi-annually since 2021H2), so the Excel file would be easy enough to work with. We use this dataflow for training purposes for our interns. We have made this dataflow public.

Series Key

Concept IDs: MNEMONIC . FUND_TYPE

1. Aggregate Statistic

• Repurchases RPC
• Sales SLS
• Nett Asset Value NAV
• Count of funds NSF
• Nett Asset Value Excluding Fund of Funds NAX

2. Investment Fund Type

• Institutional I
• Retail R

Attributes

• Source dataset: “HEDGE” (should be changed)
• Label: combines mnemonic and type
• Unit measure: Rands or Count
• Frequency: Half-yearly, semester S
• Unit of multiplication: Units 0