schwab_acc
95 columns
- Account master delivered daily via the Schwab Data Delivery App — one row per client account.
- Captures demographic & identity fields: taxpayer name, SSN/TIN, mailing address, phone, date of birth.
- Stores account classification: account type (individual, joint, IRA, trust), registration code, managed-account strategy, custodian code.
- Includes bank-sweep settings, margin agreement flags, and ERISA/retirement plan indicators.
- Column names (
versmrkr_*,a_m,p_v…) are verbatim COBOL field codes from Schwab's fixed-width format — preserved exactly as delivered. - Primary reference table for linking account numbers to client identities across all Schwab datasets.
| Column | Type | Description | |
|---|---|---|---|
versmrkr_6 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
mstracct_number | VARCHAR(255) | nullable | Schwab master account number (custodian-level umbrella account) |
account_title_line_2 | VARCHAR(255) | nullable | Second line of the account title |
versmrkr_7 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
s_i | BOOLEAN | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
versmrkr_9 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
mngdacct_pltfrmcd | VARCHAR(255) | nullable | Managed account platform code identifying the overlay manager system |
ac_cs | VARCHAR(255) | nullable | Account composite/strategy code for managed account grouping |
acct_type | VARCHAR(255) | nullable | Account type code: 1=Individual, 2=Joint, 3=IRA, 4=Trust, 5=Corporate |
cbm_mf | VARCHAR(255) | nullable | |
custdian_id | VARCHAR(255) | nullable | Custodian institution code identifying the holding institution |
txpyr_suff | VARCHAR(255) | nullable | Taxpayer name suffix (Jr., Sr., III etc.) |
a_m | BOOLEAN | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
intbearing_ticker_symbol | VARCHAR(255) | nullable | Ticker of the interest-bearing sweep vehicle (money market fund) |
b_f | BOOLEAN | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
ac_ma | VARCHAR(255) | nullable | |
statemnt_pref | VARCHAR(255) | nullable | Statement delivery preference (Electronic, Paper, Both) |
txpyr_title | VARCHAR(255) | nullable | Taxpayer title (Mr., Mrs., Dr. etc.) |
p_v | VARCHAR(255) | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
intbearing_feat_is_id | VARCHAR(255) | nullable | Item/issue ID of the interest-bearing feature security |
rstr_rsn4 | VARCHAR(255) | nullable | |
versmrkr_3 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
m_t | BIGINT | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
h2_h3 | VARCHAR(255) | nullable | |
schwab_br_cd | VARCHAR(255) | nullable | Schwab branch code where the account is serviced |
coy_cd | VARCHAR(255) | nullable | Company code identifying the Schwab entity |
date_of_birth | DATE | nullable | Primary account holder's date of birth |
versmrkr_5 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
p_m | VARCHAR(255) | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
email_address | VARCHAR(255) | nullable | Account holder primary email address |
versmrkr_8 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
a_t | BOOLEAN | nullable | Adjusted cost basis flag — True if basis has been adjusted |
p_b | VARCHAR(255) | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
cbm_nmf | VARCHAR(255) | nullable | |
schwab_bank_ic_account | VARCHAR(255) | nullable | Schwab Bank interest-credit account identifier |
rstr_rsn3 | VARCHAR(255) | nullable | |
a_o | BIGINT | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
alias_name | VARCHAR(255) | nullable | Account alias or nickname for display purposes |
wh_cd | VARCHAR(255) | nullable | Withholding code for backup withholding status |
account_status | VARCHAR(255) | nullable | Current account status: Active, Closed, Restricted, or Suspended |
s_s | VARCHAR(255) | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
intbr_mm_cd | VARCHAR(255) | nullable | Interest-bearing money market code |
acct_regis | VARCHAR(255) | nullable | Account registration code defining ownership structure |
managed_account_investment_strategy | VARCHAR(255) | nullable | Named investment strategy for discretionary managed accounts |
rstr_rsn2 | BIGINT | nullable | |
st_cd | VARCHAR(255) | nullable | State code (US state abbreviation) |
int_sf | VARCHAR(255) | nullable | |
date_opened | DATE | nullable | Date the brokerage account was opened at Schwab |
f_a | BOOLEAN | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
ac_st | VARCHAR(255) | nullable | |
p_d | BOOLEAN | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
mobile_phone_number | VARCHAR(255) | nullable | Mobile phone number |
master_account_name | VARCHAR(255) | nullable | Human-readable display name of the master/custodian account |
acct_mail_zip | VARCHAR(255) | nullable | Mailing address ZIP/postal code |
business_ph | VARCHAR(255) | nullable | Business phone number |
c_m | VARCHAR(255) | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
ssntin_number | VARCHAR(255) | nullable | Taxpayer SSN or TIN — stored encrypted/masked at source |
u_m | BOOLEAN | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
a_r | VARCHAR(255) | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
taxpayer_last_name | VARCHAR(255) | nullable | Primary taxpayer last name for tax reporting |
primary_contact | VARCHAR(255) | nullable | Primary contact designation for the account |
managed_account_money_manager | VARCHAR(255) | nullable | Money management firm overseeing the account |
o_l | BIGINT | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
filename | VARCHAR(255) | nullable | Source filename on Azure Hot Storage or Fidelity SFTP that produced this row |
p_c | BOOLEAN | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
taxpayer_middle_name | VARCHAR(255) | nullable | Primary taxpayer middle name |
account_title_line_3 | VARCHAR(255) | nullable | Third line of the account title |
custtype_code | VARCHAR(255) | nullable | Customer type code distinguishing retail, institutional, and managed accounts |
cb_mthd_date | DATE | nullable | Date: cb mthd date |
intbr_cd | VARCHAR(255) | nullable | Interest-bearing classification code |
dflt_ltsel | VARCHAR(255) | nullable | Default lot selection method for tax optimisation (HIFO, LIFO, FIFO, SpecID) |
versmrkr_2 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
nr_bn | VARCHAR(255) | nullable | |
acct_mailing_ctry_code | VARCHAR(255) | nullable | Mailing address country code (ISO 2-letter) |
taxpayer_first_name | VARCHAR(255) | nullable | Primary taxpayer first name for tax reporting |
bank_sweep_display_name | VARCHAR(255) | nullable | Display name of the bank sweep program (FDIC-insured cash feature) |
mailing_address_line_2 | VARCHAR(255) | nullable | Mailing address line 2 (apartment, suite, floor) |
account_id | VARCHAR(255) | nullable | Schwab individual account identifier within the master account |
versmrkr_1 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
mailing_address_line_1 | VARCHAR(255) | nullable | Mailing address line 1 (street number and name) |
rstr_rsn1 | BIGINT | nullable | |
rstr_rsn5 | VARCHAR(255) | nullable | |
account_title_line_1 | VARCHAR(255) | nullable | First line of the formal account title (legal name) |
nr_am | VARCHAR(255) | nullable | |
business_date | DATE | nullable | The Schwab business date this record corresponds to — used to snapshot daily files |
organization_primary_name | VARCHAR(255) | nullable | Primary organisation name for corporate or trust accounts |
account_mailing_city | VARCHAR(255) | nullable | Mailing address city |
phone_nbr | VARCHAR(255) | nullable | Primary phone number |
p_e | BOOLEAN | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
versmrkr_4 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
mailing_address_line_3 | VARCHAR(255) | nullable | Mailing address line 3 (additional address information) |
m_a | BOOLEAN | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
__master_account | VARCHAR(255) | nullable | Internal Redwood master account identifier used to partition data across clients |
imported_at | TIMESTAMP WITHOUT TIME ZONE | nullable | Timestamp when this row was first inserted into the database by the sync pipeline |
last_sync_at | TIMESTAMP WITHOUT TIME ZONE | nullable | Timestamp of the most recent sync run that touched this row (useful for detecting stale data) |
schwab_rld
85 columns
- Realized gain/loss detail at the tax-lot level for closed (sold or transferred) positions.
- Tracks acquisition cost, disposal proceeds, short vs long-term holding classification.
- Captures wash-sale disallowed loss amounts to comply with IRS wash-sale rules.
- Each row represents one closed lot event — date opened, date closed, days held.
- Feeds Schedule D and annual tax reporting workflows; 85 columns.
| Column | Type | Description | |
|---|---|---|---|
adjusted_gainloss_dollars | NUMERIC | nullable | Gain or loss amount in USD |
original_face | BOOLEAN | nullable | Original face value for bond or mortgage-backed security lots |
versmrkr_6 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
w_s | VARCHAR(255) | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
mstracct_number | VARCHAR(255) | nullable | Schwab master account number (custodian-level umbrella account) |
trans_code | VARCHAR(255) | nullable | Short code or category identifier |
security_type | VARCHAR(255) | nullable | Security type classification (Common Stock, Corporate Bond, Government Bond, Option…) |
federal_tax_withholding | VARCHAR(255) | nullable | |
wh_st | NUMERIC | nullable | |
c_f | BOOLEAN | nullable | Cost basis flag for special tax situations (e.g. inherited, gifted) |
employee_option_original_cost | VARCHAR(255) | nullable | |
c_i | NUMERIC | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
custdian_id | VARCHAR(255) | nullable | Custodian institution code identifying the holding institution |
g_f | BOOLEAN | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
underlying_ticker_symbol | VARCHAR(255) | nullable | Ticker of the underlying security (for derivatives and structured products) |
underly_sedol | VARCHAR(255) | nullable | SEDOL of the underlying security |
underlying_itm_iss_id | VARCHAR(255) | nullable | Schwab item/issue ID of the underlying security |
trancode_expanded | VARCHAR(255) | nullable | |
proceeds | NUMERIC | nullable | |
publication_timestamp | NUMERIC | nullable | Timestamp: publication timestamp |
adjusted_cost_per_share | NUMERIC | nullable | Average cost per share after amortization adjustments |
versmrkr_3 | NUMERIC | nullable | COBOL version marker — internal fixed-width file format versioning field |
h2_h3 | VARCHAR(255) | nullable | |
underlying_isin | VARCHAR(255) | nullable | ISIN of the underlying security |
item_issue_id | VARCHAR(255) | nullable | Schwab item/issue identifier — unique across all security types including options |
original_purchase_price | NUMERIC | nullable | Price per unit in USD |
prodcatg_code | VARCHAR(255) | nullable | Product category code for transaction classification |
versmrkr_5 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
c_t | VARCHAR(255) | nullable | Cost basis calculation type code |
g_i | VARCHAR(255) | nullable | Gain/loss indicator code |
adjusted_gainloss_percentage | NUMERIC | nullable | Gain or loss amount in USD |
p_f | BOOLEAN | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
adjusted_cost_basis_amortized | NUMERIC | nullable | Cost basis adjusted for premium/discount amortization (for bonds) |
options_display_symbol | VARCHAR(255) | nullable | Formatted display symbol for options (e.g. AAPL 240119C00180000) |
a_t | BOOLEAN | nullable | Adjusted cost basis flag — True if basis has been adjusted |
underlyng_cusip | VARCHAR(255) | nullable | CUSIP of the underlying security (Schwab spelling variant) |
closing_date | DATE | nullable | Date: closing date |
isin | VARCHAR(255) | nullable | ISIN — 12-character International Securities Identification Number (ISO 6166) |
r_u | VARCHAR(255) | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
disallowed_loss_amount | BOOLEAN | nullable | Loss disallowed under IRS wash-sale rules (must be added back to basis) |
days_held | VARCHAR(255) | nullable | Number of calendar days the lot has been held (determines short vs long-term) |
l_s | VARCHAR(255) | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
gainloss_percentage | NUMERIC | nullable | Gain or loss amount in USD |
gainloss_dollars | NUMERIC | nullable | Gain or loss amount in USD |
employee_option_realized_gainloss | NUMERIC | nullable | Gain or loss amount in USD |
prod_code | VARCHAR(255) | nullable | Product type code — EQ=Equity, MF=Mutual Fund, OPT=Option, FI=Fixed Income, ETF=ETF |
closing_quantity | NUMERIC | nullable | Share or unit quantity |
underly_schwab | VARCHAR(255) | nullable | Schwab internal number of the underlying security |
master_account_name | VARCHAR(255) | nullable | Human-readable display name of the master/custodian account |
c_m | VARCHAR(255) | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
proceeds_per_share | NUMERIC | nullable | |
order_number | VARCHAR(255) | nullable | Order number from the trading system |
sedol | VARCHAR(255) | nullable | SEDOL — 7-character London Stock Exchange identifier used in UK/international markets |
transaction_gainloss_pct | NUMERIC | nullable | Gain or loss amount in USD |
cusip | VARCHAR(255) | nullable | CUSIP — 9-character North American security identifier issued by DTCC |
filename | VARCHAR(255) | nullable | Source filename on Azure Hot Storage or Fidelity SFTP that produced this row |
ytd_amortization | VARCHAR(255) | nullable | |
close_event_id | VARCHAR(255) | nullable | Identifier / foreign key linking to the close event entity |
transaction_cost_per_share | NUMERIC | nullable | Transaction cost per share for this lot |
cost_basis_unamortized | NUMERIC | nullable | Raw cost basis before amortization of bond premium or discount |
ticker_symbol | VARCHAR(255) | nullable | Exchange ticker symbol (e.g. AAPL, MSFT) — may be blank for bonds and OTC securities |
versmrkr_2 | BOOLEAN | nullable | COBOL version marker — internal fixed-width file format versioning field |
cost_per_share | NUMERIC | nullable | Average cost per share — unamortized (used for equity lot tracking) |
close_open_event_id | VARCHAR(255) | nullable | Identifier / foreign key linking to the close open event entity |
schwab_sec_nbr | VARCHAR(255) | nullable | Schwab's internal proprietary security number for their master file |
account_id | VARCHAR(255) | nullable | Schwab individual account identifier within the master account |
h_t | VARCHAR(255) | nullable | Holding period type: S=Short-term, L=Long-term |
state_tax_withholding | NUMERIC | nullable | State tax withheld on this transaction |
versmrkr_1 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
adj_disallowed_loss_amount | NUMERIC | nullable | Monetary amount in USD |
lot_selct | NUMERIC | nullable | |
cbm | VARCHAR(255) | nullable | |
transaction_gainloss_dollars | NUMERIC | nullable | Gain or loss amount in USD |
orgpurch_date | DATE | nullable | Original purchase date — may differ from acquired_date for gifted or transferred lots |
tax_code | VARCHAR(255) | nullable | Tax treatment code controlling cost basis method and 1099 classification |
business_date | DATE | nullable | The Schwab business date this record corresponds to — used to snapshot daily files |
settle_date | DATE | nullable | Date: settle date |
acquired_date | DATE | nullable | Date this specific tax lot was acquired |
transaction_cost | VARCHAR(255) | nullable | Per-lot transaction cost (commissions allocated to this lot) |
versmrkr_4 | NUMERIC | nullable | COBOL version marker — internal fixed-width file format versioning field |
__master_account | VARCHAR(255) | nullable | Internal Redwood master account identifier used to partition data across clients |
versmrkr_7 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
schwab_order_id | VARCHAR(255) | nullable | Identifier / foreign key linking to the schwab order entity |
imported_at | TIMESTAMP WITHOUT TIME ZONE | nullable | Timestamp when this row was first inserted into the database by the sync pipeline |
last_sync_at | TIMESTAMP WITHOUT TIME ZONE | nullable | Timestamp of the most recent sync run that touched this row (useful for detecting stale data) |
schwab_rlu
86 columns
- Unrealized gain/loss snapshot for open (still-held) positions, at the tax-lot level.
- Mirrors schwab_rld structure but for lots not yet closed.
- Fields: acquisition date, original cost, current market value, unrealized gain/loss amount and percent.
- Wash-sale flags and lot-selection method (FIFO, specific ID) recorded per lot.
- Used for mark-to-market P&L reporting and unrealized gain/loss attribution; 86 columns.
| Column | Type | Description | |
|---|---|---|---|
adjusted_gainloss_dollars | NUMERIC | nullable | Gain or loss amount in USD |
original_face | BOOLEAN | nullable | Original face value for bond or mortgage-backed security lots |
versmrkr_6 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
w_s | VARCHAR(255) | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
mstracct_number | VARCHAR(255) | nullable | Schwab master account number (custodian-level umbrella account) |
versmrkr_7 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
trans_code | VARCHAR(255) | nullable | Short code or category identifier |
security_type | VARCHAR(255) | nullable | Security type classification (Common Stock, Corporate Bond, Government Bond, Option…) |
federal_tax_withholding | VARCHAR(255) | nullable | |
wh_st | NUMERIC | nullable | |
c_f | BOOLEAN | nullable | Cost basis flag for special tax situations (e.g. inherited, gifted) |
employee_option_original_cost | VARCHAR(255) | nullable | |
c_i | NUMERIC | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
custdian_id | VARCHAR(255) | nullable | Custodian institution code identifying the holding institution |
g_f | BOOLEAN | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
underlying_ticker_symbol | VARCHAR(255) | nullable | Ticker of the underlying security (for derivatives and structured products) |
underly_sedol | VARCHAR(255) | nullable | SEDOL of the underlying security |
underlying_itm_iss_id | VARCHAR(255) | nullable | Schwab item/issue ID of the underlying security |
trancode_expanded | VARCHAR(255) | nullable | |
proceeds | NUMERIC | nullable | |
publication_timestamp | VARCHAR(255) | nullable | Timestamp: publication timestamp |
adjusted_cost_per_share | NUMERIC | nullable | Average cost per share after amortization adjustments |
versmrkr_3 | BOOLEAN | nullable | COBOL version marker — internal fixed-width file format versioning field |
h2_h3 | VARCHAR(255) | nullable | |
underlying_isin | VARCHAR(255) | nullable | ISIN of the underlying security |
item_issue_id | VARCHAR(255) | nullable | Schwab item/issue identifier — unique across all security types including options |
original_purchase_price | NUMERIC | nullable | Price per unit in USD |
prodcatg_code | VARCHAR(255) | nullable | Product category code for transaction classification |
versmrkr_5 | NUMERIC | nullable | COBOL version marker — internal fixed-width file format versioning field |
c_t | VARCHAR(255) | nullable | Cost basis calculation type code |
g_i | VARCHAR(255) | nullable | Gain/loss indicator code |
adjusted_gainloss_percentage | NUMERIC | nullable | Gain or loss amount in USD |
p_f | BOOLEAN | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
adjusted_cost_basis_amortized | NUMERIC | nullable | Cost basis adjusted for premium/discount amortization (for bonds) |
options_display_symbol | VARCHAR(255) | nullable | Formatted display symbol for options (e.g. AAPL 240119C00180000) |
a_t | BOOLEAN | nullable | Adjusted cost basis flag — True if basis has been adjusted |
underlyng_cusip | VARCHAR(255) | nullable | CUSIP of the underlying security (Schwab spelling variant) |
closing_date | DATE | nullable | Date: closing date |
isin | VARCHAR(255) | nullable | ISIN — 12-character International Securities Identification Number (ISO 6166) |
r_u | VARCHAR(255) | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
disallowed_loss_amount | BOOLEAN | nullable | Loss disallowed under IRS wash-sale rules (must be added back to basis) |
days_held | VARCHAR(255) | nullable | Number of calendar days the lot has been held (determines short vs long-term) |
l_s | VARCHAR(255) | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
gainloss_percentage | NUMERIC | nullable | Gain or loss amount in USD |
gainloss_dollars | NUMERIC | nullable | Gain or loss amount in USD |
employee_option_realized_gainloss | NUMERIC | nullable | Gain or loss amount in USD |
prod_code | VARCHAR(255) | nullable | Product type code — EQ=Equity, MF=Mutual Fund, OPT=Option, FI=Fixed Income, ETF=ETF |
closing_quantity | NUMERIC | nullable | Share or unit quantity |
underly_schwab | VARCHAR(255) | nullable | Schwab internal number of the underlying security |
master_account_name | VARCHAR(255) | nullable | Human-readable display name of the master/custodian account |
c_m | VARCHAR(255) | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
proceeds_per_share | NUMERIC | nullable | |
order_number | VARCHAR(255) | nullable | Order number from the trading system |
sedol | VARCHAR(255) | nullable | SEDOL — 7-character London Stock Exchange identifier used in UK/international markets |
transaction_gainloss_pct | NUMERIC | nullable | Gain or loss amount in USD |
cusip | VARCHAR(255) | nullable | CUSIP — 9-character North American security identifier issued by DTCC |
filename | VARCHAR(255) | nullable | Source filename on Azure Hot Storage or Fidelity SFTP that produced this row |
ytd_amortization | VARCHAR(255) | nullable | |
close_event_id | VARCHAR(255) | nullable | Identifier / foreign key linking to the close event entity |
transaction_cost_per_share | NUMERIC | nullable | Transaction cost per share for this lot |
cost_basis_unamortized | NUMERIC | nullable | Raw cost basis before amortization of bond premium or discount |
ticker_symbol | VARCHAR(255) | nullable | Exchange ticker symbol (e.g. AAPL, MSFT) — may be blank for bonds and OTC securities |
versmrkr_2 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
cost_per_share | NUMERIC | nullable | Average cost per share — unamortized (used for equity lot tracking) |
close_open_event_id | VARCHAR(255) | nullable | Identifier / foreign key linking to the close open event entity |
schwab_sec_nbr | VARCHAR(255) | nullable | Schwab's internal proprietary security number for their master file |
account_id | VARCHAR(255) | nullable | Schwab individual account identifier within the master account |
h_t | VARCHAR(255) | nullable | Holding period type: S=Short-term, L=Long-term |
state_tax_withholding | NUMERIC | nullable | State tax withheld on this transaction |
versmrkr_1 | NUMERIC | nullable | COBOL version marker — internal fixed-width file format versioning field |
adj_disallowed_loss_amount | NUMERIC | nullable | Monetary amount in USD |
lot_selct | NUMERIC | nullable | |
cbm | VARCHAR(255) | nullable | |
transaction_gainloss_dollars | NUMERIC | nullable | Gain or loss amount in USD |
orgpurch_date | DATE | nullable | Original purchase date — may differ from acquired_date for gifted or transferred lots |
tax_code | VARCHAR(255) | nullable | Tax treatment code controlling cost basis method and 1099 classification |
business_date | DATE | nullable | The Schwab business date this record corresponds to — used to snapshot daily files |
settle_date | DATE | nullable | Date: settle date |
acquired_date | DATE | nullable | Date this specific tax lot was acquired |
transaction_cost | VARCHAR(255) | nullable | Per-lot transaction cost (commissions allocated to this lot) |
versmrkr_4 | NUMERIC | nullable | COBOL version marker — internal fixed-width file format versioning field |
schwab_order_id | VARCHAR(255) | nullable | Identifier / foreign key linking to the schwab order entity |
versmrkr_8 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
__master_account | VARCHAR(255) | nullable | Internal Redwood master account identifier used to partition data across clients |
imported_at | TIMESTAMP WITHOUT TIME ZONE | nullable | Timestamp when this row was first inserted into the database by the sync pipeline |
last_sync_at | TIMESTAMP WITHOUT TIME ZONE | nullable | Timestamp of the most recent sync run that touched this row (useful for detecting stale data) |
schwab_rly
86 columns
- Year-to-date realized gain/loss summary — aggregates closed-lot activity across the calendar year.
- One row per security per account — summarized from individual lot-level detail.
- Breaks out short-term vs long-term realized gains and losses separately.
- Supports annual tax reporting, performance attribution, and YTD tax-impact estimates.
- 86 columns; parallel structure to schwab_rlu but for closed positions only.
| Column | Type | Description | |
|---|---|---|---|
adjusted_gainloss_dollars | NUMERIC | nullable | Gain or loss amount in USD |
original_face | BOOLEAN | nullable | Original face value for bond or mortgage-backed security lots |
versmrkr_6 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
w_s | VARCHAR(255) | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
mstracct_number | VARCHAR(255) | nullable | Schwab master account number (custodian-level umbrella account) |
versmrkr_7 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
trans_code | VARCHAR(255) | nullable | Short code or category identifier |
security_type | VARCHAR(255) | nullable | Security type classification (Common Stock, Corporate Bond, Government Bond, Option…) |
federal_tax_withholding | VARCHAR(255) | nullable | |
wh_st | NUMERIC | nullable | |
c_f | BOOLEAN | nullable | Cost basis flag for special tax situations (e.g. inherited, gifted) |
employee_option_original_cost | VARCHAR(255) | nullable | |
c_i | NUMERIC | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
custdian_id | VARCHAR(255) | nullable | Custodian institution code identifying the holding institution |
g_f | BOOLEAN | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
underlying_ticker_symbol | VARCHAR(255) | nullable | Ticker of the underlying security (for derivatives and structured products) |
underly_sedol | VARCHAR(255) | nullable | SEDOL of the underlying security |
underlying_itm_iss_id | VARCHAR(255) | nullable | Schwab item/issue ID of the underlying security |
trancode_expanded | VARCHAR(255) | nullable | |
proceeds | NUMERIC | nullable | |
publication_timestamp | VARCHAR(255) | nullable | Timestamp: publication timestamp |
adjusted_cost_per_share | NUMERIC | nullable | Average cost per share after amortization adjustments |
versmrkr_3 | BOOLEAN | nullable | COBOL version marker — internal fixed-width file format versioning field |
h2_h3 | VARCHAR(255) | nullable | |
underlying_isin | VARCHAR(255) | nullable | ISIN of the underlying security |
item_issue_id | VARCHAR(255) | nullable | Schwab item/issue identifier — unique across all security types including options |
original_purchase_price | NUMERIC | nullable | Price per unit in USD |
prodcatg_code | VARCHAR(255) | nullable | Product category code for transaction classification |
versmrkr_5 | NUMERIC | nullable | COBOL version marker — internal fixed-width file format versioning field |
c_t | VARCHAR(255) | nullable | Cost basis calculation type code |
g_i | VARCHAR(255) | nullable | Gain/loss indicator code |
adjusted_gainloss_percentage | NUMERIC | nullable | Gain or loss amount in USD |
p_f | BOOLEAN | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
adjusted_cost_basis_amortized | NUMERIC | nullable | Cost basis adjusted for premium/discount amortization (for bonds) |
options_display_symbol | VARCHAR(255) | nullable | Formatted display symbol for options (e.g. AAPL 240119C00180000) |
a_t | BOOLEAN | nullable | Adjusted cost basis flag — True if basis has been adjusted |
underlyng_cusip | VARCHAR(255) | nullable | CUSIP of the underlying security (Schwab spelling variant) |
closing_date | DATE | nullable | Date: closing date |
isin | VARCHAR(255) | nullable | ISIN — 12-character International Securities Identification Number (ISO 6166) |
r_u | VARCHAR(255) | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
disallowed_loss_amount | BOOLEAN | nullable | Loss disallowed under IRS wash-sale rules (must be added back to basis) |
days_held | VARCHAR(255) | nullable | Number of calendar days the lot has been held (determines short vs long-term) |
l_s | VARCHAR(255) | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
gainloss_percentage | NUMERIC | nullable | Gain or loss amount in USD |
gainloss_dollars | NUMERIC | nullable | Gain or loss amount in USD |
employee_option_realized_gainloss | NUMERIC | nullable | Gain or loss amount in USD |
prod_code | VARCHAR(255) | nullable | Product type code — EQ=Equity, MF=Mutual Fund, OPT=Option, FI=Fixed Income, ETF=ETF |
closing_quantity | NUMERIC | nullable | Share or unit quantity |
underly_schwab | VARCHAR(255) | nullable | Schwab internal number of the underlying security |
master_account_name | VARCHAR(255) | nullable | Human-readable display name of the master/custodian account |
c_m | VARCHAR(255) | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
proceeds_per_share | NUMERIC | nullable | |
order_number | VARCHAR(255) | nullable | Order number from the trading system |
sedol | VARCHAR(255) | nullable | SEDOL — 7-character London Stock Exchange identifier used in UK/international markets |
transaction_gainloss_pct | NUMERIC | nullable | Gain or loss amount in USD |
cusip | VARCHAR(255) | nullable | CUSIP — 9-character North American security identifier issued by DTCC |
filename | VARCHAR(255) | nullable | Source filename on Azure Hot Storage or Fidelity SFTP that produced this row |
ytd_amortization | VARCHAR(255) | nullable | |
close_event_id | VARCHAR(255) | nullable | Identifier / foreign key linking to the close event entity |
transaction_cost_per_share | NUMERIC | nullable | Transaction cost per share for this lot |
cost_basis_unamortized | NUMERIC | nullable | Raw cost basis before amortization of bond premium or discount |
ticker_symbol | VARCHAR(255) | nullable | Exchange ticker symbol (e.g. AAPL, MSFT) — may be blank for bonds and OTC securities |
versmrkr_2 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
cost_per_share | NUMERIC | nullable | Average cost per share — unamortized (used for equity lot tracking) |
close_open_event_id | VARCHAR(255) | nullable | Identifier / foreign key linking to the close open event entity |
schwab_sec_nbr | VARCHAR(255) | nullable | Schwab's internal proprietary security number for their master file |
account_id | VARCHAR(255) | nullable | Schwab individual account identifier within the master account |
h_t | VARCHAR(255) | nullable | Holding period type: S=Short-term, L=Long-term |
state_tax_withholding | NUMERIC | nullable | State tax withheld on this transaction |
versmrkr_1 | NUMERIC | nullable | COBOL version marker — internal fixed-width file format versioning field |
adj_disallowed_loss_amount | NUMERIC | nullable | Monetary amount in USD |
lot_selct | NUMERIC | nullable | |
cbm | VARCHAR(255) | nullable | |
transaction_gainloss_dollars | NUMERIC | nullable | Gain or loss amount in USD |
orgpurch_date | DATE | nullable | Original purchase date — may differ from acquired_date for gifted or transferred lots |
tax_code | VARCHAR(255) | nullable | Tax treatment code controlling cost basis method and 1099 classification |
business_date | DATE | nullable | The Schwab business date this record corresponds to — used to snapshot daily files |
settle_date | DATE | nullable | Date: settle date |
acquired_date | DATE | nullable | Date this specific tax lot was acquired |
transaction_cost | VARCHAR(255) | nullable | Per-lot transaction cost (commissions allocated to this lot) |
versmrkr_4 | NUMERIC | nullable | COBOL version marker — internal fixed-width file format versioning field |
schwab_order_id | VARCHAR(255) | nullable | Identifier / foreign key linking to the schwab order entity |
versmrkr_8 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
__master_account | VARCHAR(255) | nullable | Internal Redwood master account identifier used to partition data across clients |
imported_at | TIMESTAMP WITHOUT TIME ZONE | nullable | Timestamp when this row was first inserted into the database by the sync pipeline |
last_sync_at | TIMESTAMP WITHOUT TIME ZONE | nullable | Timestamp of the most recent sync run that touched this row (useful for detecting stale data) |
schwab_rps
68 columns
- Real-time position snapshot by account and security as of each business date.
- Separate quantity columns for settled, unsettled-long, unsettled-short, cash-settled, margin-settled, and when-issued positions.
- Contains current market value, product category, long/short indicator.
- All security identifiers included: CUSIP, ISIN, SEDOL, Schwab security number, ticker.
- 68 columns — used for daily position reconciliation and risk reporting.
| Column | Type | Description | |
|---|---|---|---|
security_description_line_3 | VARCHAR(255) | nullable | Text description |
unrul_sufid | VARCHAR(255) | nullable | |
c_g | BOOLEAN | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
closing_price_unfactored | NUMERIC | nullable | Raw closing price before any adjustment factors are applied |
scrtydes_line_4 | VARCHAR(255) | nullable | |
mstracct_number | VARCHAR(255) | nullable | Schwab master account number (custodian-level umbrella account) |
underlying_industry_ticker_symbol | VARCHAR(255) | nullable | Industry-level ticker for the underlying (e.g. sector ETF) |
security_description_line_1 | VARCHAR(255) | nullable | Text description |
accounting_rule_code | VARCHAR(255) | nullable | GAAP accounting rule applied to this transaction |
custdian_id | VARCHAR(255) | nullable | Custodian institution code identifying the holding institution |
underlying_ticker_symbol | VARCHAR(255) | nullable | Ticker of the underlying security (for derivatives and structured products) |
underly_sedol | VARCHAR(255) | nullable | SEDOL of the underlying security |
underlying_itm_iss_id | BIGINT | nullable | Schwab item/issue ID of the underlying security |
non_tradable_quantity | NUMERIC | nullable | Shares that cannot currently be traded (restricted, pledged, or in transfer) |
rulst_sufid | VARCHAR(255) | nullable | |
h2_h3 | VARCHAR(255) | nullable | |
underlying_isin | VARCHAR(255) | nullable | ISIN of the underlying security |
quantity_settledunsettled | NUMERIC | nullable | Share or unit quantity |
item_issue_id | VARCHAR(255) | nullable | Schwab item/issue identifier — unique across all security types including options |
factor_date | DATE | nullable | Date the pool factor was applied for MBS/ABS securities |
versmrkr_5 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
prodcatg_code | VARCHAR(255) | nullable | Product category code for transaction classification |
quantity_settled | NUMERIC | nullable | Share quantity in settled positions (cash exchanged) |
options_display_symbol | VARCHAR(255) | nullable | Formatted display symbol for options (e.g. AAPL 240119C00180000) |
underlyng_cusip | VARCHAR(255) | nullable | CUSIP of the underlying security (Schwab spelling variant) |
isin | VARCHAR(255) | nullable | ISIN — 12-character International Securities Identification Number (ISO 6166) |
closing_price | NUMERIC | nullable | End-of-day closing price adjusted for splits and dividends |
tips_factor | NUMERIC | nullable | TIPS inflation adjustment factor applied to principal |
quantity_unsettledlong | NUMERIC | nullable | Unsettled long position quantity (bought but not yet settled) |
industry_ticker_symbol | VARCHAR(255) | nullable | Industry classification ticker assigned by Schwab |
l_s | VARCHAR(255) | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
ly_st | VARCHAR(255) | nullable | |
prod_code | VARCHAR(255) | nullable | Product type code — EQ=Equity, MF=Mutual Fund, OPT=Option, FI=Fixed Income, ETF=ETF |
security_description_line_2 | VARCHAR(255) | nullable | Text description |
underly_schwab | VARCHAR(255) | nullable | Schwab internal number of the underlying security |
master_account_name | VARCHAR(255) | nullable | Human-readable display name of the master/custodian account |
mnymk_code | VARCHAR(255) | nullable | Money market fund classification code |
sedol | VARCHAR(255) | nullable | SEDOL — 7-character London Stock Exchange identifier used in UK/international markets |
cusip | VARCHAR(255) | nullable | CUSIP — 9-character North American security identifier issued by DTCC |
filename | VARCHAR(255) | nullable | Source filename on Azure Hot Storage or Fidelity SFTP that produced this row |
market_value_settledunsettled | NUMERIC | nullable | Combined market value of settled and unsettled positions |
d_r | BOOLEAN | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
ticker_symbol | VARCHAR(255) | nullable | Exchange ticker symbol (e.g. AAPL, MSFT) — may be blank for bonds and OTC securities |
asset_backed_factor | NUMERIC | nullable | Pool factor for asset-backed securities |
versmrkr_2 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
quantity_unsettledshort | NUMERIC | nullable | Unsettled short position quantity (sold but not yet settled) |
schwab_sec_nbr | VARCHAR(255) | nullable | Schwab's internal proprietary security number for their master file |
account_id | VARCHAR(255) | nullable | Schwab individual account identifier within the master account |
versmrkr_1 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
a_i | BOOLEAN | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
factor | NUMERIC | nullable | Pool factor — remaining principal as a percentage of original face (for MBS/ABS) |
tax_code | VARCHAR(255) | nullable | Tax treatment code controlling cost basis method and 1099 classification |
business_date | DATE | nullable | The Schwab business date this record corresponds to — used to snapshot daily files |
secprice_lstupdte | VARCHAR(255) | nullable | Price per unit in USD |
versmrkr_4 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
row_id | INTEGER | required | Auto-incrementing row identifier for position records |
created_at | TIMESTAMP WITHOUT TIME ZONE | nullable | Row creation timestamp (database-side) |
ticker | VARCHAR(255) | nullable | Exchange ticker symbol — short form |
cash_balance_type | VARCHAR(255) | nullable | Account or position balance in USD |
__master_account | VARCHAR(255) | nullable | Internal Redwood master account identifier used to partition data across clients |
account | VARCHAR(255) | nullable | Account reference in TCF/position file |
versmrkr_6 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
quantity_cash_settled | NUMERIC | nullable | Quantity in cash accounts that has settled |
quantity_margin_settled | NUMERIC | nullable | Quantity in margin accounts that has settled |
quantity_short_settled | NUMERIC | nullable | Short position quantity that has settled |
quantity_wheniss_settled | NUMERIC | nullable | When-issued position quantity that has settled |
imported_at | TIMESTAMP WITHOUT TIME ZONE | nullable | Timestamp when this row was first inserted into the database by the sync pipeline |
last_sync_at | TIMESTAMP WITHOUT TIME ZONE | nullable | Timestamp of the most recent sync run that touched this row (useful for detecting stale data) |
schwab_rps2
38 columns
- Condensed position summary — lighter variant of schwab_rps with fewer quantity breakout fields.
- 38 columns covering account, security, and market-value essentials.
- Used for accounts with simpler position structures or cash-equivalent holdings (money market, sweep).
- Processed by the same reconciliation pipeline as schwab_rps but stored separately to avoid schema conflicts.
| Column | Type | Description | |
|---|---|---|---|
net_mv_plus_cash | NUMERIC | nullable | |
eqty_excl_option | NUMERIC | nullable | |
mstracct_number | VARCHAR(255) | nullable | Schwab master account number (custodian-level umbrella account) |
account_title_line_2 | VARCHAR(255) | nullable | Second line of the account title |
mtd_margin_int | NUMERIC | nullable | |
bank_sweep_ibf | NUMERIC | nullable | |
acct_type | VARCHAR(255) | nullable | Account type code: 1=Individual, 2=Joint, 3=IRA, 4=Trust, 5=Corporate |
custdian_id | VARCHAR(255) | nullable | Custodian institution code identifying the holding institution |
net_mv_positions | NUMERIC | nullable | |
versmrkr_3 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
mkt_value_short | NUMERIC | nullable | |
available_to_pay | NUMERIC | nullable | |
mvl_cash_ex_optn | NUMERIC | nullable | |
option_rqrmnts | NUMERIC | nullable | |
daily_margin_int | NUMERIC | nullable | |
acct_regis | VARCHAR(255) | nullable | Account registration code defining ownership structure |
eqty_percentage | NUMERIC | nullable | Percentage value |
cash_margin_bal_settled | NUMERIC | nullable | |
margin_balance | NUMERIC | nullable | Account or position balance in USD |
master_account_name | VARCHAR(255) | nullable | Human-readable display name of the master/custodian account |
mkt_value_long | NUMERIC | nullable | |
money_mkt_funds | NUMERIC | nullable | |
mnth_end_div_pay | NUMERIC | nullable | |
filename | VARCHAR(255) | nullable | Source filename on Azure Hot Storage or Fidelity SFTP that produced this row |
cash_balance_settled_only | NUMERIC | nullable | Account or position balance in USD |
account_title_line_3 | VARCHAR(255) | nullable | Third line of the account title |
net_credit_debit | NUMERIC | nullable | |
mrgn_buying_pwr | NUMERIC | nullable | |
eqty_incl_option | NUMERIC | nullable | |
maintenance_call | NUMERIC | nullable | |
account_id | VARCHAR(255) | nullable | Schwab individual account identifier within the master account |
account_title_line_1 | VARCHAR(255) | nullable | First line of the formal account title (legal name) |
business_date | DATE | nullable | The Schwab business date this record corresponds to — used to snapshot daily files |
__master_account | VARCHAR(255) | nullable | Internal Redwood master account identifier used to partition data across clients |
versmrkr_6 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
bank_cash | NUMERIC | nullable | |
imported_at | TIMESTAMP WITHOUT TIME ZONE | nullable | Timestamp when this row was first inserted into the database by the sync pipeline |
last_sync_at | TIMESTAMP WITHOUT TIME ZONE | nullable | Timestamp of the most recent sync run that touched this row (useful for detecting stale data) |
schwab_rtu
75 columns
- Unrealized gain/loss by tax lot — short-term holdings (held 1 year or less).
- Captures acquisition date, cost basis, current market value for each short-term open lot.
- Holding-period classification applied at the lot level based on days-held count.
- Used for short-term capital gains estimation and tax-loss harvesting analysis.
- 75 columns; companion to schwab_rty (long-term lots).
| Column | Type | Description | |
|---|---|---|---|
adjusted_gainloss_dollars | NUMERIC | nullable | Gain or loss amount in USD |
original_face | NUMERIC | nullable | Original face value for bond or mortgage-backed security lots |
w_s | BOOLEAN | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
mstracct_number | VARCHAR(255) | nullable | Schwab master account number (custodian-level umbrella account) |
trans_code | VARCHAR(255) | nullable | Short code or category identifier |
security_type | VARCHAR(255) | nullable | Security type classification (Common Stock, Corporate Bond, Government Bond, Option…) |
federal_tax_withholding | NUMERIC | nullable | |
wh_st | VARCHAR(255) | nullable | |
c_f | BOOLEAN | nullable | Cost basis flag for special tax situations (e.g. inherited, gifted) |
c_i | VARCHAR(255) | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
custdian_id | VARCHAR(255) | nullable | Custodian institution code identifying the holding institution |
g_f | BOOLEAN | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
underlying_ticker_symbol | VARCHAR(255) | nullable | Ticker of the underlying security (for derivatives and structured products) |
underly_sedol | VARCHAR(255) | nullable | SEDOL of the underlying security |
underlying_itm_iss_id | VARCHAR(255) | nullable | Schwab item/issue ID of the underlying security |
trancode_expanded | VARCHAR(255) | nullable | |
proceeds | NUMERIC | nullable | |
publication_timestamp | VARCHAR(255) | nullable | Timestamp: publication timestamp |
adjusted_cost_per_share | NUMERIC | nullable | Average cost per share after amortization adjustments |
versmrkr_3 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
h2_h3 | VARCHAR(255) | nullable | |
underlying_isin | VARCHAR(255) | nullable | ISIN of the underlying security |
item_issue_id | VARCHAR(255) | nullable | Schwab item/issue identifier — unique across all security types including options |
prodcatg_code | VARCHAR(255) | nullable | Product category code for transaction classification |
versmrkr_5 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
c_t | VARCHAR(255) | nullable | Cost basis calculation type code |
adjusted_gainloss_percentage | NUMERIC | nullable | Gain or loss amount in USD |
p_f | BOOLEAN | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
adjusted_cost_basis_amortized | NUMERIC | nullable | Cost basis adjusted for premium/discount amortization (for bonds) |
options_display_symbol | VARCHAR(255) | nullable | Formatted display symbol for options (e.g. AAPL 240119C00180000) |
a_t | BOOLEAN | nullable | Adjusted cost basis flag — True if basis has been adjusted |
underlyng_cusip | VARCHAR(255) | nullable | CUSIP of the underlying security (Schwab spelling variant) |
closing_date | DATE | nullable | Date: closing date |
isin | VARCHAR(255) | nullable | ISIN — 12-character International Securities Identification Number (ISO 6166) |
disallowed_loss_amount | NUMERIC | nullable | Loss disallowed under IRS wash-sale rules (must be added back to basis) |
days_held | VARCHAR(255) | nullable | Number of calendar days the lot has been held (determines short vs long-term) |
l_s | VARCHAR(255) | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
gainloss_percentage | NUMERIC | nullable | Gain or loss amount in USD |
gainloss_dollars | NUMERIC | nullable | Gain or loss amount in USD |
prod_code | VARCHAR(255) | nullable | Product type code — EQ=Equity, MF=Mutual Fund, OPT=Option, FI=Fixed Income, ETF=ETF |
closing_quantity | NUMERIC | nullable | Share or unit quantity |
underly_schwab | VARCHAR(255) | nullable | Schwab internal number of the underlying security |
master_account_name | VARCHAR(255) | nullable | Human-readable display name of the master/custodian account |
c_m | VARCHAR(255) | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
proceeds_per_share | NUMERIC | nullable | |
sedol | VARCHAR(255) | nullable | SEDOL — 7-character London Stock Exchange identifier used in UK/international markets |
transaction_gainloss_pct | NUMERIC | nullable | Gain or loss amount in USD |
cusip | VARCHAR(255) | nullable | CUSIP — 9-character North American security identifier issued by DTCC |
filename | VARCHAR(255) | nullable | Source filename on Azure Hot Storage or Fidelity SFTP that produced this row |
ytd_amortization | NUMERIC | nullable | |
close_event_id | VARCHAR(255) | nullable | Identifier / foreign key linking to the close event entity |
transaction_cost_per_share | NUMERIC | nullable | Transaction cost per share for this lot |
cost_basis_unamortized | NUMERIC | nullable | Raw cost basis before amortization of bond premium or discount |
ticker_symbol | VARCHAR(255) | nullable | Exchange ticker symbol (e.g. AAPL, MSFT) — may be blank for bonds and OTC securities |
versmrkr_2 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
cost_per_share | NUMERIC | nullable | Average cost per share — unamortized (used for equity lot tracking) |
schwab_sec_nbr | VARCHAR(255) | nullable | Schwab's internal proprietary security number for their master file |
account_id | VARCHAR(255) | nullable | Schwab individual account identifier within the master account |
h_t | VARCHAR(255) | nullable | Holding period type: S=Short-term, L=Long-term |
state_tax_withholding | NUMERIC | nullable | State tax withheld on this transaction |
versmrkr_1 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
adj_disallowed_loss_amount | NUMERIC | nullable | Monetary amount in USD |
lot_selct | VARCHAR(255) | nullable | |
cbm | VARCHAR(255) | nullable | |
transaction_gainloss_dollars | NUMERIC | nullable | Gain or loss amount in USD |
tax_code | VARCHAR(255) | nullable | Tax treatment code controlling cost basis method and 1099 classification |
business_date | DATE | nullable | The Schwab business date this record corresponds to — used to snapshot daily files |
c_b | BOOLEAN | nullable | Cost basis available flag — True if cost basis is tracked for this lot |
settle_date | DATE | nullable | Date: settle date |
acquired_date | DATE | nullable | Date this specific tax lot was acquired |
transaction_cost | NUMERIC | nullable | Per-lot transaction cost (commissions allocated to this lot) |
versmrkr_4 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
__master_account | VARCHAR(255) | nullable | Internal Redwood master account identifier used to partition data across clients |
imported_at | TIMESTAMP WITHOUT TIME ZONE | nullable | Timestamp when this row was first inserted into the database by the sync pipeline |
last_sync_at | TIMESTAMP WITHOUT TIME ZONE | nullable | Timestamp of the most recent sync run that touched this row (useful for detecting stale data) |
schwab_rty
75 columns
- Unrealized gain/loss by tax lot — long-term holdings (held more than 1 year).
- Same structure as schwab_rtu scoped to positions qualifying for long-term capital gains tax rates.
- Fields include acquisition date, adjusted cost basis, current value, unrealized G/L.
- Used alongside schwab_rtu for complete open-lot tax position reporting.
- 75 columns.
| Column | Type | Description | |
|---|---|---|---|
adjusted_gainloss_dollars | NUMERIC | nullable | Gain or loss amount in USD |
original_face | NUMERIC | nullable | Original face value for bond or mortgage-backed security lots |
w_s | BOOLEAN | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
mstracct_number | VARCHAR(255) | nullable | Schwab master account number (custodian-level umbrella account) |
trans_code | VARCHAR(255) | nullable | Short code or category identifier |
security_type | VARCHAR(255) | nullable | Security type classification (Common Stock, Corporate Bond, Government Bond, Option…) |
federal_tax_withholding | NUMERIC | nullable | |
wh_st | VARCHAR(255) | nullable | |
c_f | BOOLEAN | nullable | Cost basis flag for special tax situations (e.g. inherited, gifted) |
c_i | VARCHAR(255) | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
custdian_id | VARCHAR(255) | nullable | Custodian institution code identifying the holding institution |
g_f | BOOLEAN | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
underlying_ticker_symbol | VARCHAR(255) | nullable | Ticker of the underlying security (for derivatives and structured products) |
underly_sedol | VARCHAR(255) | nullable | SEDOL of the underlying security |
underlying_itm_iss_id | VARCHAR(255) | nullable | Schwab item/issue ID of the underlying security |
trancode_expanded | VARCHAR(255) | nullable | |
proceeds | NUMERIC | nullable | |
publication_timestamp | VARCHAR(255) | nullable | Timestamp: publication timestamp |
adjusted_cost_per_share | NUMERIC | nullable | Average cost per share after amortization adjustments |
versmrkr_3 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
h2_h3 | VARCHAR(255) | nullable | |
underlying_isin | VARCHAR(255) | nullable | ISIN of the underlying security |
item_issue_id | VARCHAR(255) | nullable | Schwab item/issue identifier — unique across all security types including options |
prodcatg_code | VARCHAR(255) | nullable | Product category code for transaction classification |
versmrkr_5 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
c_t | VARCHAR(255) | nullable | Cost basis calculation type code |
adjusted_gainloss_percentage | NUMERIC | nullable | Gain or loss amount in USD |
p_f | BOOLEAN | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
adjusted_cost_basis_amortized | NUMERIC | nullable | Cost basis adjusted for premium/discount amortization (for bonds) |
options_display_symbol | VARCHAR(255) | nullable | Formatted display symbol for options (e.g. AAPL 240119C00180000) |
a_t | BOOLEAN | nullable | Adjusted cost basis flag — True if basis has been adjusted |
underlyng_cusip | VARCHAR(255) | nullable | CUSIP of the underlying security (Schwab spelling variant) |
closing_date | DATE | nullable | Date: closing date |
isin | VARCHAR(255) | nullable | ISIN — 12-character International Securities Identification Number (ISO 6166) |
disallowed_loss_amount | NUMERIC | nullable | Loss disallowed under IRS wash-sale rules (must be added back to basis) |
days_held | VARCHAR(255) | nullable | Number of calendar days the lot has been held (determines short vs long-term) |
l_s | VARCHAR(255) | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
gainloss_percentage | NUMERIC | nullable | Gain or loss amount in USD |
gainloss_dollars | NUMERIC | nullable | Gain or loss amount in USD |
prod_code | VARCHAR(255) | nullable | Product type code — EQ=Equity, MF=Mutual Fund, OPT=Option, FI=Fixed Income, ETF=ETF |
closing_quantity | NUMERIC | nullable | Share or unit quantity |
underly_schwab | VARCHAR(255) | nullable | Schwab internal number of the underlying security |
master_account_name | VARCHAR(255) | nullable | Human-readable display name of the master/custodian account |
c_m | VARCHAR(255) | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
proceeds_per_share | NUMERIC | nullable | |
sedol | VARCHAR(255) | nullable | SEDOL — 7-character London Stock Exchange identifier used in UK/international markets |
transaction_gainloss_pct | NUMERIC | nullable | Gain or loss amount in USD |
cusip | VARCHAR(255) | nullable | CUSIP — 9-character North American security identifier issued by DTCC |
filename | VARCHAR(255) | nullable | Source filename on Azure Hot Storage or Fidelity SFTP that produced this row |
ytd_amortization | NUMERIC | nullable | |
close_event_id | VARCHAR(255) | nullable | Identifier / foreign key linking to the close event entity |
transaction_cost_per_share | NUMERIC | nullable | Transaction cost per share for this lot |
cost_basis_unamortized | NUMERIC | nullable | Raw cost basis before amortization of bond premium or discount |
ticker_symbol | VARCHAR(255) | nullable | Exchange ticker symbol (e.g. AAPL, MSFT) — may be blank for bonds and OTC securities |
versmrkr_2 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
cost_per_share | NUMERIC | nullable | Average cost per share — unamortized (used for equity lot tracking) |
schwab_sec_nbr | VARCHAR(255) | nullable | Schwab's internal proprietary security number for their master file |
account_id | VARCHAR(255) | nullable | Schwab individual account identifier within the master account |
h_t | VARCHAR(255) | nullable | Holding period type: S=Short-term, L=Long-term |
state_tax_withholding | NUMERIC | nullable | State tax withheld on this transaction |
versmrkr_1 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
adj_disallowed_loss_amount | NUMERIC | nullable | Monetary amount in USD |
lot_selct | VARCHAR(255) | nullable | |
cbm | VARCHAR(255) | nullable | |
transaction_gainloss_dollars | NUMERIC | nullable | Gain or loss amount in USD |
tax_code | VARCHAR(255) | nullable | Tax treatment code controlling cost basis method and 1099 classification |
business_date | DATE | nullable | The Schwab business date this record corresponds to — used to snapshot daily files |
c_b | BOOLEAN | nullable | Cost basis available flag — True if cost basis is tracked for this lot |
settle_date | DATE | nullable | Date: settle date |
acquired_date | DATE | nullable | Date this specific tax lot was acquired |
transaction_cost | NUMERIC | nullable | Per-lot transaction cost (commissions allocated to this lot) |
versmrkr_4 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
__master_account | VARCHAR(255) | nullable | Internal Redwood master account identifier used to partition data across clients |
imported_at | TIMESTAMP WITHOUT TIME ZONE | nullable | Timestamp when this row was first inserted into the database by the sync pipeline |
last_sync_at | TIMESTAMP WITHOUT TIME ZONE | nullable | Timestamp of the most recent sync run that touched this row (useful for detecting stale data) |
schwab_sec
74 columns
- Security master reference — one row per security per business date.
- Pricing fields: closing price, factored price, unfactored price, price factor.
- Bond attributes: coupon rate, maturity date, call date/price, par value, TIPS inflation factor, S&P rating, Moody's rating.
- Options fields: strike price, expiration date, put/call indicator, contract multiplier.
- Full identifier cross-reference: CUSIP, ISIN, SEDOL, Schwab internal security number, ticker, industry ticker.
- Authoritative security reference for joining positions and transactions to security attributes.
| Column | Type | Description | |
|---|---|---|---|
o_i | BOOLEAN | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
security_description_line_3 | VARCHAR(255) | nullable | Text description |
unrul_sufid | VARCHAR(255) | nullable | |
dividendinterest_frequency | VARCHAR(255) | nullable | |
closing_price_unfactored | NUMERIC | nullable | Raw closing price before any adjustment factors are applied |
scrtydes_line_4 | VARCHAR(255) | nullable | |
mstracct_number | VARCHAR(255) | nullable | Schwab master account number (custodian-level umbrella account) |
underlying_industry_ticker_symbol | VARCHAR(255) | nullable | Industry-level ticker for the underlying (e.g. sector ETF) |
security_description_line_1 | VARCHAR(255) | nullable | Text description |
issue_date | DATE | nullable | Date the security was originally issued |
custdian_id | VARCHAR(255) | nullable | Custodian institution code identifying the holding institution |
underlying_ticker_symbol | VARCHAR(255) | nullable | Ticker of the underlying security (for derivatives and structured products) |
opt_expr_date | DATE | nullable | Option contract expiration date |
underly_sedol | VARCHAR(255) | nullable | SEDOL of the underlying security |
sp_rating | VARCHAR(255) | nullable | Standard & Poor's credit rating (AAA, AA+, AA, … D) |
next_put_date | DATE | nullable | Next put date — when bondholder can force early redemption |
underlying_itm_iss_id | VARCHAR(255) | nullable | Schwab item/issue ID of the underlying security |
rulst_sufid | VARCHAR(255) | nullable | |
last_update | DATE | nullable | |
versmrkr_3 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
call_price | NUMERIC | nullable | Price at which the bond may be called by the issuer |
h2_h3 | VARCHAR(255) | nullable | |
underlying_isin | VARCHAR(255) | nullable | ISIN of the underlying security |
item_issue_id | VARCHAR(255) | nullable | Schwab item/issue identifier — unique across all security types including options |
factor_date | DATE | nullable | Date the pool factor was applied for MBS/ABS securities |
par_value | NUMERIC | nullable | Bond par/face value (typically 1000 for corporate bonds) |
prodcatg_code | VARCHAR(255) | nullable | Product category code for transaction classification |
mkt_code | VARCHAR(255) | nullable | Short code or category identifier |
options_display_symbol | VARCHAR(255) | nullable | Formatted display symbol for options (e.g. AAPL 240119C00180000) |
next_put_price | NUMERIC | nullable | Price at which the bondholder can put the bond back to the issuer |
underlyng_cusip | VARCHAR(255) | nullable | CUSIP of the underlying security (Schwab spelling variant) |
options_multiplier | NUMERIC | nullable | Options contract multiplier — typically 100 shares per contract |
moodys_rating | VARCHAR(255) | nullable | Moody's credit rating (Aaa, Aa1, Aa2, … C) |
isin | VARCHAR(255) | nullable | ISIN — 12-character International Securities Identification Number (ISO 6166) |
closing_price | NUMERIC | nullable | End-of-day closing price adjusted for splits and dividends |
tips_factor | NUMERIC | nullable | TIPS inflation adjustment factor applied to principal |
industry_ticker_symbol | VARCHAR(255) | nullable | Industry classification ticker assigned by Schwab |
st_cd | VARCHAR(255) | nullable | State code (US state abbreviation) |
optnrt_symbol | VARCHAR(255) | nullable | Options root symbol (underlying ticker) |
c_p | VARCHAR(255) | nullable | Call/Put indicator: C=Call, P=Put |
ly_st | VARCHAR(255) | nullable | |
prod_code | VARCHAR(255) | nullable | Product type code — EQ=Equity, MF=Mutual Fund, OPT=Option, FI=Fixed Income, ETF=ETF |
security_description_line_2 | VARCHAR(255) | nullable | Text description |
underly_schwab | VARCHAR(255) | nullable | Schwab internal number of the underlying security |
master_account_name | VARCHAR(255) | nullable | Human-readable display name of the master/custodian account |
p_i | BOOLEAN | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
mnymk_code | VARCHAR(255) | nullable | Money market fund classification code |
sedol | VARCHAR(255) | nullable | SEDOL — 7-character London Stock Exchange identifier used in UK/international markets |
cusip | VARCHAR(255) | nullable | CUSIP — 9-character North American security identifier issued by DTCC |
call_date | DATE | nullable | Next date on which the issuer may redeem the bond early |
maturity_date | DATE | nullable | Date the bond principal is due to be repaid |
filename | VARCHAR(255) | nullable | Source filename on Azure Hot Storage or Fidelity SFTP that produced this row |
interest_rate | NUMERIC | nullable | Coupon or stated interest rate as a percentage |
security_valuation_unit | NUMERIC | nullable | Valuation unit (e.g. percentage of par for bonds vs per-share for equities) |
ticker_symbol | VARCHAR(255) | nullable | Exchange ticker symbol (e.g. AAPL, MSFT) — may be blank for bonds and OTC securities |
is_cy | VARCHAR(255) | nullable | Boolean flag (True/False) |
asset_backed_factor | NUMERIC | nullable | Pool factor for asset-backed securities |
versmrkr_2 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
face_value_amt | NUMERIC | nullable | Face value amount for fixed income or structured securities |
strike_price | NUMERIC | nullable | Option strike (exercise) price |
cy_is | VARCHAR(255) | nullable | |
schwab_sec_nbr | VARCHAR(255) | nullable | Schwab's internal proprietary security number for their master file |
versmrkr_1 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
reorg_sec_nbr | VARCHAR(255) | nullable | Numeric identifier or reference number |
next_pay_date | DATE | nullable | Next scheduled interest payment date |
factor | NUMERIC | nullable | Pool factor — remaining principal as a percentage of original face (for MBS/ABS) |
tax_code | VARCHAR(255) | nullable | Tax treatment code controlling cost basis method and 1099 classification |
business_date | DATE | nullable | The Schwab business date this record corresponds to — used to snapshot daily files |
s_f | BOOLEAN | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
secprice_lstupdte | DATE | nullable | Price per unit in USD |
1st_cpn_date | DATE | nullable | Date: 1st cpn date |
__master_account | VARCHAR(255) | nullable | Internal Redwood master account identifier used to partition data across clients |
imported_at | TIMESTAMP WITHOUT TIME ZONE | nullable | Timestamp when this row was first inserted into the database by the sync pipeline |
last_sync_at | TIMESTAMP WITHOUT TIME ZONE | nullable | Timestamp of the most recent sync run that touched this row (useful for detecting stale data) |
schwab_tcf
199 columns
- Tax cost factor / trade confirmation file — parsed from Schwab's fixed-width COBOL format.
- Contains named financial fields: price, principal, commission, fees, accrued interest for each trade confirmation.
- The
message_1 … message_150columns are raw COBOL message segments carrying confirmation text, regulatory disclosures, and trade narrative. - 199 columns — the largest single Schwab table in terms of column count.
- Used for trade confirmation archival and regulatory audit; also supports FINRA/SEC audit-trail requirements.
| Column | Type | Description | |
|---|---|---|---|
message_45 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_38 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_33 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
price | NUMERIC | nullable | Security price per unit as of the as_of_date |
message_143 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
trd_date | DATE | nullable | Trade date from confirmation |
message_124 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_105 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_40 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
quantity | NUMERIC | nullable | Share or unit quantity for this transaction |
message_22 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_3 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_49 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_37 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
ofdesc | VARCHAR(255) | nullable | Order fill description from the trade confirmation |
message_10 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
account_nameaddress_5 | VARCHAR(255) | nullable | Account name/address line 5 in TCF |
message_63 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_19 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_12 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
unused_field_4 | VARCHAR(255) | nullable | Reserved/unused field in TCF COBOL layout |
security_description | VARCHAR(255) | nullable | Security description text for display |
message_139 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_116 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_86 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_72 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_54 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_4 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_30 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_144 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
master | VARCHAR(255) | nullable | Master account reference in TCF file |
account_nameaddress_1 | VARCHAR(255) | nullable | Account name/address line 1 in TCF |
message_59 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_50 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_79 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_140 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_131 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
step_in_fee | NUMERIC | nullable | Step-in fee for block trades or securities lending |
message_81 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_70 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_85 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_73 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_47 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
settl_dt | DATE | nullable | Settlement date from trade confirmation |
h2 | VARCHAR(255) | nullable | COBOL H2 record marker |
message_121 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
trace_symbol | VARCHAR(255) | nullable | TRACE reporting symbol for fixed income transaction reporting |
message_26 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
order_hndl_fee | NUMERIC | nullable | Order handling fee |
message_150 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_119 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
principal | NUMERIC | nullable | Principal (face value) amount of the transaction |
message_138 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_123 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_130 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_69 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_27 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_78 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_56 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_136 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_60 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_31 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
action | VARCHAR(255) | nullable | Action code: B=Buy, S=Sell, SS=Short Sell, BC=Buy to Cover |
message_24 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_21 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_43 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_100 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_15 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_135 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_51 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
prime_brok_fee | NUMERIC | nullable | Prime broker fee (TCF version) |
message_125 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_122 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_95 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_1 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_142 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_141 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_66 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_83 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
account_title_3 | VARCHAR(255) | nullable | Third line of account title in TCF |
message_94 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
msgbreak | VARCHAR(255) | nullable | Message break indicator — separates logical records within COBOL file |
accrued_int | NUMERIC | nullable | Accrued interest on bond trade |
message_29 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_5 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_62 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_107 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_61 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_87 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
__master_account | VARCHAR(255) | nullable | Internal Redwood master account identifier used to partition data across clients |
account | VARCHAR(255) | nullable | Account reference in TCF/position file |
message_71 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_134 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_92 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_103 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_25 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_96 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_91 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
exch_proc_fee | NUMERIC | nullable | Exchange processing fee (TCF version) |
message_109 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
account_nameaddress_4 | VARCHAR(255) | nullable | Account name/address line 4 in TCF |
message_23 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_18 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_146 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_147 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_82 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
state_tax | NUMERIC | nullable | State tax amount |
account_type | VARCHAR(255) | nullable | Account type: Individual Taxable, Traditional IRA, Roth IRA, Trust, Corporate, Joint |
message_74 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_64 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_67 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_11 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
markupmarkdown | NUMERIC | nullable | Markup/markdown for fixed income principal transactions |
message_8 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_84 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
account_nameaddress_6 | VARCHAR(255) | nullable | Account name/address line 6 in TCF |
message_97 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_99 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_145 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
account_title_2 | VARCHAR(255) | nullable | Second line of account title in TCF |
message_48 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
filename | VARCHAR(255) | nullable | Source filename on Azure Hot Storage or Fidelity SFTP that produced this row |
symbol | VARCHAR(255) | nullable | Security symbol from the data source — may be ticker, CUSIP, or options symbol |
message_58 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_53 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_13 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_113 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_16 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
bus_date | DATE | nullable | Business date of the trade confirmation |
txdesc | VARCHAR(255) | nullable | Transaction description text |
message_52 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_46 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_120 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_2 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
account_nameaddress_2 | VARCHAR(255) | nullable | Account name/address line 2 in TCF |
message_110 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_76 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_36 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_127 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_93 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_112 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_77 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
cusip | VARCHAR(255) | nullable | CUSIP — 9-character North American security identifier issued by DTCC |
message_104 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_98 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
account_nameaddress_3 | VARCHAR(255) | nullable | Account name/address line 3 in TCF |
message_55 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_34 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_137 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_44 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_115 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_149 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_65 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_41 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_20 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_17 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_14 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_7 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_129 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_102 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_101 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_6 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
unused_field_5 | VARCHAR(255) | nullable | Reserved/unused field in TCF COBOL layout |
message_108 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
transact_fee | NUMERIC | nullable | Transaction fee amount |
message_28 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_32 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_75 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_35 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_133 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
sec_id | VARCHAR(255) | nullable | Security ID used in the trade confirmation |
message_111 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_68 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_88 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_9 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_118 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
other_fees | NUMERIC | nullable | |
cancel | VARCHAR(255) | nullable | Cancellation indicator — Y if this record cancels a previous confirmation |
message_42 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_148 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_114 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_57 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_132 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
total_amount | NUMERIC | nullable | Total transaction amount including all fees and commissions |
message_126 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
account_title_1 | VARCHAR(255) | nullable | First line of account title in TCF |
message_80 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
broker_svc_fee | NUMERIC | nullable | Broker service fee (TCF version) |
schwab_comm | NUMERIC | nullable | Schwab commission amount |
message_90 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_89 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_106 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
research_fee | NUMERIC | nullable | Research/regulatory fee charged on the transaction |
message_128 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
message_39 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
exec_brok_comm | NUMERIC | nullable | Executing broker commission (TCF version) |
message_117 | VARCHAR(255) | nullable | Raw COBOL message segment from trade confirmation file — carries confirmation text or regulatory disclosure |
imported_at | TIMESTAMP WITHOUT TIME ZONE | nullable | Timestamp when this row was first inserted into the database by the sync pipeline |
last_sync_at | TIMESTAMP WITHOUT TIME ZONE | nullable | Timestamp of the most recent sync run that touched this row (useful for detecting stale data) |
schwab_trn
142 columns
- All transaction activity across Schwab accounts: buys, sells, dividends, interest, transfers, journal entries, and corporate actions.
- Key financial fields: price, quantity, net/gross amount, accrued interest, commissions, and multi-tiered fees (broker service, prime broker, exchange processing, research, trade-away).
- Securities cross-referenced by CUSIP, ISIN, SEDOL, and ticker.
- Settlement date and trade date tracked separately for T+1/T+2 reconciliation.
- Includes retirement-specific fields: federal and state income tax withholding on retirement distributions.
- 142 columns total — the most complete transaction record in the Schwab dataset.
| Column | Type | Description | |
|---|---|---|---|
api_json | VARCHAR(255) | nullable | |
research_fee | NUMERIC | nullable | Research/regulatory fee charged on the transaction |
dividendinterest_share_quantity | NUMERIC | nullable | Shares issued or redeemed in stock dividend transactions |
exdivdnd_date | DATE | nullable | Ex-dividend date — first date when buying does not entitle holder to dividend |
record_date | DATE | nullable | Record date determining which shareholders receive a dividend |
unrul_sufid | BIGINT | nullable | |
broker_service_fee | NUMERIC | nullable | Broker service fee (clearing or custody-related) |
closing_price_unfactored | NUMERIC | nullable | Raw closing price before any adjustment factors are applied |
bank_aba | VARCHAR(255) | nullable | |
mstracct_number | VARCHAR(255) | nullable | Schwab master account number (custodian-level umbrella account) |
publication_time_stamp | VARCHAR(255) | nullable | |
account_title_line_2 | VARCHAR(255) | nullable | Second line of the account title |
sr_sy | VARCHAR(255) | nullable | |
prime_broker_fee | NUMERIC | nullable | Prime brokerage fee for institutional accounts |
order_id | VARCHAR(255) | nullable | Unique order identifier linking related execution fills |
underlying_industry_ticker_symbol | VARCHAR(255) | nullable | Industry-level ticker for the underlying (e.g. sector ETF) |
s_i | BOOLEAN | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
transaction_check_memo_1 | VARCHAR(255) | nullable | |
trade_date | DATE | nullable | Date the trade was executed in the market |
debit_credit | VARCHAR(255) | nullable | D=Debit (cash leaves account), C=Credit (cash enters account) |
settlmnt_date | DATE | nullable | Settlement date when cash and securities change hands (T+1 for equities, T+2 for bonds) |
quantity | NUMERIC | nullable | Share or unit quantity for this transaction |
accounting_rule_code | VARCHAR(255) | nullable | GAAP accounting rule applied to this transaction |
acct_type | VARCHAR(255) | nullable | Account type code: 1=Individual, 2=Joint, 3=IRA, 4=Trust, 5=Corporate |
custdian_id | VARCHAR(255) | nullable | Custodian institution code identifying the holding institution |
underlying_ticker_symbol | VARCHAR(255) | nullable | Ticker of the underlying security (for derivatives and structured products) |
d_m | VARCHAR(255) | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
transaction_source_code_description | VARCHAR(255) | nullable | Description of the originating system or source of the transaction |
tran_srcde | VARCHAR(255) | nullable | Source code identifying the transaction origin system |
broker_name | VARCHAR(255) | nullable | Executing broker name for away-market trades |
price | NUMERIC | nullable | Security price per unit as of the as_of_date |
underly_sedol | VARCHAR(255) | nullable | SEDOL of the underlying security |
underlying_itm_iss_id | VARCHAR(255) | nullable | Schwab item/issue ID of the underlying security |
recipient_maker_name_line_3 | VARCHAR(255) | nullable | |
rulst_sufid | VARCHAR(255) | nullable | |
accstart_date | DATE | nullable | Accrued interest start date |
rt_tx | VARCHAR(255) | nullable | |
versmrkr_3 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
transaction_memo | VARCHAR(255) | nullable | Memo, notes, or wire instructions attached to the transaction |
h2_h3 | VARCHAR(255) | nullable | |
underlying_isin | VARCHAR(255) | nullable | ISIN of the underlying security |
t_c | BOOLEAN | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
item_issue_id | VARCHAR(255) | nullable | Schwab item/issue identifier — unique across all security types including options |
factor_date | DATE | nullable | Date the pool factor was applied for MBS/ABS securities |
federal_bank_reference_number | VARCHAR(255) | nullable | Federal bank/wire reference number for cash transfers |
step_in_fee | NUMERIC | nullable | Step-in fee for block trades or securities lending |
st_tx | VARCHAR(255) | nullable | |
recipient_maker_acct_number | VARCHAR(255) | nullable | Numeric identifier or reference number |
versmrkr_5 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
prodcatg_code | VARCHAR(255) | nullable | Product category code for transaction classification |
sb_cd | VARCHAR(255) | nullable | Buy/Sell indicator code |
cash_in_lieu_share_quantity | NUMERIC | nullable | Fractional share quantity settled as cash in lieu |
trade_order_exectn_tm_stmp | VARCHAR(255) | nullable | Timestamp of order execution |
bank_act_typ | VARCHAR(255) | nullable | |
options_display_symbol | VARCHAR(255) | nullable | Formatted display symbol for options (e.g. AAPL 240119C00180000) |
retirement_fed_income_tax | NUMERIC | nullable | Federal income tax withheld on retirement distributions |
transaction_detail_description | VARCHAR(255) | nullable | Human-readable description of the transaction |
underlyng_cusip | VARCHAR(255) | nullable | CUSIP of the underlying security (Schwab spelling variant) |
broker_code | VARCHAR(255) | nullable | Executing broker's identifier code |
isin | VARCHAR(255) | nullable | ISIN — 12-character International Securities Identification Number (ISO 6166) |
trade_order_entry_tm_stmp | VARCHAR(255) | nullable | Timestamp of order entry |
recipient_maker_name_line_2 | VARCHAR(255) | nullable | |
commission | NUMERIC | nullable | Brokerage commission charged for this transaction |
closing_price | NUMERIC | nullable | End-of-day closing price adjusted for splits and dividends |
tips_factor | NUMERIC | nullable | TIPS inflation adjustment factor applied to principal |
acct_regis | VARCHAR(255) | nullable | Account registration code defining ownership structure |
recipient_maker_name_line_1 | VARCHAR(255) | nullable | |
cash_in_lieu_rate | NUMERIC | nullable | Cash-in-lieu rate when fractional shares cannot be issued |
transaction_check_memo_2 | VARCHAR(255) | nullable | |
schwab_cashiering_unique_id | VARCHAR(255) | nullable | Schwab's internal ID for cashiering events |
tr_cd | VARCHAR(255) | nullable | Transaction type code (internal Schwab coding scheme) |
industry_ticker_symbol | VARCHAR(255) | nullable | Industry classification ticker assigned by Schwab |
exchange_processing_fee | NUMERIC | nullable | Exchange or regulatory transaction fee (SEC fee, ORF) |
swb_from_account | VARCHAR(255) | nullable | Source account for internal Schwab account-to-account transfers |
ly_st | VARCHAR(255) | nullable | |
prod_code | VARCHAR(255) | nullable | Product type code — EQ=Equity, MF=Mutual Fund, OPT=Option, FI=Fixed Income, ETF=ETF |
transaction_category | VARCHAR(255) | nullable | High-level category: Trade, Income, Transfer, Fee, Tax, Corporate Action |
underly_schwab | VARCHAR(255) | nullable | Schwab internal number of the underlying security |
net_amount | NUMERIC | nullable | Net cash amount after all fees, commissions, and taxes |
federal_tefra_withholding | NUMERIC | nullable | TEFRA backup withholding amount |
master_account_name | VARCHAR(255) | nullable | Human-readable display name of the master/custodian account |
jn_tp | VARCHAR(255) | nullable | Journal type code for internal accounting entries |
mnymk_code | VARCHAR(255) | nullable | Money market fund classification code |
stock_exchg | VARCHAR(255) | nullable | Exchange where the trade was executed |
redemption_fee | NUMERIC | nullable | Mutual fund redemption fee for early redemption |
order_number | VARCHAR(255) | nullable | Order number from the trading system |
sedol | VARCHAR(255) | nullable | SEDOL — 7-character London Stock Exchange identifier used in UK/international markets |
cusip | VARCHAR(255) | nullable | CUSIP — 9-character North American security identifier issued by DTCC |
accrued_interest | NUMERIC | nullable | Accrued interest on fixed income positions |
filename | VARCHAR(255) | nullable | Source filename on Azure Hot Storage or Fidelity SFTP that produced this row |
ordr_srcd | VARCHAR(255) | nullable | Order source code (e.g. online, phone, advisor) |
other_fee | NUMERIC | nullable | Miscellaneous other fees |
account_title_line_3 | VARCHAR(255) | nullable | Third line of the account title |
accrend_date | DATE | nullable | Accrued interest end date |
payable_date | DATE | nullable | Payable/payment date for dividends and interest distributions |
distribution_rate | NUMERIC | nullable | Per-share distribution rate for dividends and capital gain distributions |
exec_brok_comm | NUMERIC | nullable | Executing broker commission (TCF version) |
ticker_symbol | VARCHAR(255) | nullable | Exchange ticker symbol (e.g. AAPL, MSFT) — may be blank for bonds and OTC securities |
asset_backed_factor | NUMERIC | nullable | Pool factor for asset-backed securities |
versmrkr_2 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
markupmarkdown | NUMERIC | nullable | Markup/markdown for fixed income principal transactions |
bank_name_part_1 | VARCHAR(255) | nullable | |
disburse_check_number | VARCHAR(255) | nullable | Numeric identifier or reference number |
trade_away_fee | NUMERIC | nullable | Fee charged when a trade executes away from the primary broker |
s1_check_number | VARCHAR(255) | nullable | Numeric identifier or reference number |
swb_to_account | VARCHAR(255) | nullable | Destination account for internal transfers |
schwab_sec_nbr | VARCHAR(255) | nullable | Schwab's internal proprietary security number for their master file |
trade_typcd | VARCHAR(255) | nullable | Trade type code distinguishing buy, sell, short, cover, exercise |
account_id | VARCHAR(255) | nullable | Schwab individual account identifier within the master account |
i_e | BOOLEAN | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
retirement_state_income_tax | NUMERIC | nullable | State income tax withheld on retirement distributions |
state_tax_withholding | NUMERIC | nullable | State tax withheld on this transaction |
versmrkr_1 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
bank_name_part_2 | VARCHAR(255) | nullable | |
d_f | BOOLEAN | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
factor | NUMERIC | nullable | Pool factor — remaining principal as a percentage of original face (for MBS/ABS) |
account_title_line_1 | VARCHAR(255) | nullable | First line of the formal account title (legal name) |
tran_date | DATE | nullable | Transaction date — may differ from trade date for corporate actions and adjustments |
tax_code | VARCHAR(255) | nullable | Tax treatment code controlling cost basis method and 1099 classification |
business_date | DATE | nullable | The Schwab business date this record corresponds to — used to snapshot daily files |
frequency | VARCHAR(255) | nullable | Payment frequency code (M=Monthly, Q=Quarterly, S=Semi-annual, A=Annual) |
gross_amount | NUMERIC | nullable | Gross cash amount before fees and commissions |
versmrkr_4 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
intermediary_name | VARCHAR(255) | nullable | |
record_id | UUID | required | Unique UUID assigned to this row for deduplication and cross-table joining |
__master_account | VARCHAR(255) | nullable | Internal Redwood master account identifier used to partition data across clients |
versmrkr_6 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
yield_to_call | NUMERIC | nullable | Yield to call date for callable bond transactions |
yield_to_maturity | NUMERIC | nullable | Yield to maturity for fixed income transactions |
trust_princ | NUMERIC | nullable | Trust principal amount (for trust account transactions) |
trust_income | NUMERIC | nullable | Trust income amount (for trust account transactions) |
tax_cd | VARCHAR(255) | nullable | Short code or category identifier |
r_dc | VARCHAR(255) | nullable | |
r_cc | VARCHAR(255) | nullable | |
r_cy | BIGINT | nullable | |
cycle_id | VARCHAR(255) | nullable | Identifier / foreign key linking to the cycle entity |
tran_randm | BIGINT | nullable | |
tranproc_date | DATE | nullable | Date the transaction was processed by Schwab's back office |
cashiering_transaction_identifier | VARCHAR(255) | nullable | Unique identifier for cashiering (wire/ACH/check) transactions |
transaction_memo_or_wire_instructions | VARCHAR(255) | nullable | |
imported_at | TIMESTAMP WITHOUT TIME ZONE | nullable | Timestamp when this row was first inserted into the database by the sync pipeline |
last_sync_at | TIMESTAMP WITHOUT TIME ZONE | nullable | Timestamp of the most recent sync run that touched this row (useful for detecting stale data) |
schwab_uln
60 columns
- Unrealized position detail at the individual tax-lot level — the most granular Schwab lot table.
- Tracks each lot's acquisition date, original purchase price, unamortized and amortized/adjusted cost basis, cost per share.
- Records unrealized gain/loss, days held, wash-sale disallowance flags, transaction cost, lot-selection method (FIFO/specific ID).
- 60 columns — primary source for cost-basis accounting and tax-lot optimisation.
- Feeds lot-level inputs into Schedule D and tax-efficiency rebalancing workflows.
| Column | Type | Description | |
|---|---|---|---|
filename | VARCHAR(255) | nullable | Source filename on Azure Hot Storage or Fidelity SFTP that produced this row |
__master_account | VARCHAR(255) | nullable | Internal Redwood master account identifier used to partition data across clients |
custdian_id | VARCHAR(255) | nullable | Custodian institution code identifying the holding institution |
mstracct_number | VARCHAR(255) | nullable | Schwab master account number (custodian-level umbrella account) |
master_account_name | VARCHAR(255) | nullable | Human-readable display name of the master/custodian account |
business_date | DATE | nullable | The Schwab business date this record corresponds to — used to snapshot daily files |
account_id | VARCHAR(255) | nullable | Schwab individual account identifier within the master account |
prod_code | VARCHAR(255) | nullable | Product type code — EQ=Equity, MF=Mutual Fund, OPT=Option, FI=Fixed Income, ETF=ETF |
prodcatg_code | VARCHAR(255) | nullable | Product category code for transaction classification |
tax_code | VARCHAR(255) | nullable | Tax treatment code controlling cost basis method and 1099 classification |
ticker_symbol | VARCHAR(255) | nullable | Exchange ticker symbol (e.g. AAPL, MSFT) — may be blank for bonds and OTC securities |
cusip | VARCHAR(255) | nullable | CUSIP — 9-character North American security identifier issued by DTCC |
schwab_sec_nbr | VARCHAR(255) | nullable | Schwab's internal proprietary security number for their master file |
item_issue_id | VARCHAR(255) | nullable | Schwab item/issue identifier — unique across all security types including options |
isin | VARCHAR(255) | nullable | ISIN — 12-character International Securities Identification Number (ISO 6166) |
sedol | VARCHAR(255) | nullable | SEDOL — 7-character London Stock Exchange identifier used in UK/international markets |
options_display_symbol | VARCHAR(255) | nullable | Formatted display symbol for options (e.g. AAPL 240119C00180000) |
underlying_ticker_symbol | VARCHAR(255) | nullable | Ticker of the underlying security (for derivatives and structured products) |
underlyng_cusip | VARCHAR(255) | nullable | CUSIP of the underlying security (Schwab spelling variant) |
underly_schwab | VARCHAR(255) | nullable | Schwab internal number of the underlying security |
underlying_itm_iss_id | VARCHAR(255) | nullable | Schwab item/issue ID of the underlying security |
underlying_isin | VARCHAR(255) | nullable | ISIN of the underlying security |
underly_sedol | VARCHAR(255) | nullable | SEDOL of the underlying security |
current_quantity | NUMERIC | nullable | Total current share quantity held across all settlement types |
l_s | VARCHAR(255) | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
trans_code | VARCHAR(255) | nullable | Short code or category identifier |
current_market_value | NUMERIC | nullable | Current market value of the position in USD |
accrued_interest | NUMERIC | nullable | Accrued interest on fixed income positions |
acquired_date | DATE | nullable | Date this specific tax lot was acquired |
orgpurch_date | DATE | nullable | Original purchase date — may differ from acquired_date for gifted or transferred lots |
org_purchase_price | NUMERIC | nullable | Original purchase price per share for the lot |
yield_tomaturity | NUMERIC | nullable | |
cost_basis_unamortized | NUMERIC | nullable | Raw cost basis before amortization of bond premium or discount |
cost_per_share | NUMERIC | nullable | Average cost per share — unamortized (used for equity lot tracking) |
adjusted_cost_basis_amortized | NUMERIC | nullable | Cost basis adjusted for premium/discount amortization (for bonds) |
adjusted_cost_per_share | NUMERIC | nullable | Average cost per share after amortization adjustments |
unrealized_gainloss | NUMERIC | nullable | Unrealised gain or loss — positive = gain, negative = loss |
days_held | VARCHAR(255) | nullable | Number of calendar days the lot has been held (determines short vs long-term) |
h_t | VARCHAR(255) | nullable | Holding period type: S=Short-term, L=Long-term |
c_b | BOOLEAN | nullable | Cost basis available flag — True if cost basis is tracked for this lot |
c_t | VARCHAR(255) | nullable | Cost basis calculation type code |
a_t | BOOLEAN | nullable | Adjusted cost basis flag — True if basis has been adjusted |
c_f | BOOLEAN | nullable | Cost basis flag for special tax situations (e.g. inherited, gifted) |
original_face | NUMERIC | nullable | Original face value for bond or mortgage-backed security lots |
dflot_selct | VARCHAR(255) | nullable | Default lot selection method override for this specific lot |
w_s | BOOLEAN | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
versmrkr_1 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
disallowed_loss_amount | NUMERIC | nullable | Loss disallowed under IRS wash-sale rules (must be added back to basis) |
transaction_cost | NUMERIC | nullable | Per-lot transaction cost (commissions allocated to this lot) |
transaction_cost_per_share | NUMERIC | nullable | Transaction cost per share for this lot |
versmrkr_2 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
g_i | VARCHAR(255) | nullable | Gain/loss indicator code |
original_cost_basis | NUMERIC | nullable | Unadjusted original cost basis before wash-sale adjustments |
versmrkr_3 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
adjusted_cost_incldg_unpd_amort | NUMERIC | nullable | Cost basis including unamortized bond premium/discount |
versmrkr_4 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
event_id | VARCHAR(255) | nullable | Corporate action event ID that created or modified this lot |
c_v | VARCHAR(255) | nullable | Cost basis verification flag |
imported_at | TIMESTAMP WITHOUT TIME ZONE | nullable | Timestamp when this row was first inserted into the database by the sync pipeline |
last_sync_at | TIMESTAMP WITHOUT TIME ZONE | nullable | Timestamp of the most recent sync run that touched this row (useful for detecting stale data) |
schwab_ult
61 columns
Unrealized lot totals — highest-level aggregation of open-lot cost basis and unrealised gain/loss across the account, broken out by holding-period category (short-term vs long-term). Used for tax planning summaries.
| Column | Type | Description | |
|---|---|---|---|
original_face | NUMERIC | nullable | Original face value for bond or mortgage-backed security lots |
w_s | BOOLEAN | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
mstracct_number | VARCHAR(255) | nullable | Schwab master account number (custodian-level umbrella account) |
trans_code | VARCHAR(255) | nullable | Short code or category identifier |
original_cost_basis | NUMERIC | nullable | Unadjusted original cost basis before wash-sale adjustments |
c_f | BOOLEAN | nullable | Cost basis flag for special tax situations (e.g. inherited, gifted) |
custdian_id | VARCHAR(255) | nullable | Custodian institution code identifying the holding institution |
underlying_ticker_symbol | VARCHAR(255) | nullable | Ticker of the underlying security (for derivatives and structured products) |
underly_sedol | VARCHAR(255) | nullable | SEDOL of the underlying security |
underlying_itm_iss_id | VARCHAR(255) | nullable | Schwab item/issue ID of the underlying security |
adjusted_cost_per_share | NUMERIC | nullable | Average cost per share after amortization adjustments |
versmrkr_3 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
h2_h3 | VARCHAR(255) | nullable | |
underlying_isin | VARCHAR(255) | nullable | ISIN of the underlying security |
item_issue_id | VARCHAR(255) | nullable | Schwab item/issue identifier — unique across all security types including options |
prodcatg_code | VARCHAR(255) | nullable | Product category code for transaction classification |
c_t | VARCHAR(255) | nullable | Cost basis calculation type code |
g_i | VARCHAR(255) | nullable | Gain/loss indicator code |
adjusted_cost_basis_amortized | NUMERIC | nullable | Cost basis adjusted for premium/discount amortization (for bonds) |
unrealized_gainloss | NUMERIC | nullable | Unrealised gain or loss — positive = gain, negative = loss |
options_display_symbol | VARCHAR(255) | nullable | Formatted display symbol for options (e.g. AAPL 240119C00180000) |
a_t | BOOLEAN | nullable | Adjusted cost basis flag — True if basis has been adjusted |
org_purchase_price | NUMERIC | nullable | Original purchase price per share for the lot |
underlyng_cusip | VARCHAR(255) | nullable | CUSIP of the underlying security (Schwab spelling variant) |
isin | VARCHAR(255) | nullable | ISIN — 12-character International Securities Identification Number (ISO 6166) |
disallowed_loss_amount | NUMERIC | nullable | Loss disallowed under IRS wash-sale rules (must be added back to basis) |
days_held | VARCHAR(255) | nullable | Number of calendar days the lot has been held (determines short vs long-term) |
l_s | VARCHAR(255) | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
prod_code | VARCHAR(255) | nullable | Product type code — EQ=Equity, MF=Mutual Fund, OPT=Option, FI=Fixed Income, ETF=ETF |
underly_schwab | VARCHAR(255) | nullable | Schwab internal number of the underlying security |
master_account_name | VARCHAR(255) | nullable | Human-readable display name of the master/custodian account |
c_v | VARCHAR(255) | nullable | Cost basis verification flag |
sedol | VARCHAR(255) | nullable | SEDOL — 7-character London Stock Exchange identifier used in UK/international markets |
cusip | VARCHAR(255) | nullable | CUSIP — 9-character North American security identifier issued by DTCC |
accrued_interest | NUMERIC | nullable | Accrued interest on fixed income positions |
filename | VARCHAR(255) | nullable | Source filename on Azure Hot Storage or Fidelity SFTP that produced this row |
current_market_value | NUMERIC | nullable | Current market value of the position in USD |
transaction_cost_per_share | NUMERIC | nullable | Transaction cost per share for this lot |
cost_basis_unamortized | NUMERIC | nullable | Raw cost basis before amortization of bond premium or discount |
ticker_symbol | VARCHAR(255) | nullable | Exchange ticker symbol (e.g. AAPL, MSFT) — may be blank for bonds and OTC securities |
versmrkr_2 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
cost_per_share | NUMERIC | nullable | Average cost per share — unamortized (used for equity lot tracking) |
adjusted_cost_incldg_unpd_amort | NUMERIC | nullable | Cost basis including unamortized bond premium/discount |
schwab_sec_nbr | VARCHAR(255) | nullable | Schwab's internal proprietary security number for their master file |
account_id | VARCHAR(255) | nullable | Schwab individual account identifier within the master account |
h_t | VARCHAR(255) | nullable | Holding period type: S=Short-term, L=Long-term |
versmrkr_1 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
yield_tomaturity | NUMERIC | nullable | |
orgpurch_date | DATE | nullable | Original purchase date — may differ from acquired_date for gifted or transferred lots |
tax_code | VARCHAR(255) | nullable | Tax treatment code controlling cost basis method and 1099 classification |
business_date | DATE | nullable | The Schwab business date this record corresponds to — used to snapshot daily files |
dflot_selct | VARCHAR(255) | nullable | Default lot selection method override for this specific lot |
event_id | VARCHAR(255) | nullable | Corporate action event ID that created or modified this lot |
c_b | BOOLEAN | nullable | Cost basis available flag — True if cost basis is tracked for this lot |
acquired_date | DATE | nullable | Date this specific tax lot was acquired |
transaction_cost | NUMERIC | nullable | Per-lot transaction cost (commissions allocated to this lot) |
versmrkr_4 | VARCHAR(255) | nullable | COBOL version marker — internal fixed-width file format versioning field |
current_quantity | NUMERIC | nullable | Total current share quantity held across all settlement types |
__master_account | VARCHAR(255) | nullable | Internal Redwood master account identifier used to partition data across clients |
imported_at | TIMESTAMP WITHOUT TIME ZONE | nullable | Timestamp when this row was first inserted into the database by the sync pipeline |
last_sync_at | TIMESTAMP WITHOUT TIME ZONE | nullable | Timestamp of the most recent sync run that touched this row (useful for detecting stale data) |
schwab_upn
43 columns
- Unrealized position summary at the security level — aggregated across all open lots for each account.
- Provides current quantity, market value, blended cost basis and cost per share, adjusted amortised basis.
- Total unrealised gain/loss per security per account across all lot entries.
- 43 columns — used for portfolio-level P&L dashboards without needing lot-level detail.
- One step above schwab_uln in the aggregation hierarchy.
| Column | Type | Description | |
|---|---|---|---|
filename | VARCHAR(255) | nullable | Source filename on Azure Hot Storage or Fidelity SFTP that produced this row |
__master_account | VARCHAR(255) | nullable | Internal Redwood master account identifier used to partition data across clients |
custdian_id | VARCHAR(255) | nullable | Custodian institution code identifying the holding institution |
mstracct_number | VARCHAR(255) | nullable | Schwab master account number (custodian-level umbrella account) |
master_account_name | VARCHAR(255) | nullable | Human-readable display name of the master/custodian account |
business_date | DATE | nullable | The Schwab business date this record corresponds to — used to snapshot daily files |
account_id | VARCHAR(255) | nullable | Schwab individual account identifier within the master account |
security_type | VARCHAR(255) | nullable | Security type classification (Common Stock, Corporate Bond, Government Bond, Option…) |
prod_code | VARCHAR(255) | nullable | Product type code — EQ=Equity, MF=Mutual Fund, OPT=Option, FI=Fixed Income, ETF=ETF |
prodcatg_code | VARCHAR(255) | nullable | Product category code for transaction classification |
tax_code | VARCHAR(255) | nullable | Tax treatment code controlling cost basis method and 1099 classification |
ticker_symbol | VARCHAR(255) | nullable | Exchange ticker symbol (e.g. AAPL, MSFT) — may be blank for bonds and OTC securities |
cusip | VARCHAR(255) | nullable | CUSIP — 9-character North American security identifier issued by DTCC |
schwab_sec_nbr | VARCHAR(255) | nullable | Schwab's internal proprietary security number for their master file |
item_issue_id | VARCHAR(255) | nullable | Schwab item/issue identifier — unique across all security types including options |
isin | VARCHAR(255) | nullable | ISIN — 12-character International Securities Identification Number (ISO 6166) |
sedol | VARCHAR(255) | nullable | SEDOL — 7-character London Stock Exchange identifier used in UK/international markets |
options_display_symbol | VARCHAR(255) | nullable | Formatted display symbol for options (e.g. AAPL 240119C00180000) |
underlying_ticker_symbol | VARCHAR(255) | nullable | Ticker of the underlying security (for derivatives and structured products) |
underlyng_cusip | VARCHAR(255) | nullable | CUSIP of the underlying security (Schwab spelling variant) |
underly_schwab | VARCHAR(255) | nullable | Schwab internal number of the underlying security |
underlying_itm_iss_id | VARCHAR(255) | nullable | Schwab item/issue ID of the underlying security |
underlying_isin | VARCHAR(255) | nullable | ISIN of the underlying security |
underly_sedol | VARCHAR(255) | nullable | SEDOL of the underlying security |
current_quantity | NUMERIC | nullable | Total current share quantity held across all settlement types |
l_s | VARCHAR(255) | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
current_market_value | NUMERIC | nullable | Current market value of the position in USD |
accrued_interest | NUMERIC | nullable | Accrued interest on fixed income positions |
cost_basis_unamortized | NUMERIC | nullable | Raw cost basis before amortization of bond premium or discount |
cost_per_share | NUMERIC | nullable | Average cost per share — unamortized (used for equity lot tracking) |
adjusted_cost_basis_amortized | NUMERIC | nullable | Cost basis adjusted for premium/discount amortization (for bonds) |
adjusted_cost_per_share | NUMERIC | nullable | Average cost per share after amortization adjustments |
unrealized_gainloss | NUMERIC | nullable | Unrealised gain or loss — positive = gain, negative = loss |
c_b | BOOLEAN | nullable | Cost basis available flag — True if cost basis is tracked for this lot |
c_t | VARCHAR(255) | nullable | Cost basis calculation type code |
a_t | BOOLEAN | nullable | Adjusted cost basis flag — True if basis has been adjusted |
c_f | BOOLEAN | nullable | Cost basis flag for special tax situations (e.g. inherited, gifted) |
original_face | NUMERIC | nullable | Original face value for bond or mortgage-backed security lots |
dflot_selct | VARCHAR(255) | nullable | Default lot selection method override for this specific lot |
c_m | VARCHAR(255) | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
principal_paydown_factor | NUMERIC | nullable | Principal paydown factor for mortgage-backed securities |
imported_at | TIMESTAMP WITHOUT TIME ZONE | nullable | Timestamp when this row was first inserted into the database by the sync pipeline |
last_sync_at | TIMESTAMP WITHOUT TIME ZONE | nullable | Timestamp of the most recent sync run that touched this row (useful for detecting stale data) |
schwab_upt
44 columns
- Unrealized position totals — account-level rollup of unrealised gain/loss.
- Summarises schwab_upn data at the account level — single row per account per date.
- 44 columns covering total cost basis, total market value, total unrealised G/L (short-term and long-term).
- Used for client statement generation and top-level portfolio reporting.
- Highest level of aggregation in the Schwab unrealized G/L hierarchy: uln → upn → upt.
| Column | Type | Description | |
|---|---|---|---|
original_face | NUMERIC | nullable | Original face value for bond or mortgage-backed security lots |
mstracct_number | VARCHAR(255) | nullable | Schwab master account number (custodian-level umbrella account) |
security_type | VARCHAR(255) | nullable | Security type classification (Common Stock, Corporate Bond, Government Bond, Option…) |
c_f | BOOLEAN | nullable | Cost basis flag for special tax situations (e.g. inherited, gifted) |
custdian_id | VARCHAR(255) | nullable | Custodian institution code identifying the holding institution |
underlying_ticker_symbol | VARCHAR(255) | nullable | Ticker of the underlying security (for derivatives and structured products) |
underly_sedol | VARCHAR(255) | nullable | SEDOL of the underlying security |
underlying_itm_iss_id | VARCHAR(255) | nullable | Schwab item/issue ID of the underlying security |
adjusted_cost_per_share | NUMERIC | nullable | Average cost per share after amortization adjustments |
h2_h3 | VARCHAR(255) | nullable | |
underlying_isin | VARCHAR(255) | nullable | ISIN of the underlying security |
item_issue_id | VARCHAR(255) | nullable | Schwab item/issue identifier — unique across all security types including options |
prodcatg_code | VARCHAR(255) | nullable | Product category code for transaction classification |
c_t | VARCHAR(255) | nullable | Cost basis calculation type code |
adjusted_cost_basis_amortized | NUMERIC | nullable | Cost basis adjusted for premium/discount amortization (for bonds) |
unrealized_gainloss | NUMERIC | nullable | Unrealised gain or loss — positive = gain, negative = loss |
options_display_symbol | VARCHAR(255) | nullable | Formatted display symbol for options (e.g. AAPL 240119C00180000) |
a_t | BOOLEAN | nullable | Adjusted cost basis flag — True if basis has been adjusted |
underlyng_cusip | VARCHAR(255) | nullable | CUSIP of the underlying security (Schwab spelling variant) |
isin | VARCHAR(255) | nullable | ISIN — 12-character International Securities Identification Number (ISO 6166) |
l_s | VARCHAR(255) | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
prod_code | VARCHAR(255) | nullable | Product type code — EQ=Equity, MF=Mutual Fund, OPT=Option, FI=Fixed Income, ETF=ETF |
underly_schwab | VARCHAR(255) | nullable | Schwab internal number of the underlying security |
master_account_name | VARCHAR(255) | nullable | Human-readable display name of the master/custodian account |
c_m | VARCHAR(255) | nullable | Short-code COBOL field — abbreviated flag or classification code from Schwab's fixed-width format |
sedol | VARCHAR(255) | nullable | SEDOL — 7-character London Stock Exchange identifier used in UK/international markets |
cusip | VARCHAR(255) | nullable | CUSIP — 9-character North American security identifier issued by DTCC |
accrued_interest | NUMERIC | nullable | Accrued interest on fixed income positions |
filename | VARCHAR(255) | nullable | Source filename on Azure Hot Storage or Fidelity SFTP that produced this row |
current_market_value | NUMERIC | nullable | Current market value of the position in USD |
cost_basis_unamortized | NUMERIC | nullable | Raw cost basis before amortization of bond premium or discount |
ticker_symbol | VARCHAR(255) | nullable | Exchange ticker symbol (e.g. AAPL, MSFT) — may be blank for bonds and OTC securities |
cost_per_share | NUMERIC | nullable | Average cost per share — unamortized (used for equity lot tracking) |
schwab_sec_nbr | VARCHAR(255) | nullable | Schwab's internal proprietary security number for their master file |
account_id | VARCHAR(255) | nullable | Schwab individual account identifier within the master account |
tax_code | VARCHAR(255) | nullable | Tax treatment code controlling cost basis method and 1099 classification |
business_date | DATE | nullable | The Schwab business date this record corresponds to — used to snapshot daily files |
dflot_selct | VARCHAR(255) | nullable | Default lot selection method override for this specific lot |
c_b | BOOLEAN | nullable | Cost basis available flag — True if cost basis is tracked for this lot |
principal_paydown_factor | NUMERIC | nullable | Principal paydown factor for mortgage-backed securities |
current_quantity | NUMERIC | nullable | Total current share quantity held across all settlement types |
__master_account | VARCHAR(255) | nullable | Internal Redwood master account identifier used to partition data across clients |
imported_at | TIMESTAMP WITHOUT TIME ZONE | nullable | Timestamp when this row was first inserted into the database by the sync pipeline |
last_sync_at | TIMESTAMP WITHOUT TIME ZONE | nullable | Timestamp of the most recent sync run that touched this row (useful for detecting stale data) |
Pipeline · Configuration & Logs
schwab_sync_config
config / log
11 columns
- Configuration registry for the Schwab sync pipeline — one row per file type.
- Defines object codes (acc, trn, sec, rps, rld, uln …), enabled/disabled flag, and destination table + schema.
- Specifies sync mode: full (truncate-and-replace all rows) or incremental (merge/upsert changes only).
- Stores archival policy and last successful sync timestamp per file type.
- Edit this table to add new file types or modify sync behaviour without code changes.
| Column | Type | Description | |
|---|---|---|---|
master_account | VARCHAR(50) | required | Redwood master account identifier for pipeline scoping |
object | VARCHAR(10) | required | Schwab file type code (acc, trn, sec, rps, uln, upn, tcf…) |
sync_mode | VARCHAR(20) | required | Sync strategy: full=truncate and reload, incremental=merge/upsert changes only |
destination_table | VARCHAR(100) | required | PostgreSQL table where this template's report data is loaded |
enabled | BOOLEAN | required | Whether this object/file type is currently active in the pipeline |
description | TEXT | nullable | Human-readable description of this pipeline configuration entry |
archival_policy | VARCHAR(50) | nullable | Cold storage policy — defines retention period before archiving (e.g. 'after 7 days') |
last_sync_date | TIMESTAMP WITH TIME ZONE | nullable | Timestamp of the most recent successful sync for this object |
database_schema | VARCHAR(50) | required | PostgreSQL schema where the destination table resides |
created_at | TIMESTAMP WITH TIME ZONE | nullable | Row creation timestamp (database-side) |
updated_at | TIMESTAMP WITH TIME ZONE | nullable | Timestamp when this row was last updated |
schwab_sync_log
config / log
15 columns
- Operational pipeline log — one row per file processed by the Schwab data sync DAG.
- Tracks filename, file date, SHA-256 hash for deduplication, and record count per file.
- Records destination table, processing status, sync start and completion timestamps.
- Archival dates stored: DB archive timestamp and file archive date to Azure Cold Storage.
- Used for pipeline monitoring, idempotency checks, and full audit trails of data ingestion.
| Column | Type | Description | |
|---|---|---|---|
log_id | BIGINT | required | Auto-incrementing primary key for the log entry |
master_account | VARCHAR(50) | required | Redwood master account identifier for pipeline scoping |
filename | VARCHAR(255) | required | Source filename on Azure Hot Storage or Fidelity SFTP that produced this row |
filedate | DATE | nullable | Date embedded in or derived from the source filename |
file_hash | VARCHAR(64) | nullable | SHA-256 hash of file content — used for deduplication to prevent re-processing |
number_of_records | INTEGER | nullable | Count of data records in the processed source file |
destination_table | VARCHAR(100) | required | PostgreSQL table where this template's report data is loaded |
status | VARCHAR(20) | required | Processing status: pending → in_progress → copied → failed → archived |
database_archived_date | TIMESTAMP WITH TIME ZONE | nullable | Timestamp when database records were archived to Azure Cold Storage |
file_archived_date | TIMESTAMP WITH TIME ZONE | nullable | Timestamp when the source file was moved to Azure Cold Storage |
sync_started_at | TIMESTAMP WITH TIME ZONE | nullable | Timestamp when the sync pipeline began processing this file |
sync_completed_at | TIMESTAMP WITH TIME ZONE | nullable | Timestamp when the sync pipeline finished processing this file |
error_message | TEXT | nullable | Error message if processing failed — null on success |
created_at | TIMESTAMP WITH TIME ZONE | nullable | Row creation timestamp (database-side) |
updated_at | TIMESTAMP WITH TIME ZONE | nullable | Timestamp when this row was last updated |