Excel Tips for Financial Analysts

Data Import and Formatting

Preventing Auto-Formatting Issues

Excel's helpful auto-formatting often causes problems with financial data:

Issue: Tickers Converted to Dates

Tickers like "MAR" (Marriott) get converted to March dates

Solution:

1. Format cells as Text before pasting
2. Or prefix with apostrophe: 'MAR
3. Or import via Data → From Text/CSV (auto-detects)

Issue: Leading Zeros Removed

CUSIPs like "037833100" become "37833100"

Solution:

1. Format cells as Text
2. Use TEXT function: =TEXT(A1,"000000000")
3. Import as text column in Power Query

Issue: Large Numbers in Scientific Notation

ISINs display as "3.78E+11"

Solution:

1. Format as Text (not Number)
2. Increase decimal places temporarily
3. Use custom format: 0

Essential Financial Formulas

Portfolio Returns

Calculate percentage returns:

// Simple return
=(End_Price - Start_Price) / Start_Price

// In Excel with cells
=(B2-A2)/A2

// Format as Percentage
Right-click → Format Cells → Percentage, 2 decimals

XIRR - Actual Returns with Cash Flows

Better than simple IRR for portfolios with deposits/withdrawals:

=XIRR(values, dates)

Example:
Date        | Cash Flow
2024-01-01  | -10000    (initial investment)
2024-06-01  | -2000     (additional deposit)
2024-12-31  | 13500     (ending value)

=XIRR(B2:B4, A2:A4) = 15.2% annualized

VLOOKUP for Price Lookups

// Basic syntax
=VLOOKUP(lookup_value, table_array, col_index, FALSE)

// Example: Look up price for ticker
=VLOOKUP(A2, PriceTable, 2, FALSE)

// Tip: Use FALSE for exact match (critical for tickers)

INDEX/MATCH - More Flexible Than VLOOKUP

// Can look left, more robust
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))

Example:
=INDEX(Prices!B:B, MATCH(A2, Prices!A:A, 0))

// Advantages:
// - Works if columns rearranged
// - Can look to left of lookup column
// - Generally faster than VLOOKUP

SUMIF and SUMIFS for Sector Totals

// Single condition
=SUMIF(range, criteria, sum_range)
=SUMIF(Sectors, "Technology", Values)

// Multiple conditions
=SUMIFS(sum_range, criteria_range1, criteria1, criteria_range2, criteria2)
=SUMIFS(Values, Sectors, "Tech", Countries, "US")

Power Query for Data Automation

Automating Bloomberg Exports

1. Data → Get Data → From File → From CSV
2. Select your Bloomberg export
3. Transform: Remove "US Equity" suffixes
4. Transform: Split columns, clean headers
5. Close & Load
6. Refresh data: Right-click → Refresh

Cleaning Ticker Formats

Power Query M language for ticker cleanup:

// Remove exchange suffixes
= Table.TransformColumns(
    Source,
    {{"Ticker", each Text.BeforeDelimiter(_, " "), type text}}
)

// Uppercase all tickers
= Table.TransformColumns(
    Source,
    {{"Ticker", Text.Upper, type text}}
)

Combining Multiple Files

Merge multiple Bloomberg exports:

1. Put all CSVs in one folder
2. Data → Get Data → From Folder
3. Select folder
4. Combine & Transform Data
5. All files merged automatically!

Pivot Tables for Analysis

Portfolio Analysis by Sector

1. Select your data
2. Insert → PivotTable
3. Rows: Sector
4. Values: Sum of Market Value
5. Values: Average of Return

Custom Calculations in Pivot Tables

Show percentage of total:

1. Click value in pivot table
2. Value Field Settings
3. Show Values As → % of Grand Total

Slicers for Interactive Dashboards

1. Click pivot table
2. PivotTable Analyze → Insert Slicer
3. Select fields (Sector, Country, etc.)
4. Click slicer buttons to filter

Data Validation and Quality

Create Ticker Dropdown Lists

1. Create list of valid tickers in a column
2. Select cells where you want dropdown
3. Data → Data Validation
4. Allow: List
5. Source: =ValidTickers (your list range)

Conditional Formatting for Alerts

Highlight Negative Returns:

1. Select return column
2. Home → Conditional Formatting → New Rule
3. Format cells where value < 0
4. Choose red fill

Color Scale for Performance:

1. Select return column
2. Conditional Formatting → Color Scales
3. Choose Red-Yellow-Green scale

Find and Remove Duplicates

// Using COUNTIF to identify
=COUNTIF($A$2:A2, A2)>1

// Or use built-in feature
Data → Remove Duplicates

Time-Saving Shortcuts

Essential Keyboard Shortcuts

Flash Fill for Pattern Recognition

Excel learns patterns from examples:

1. Type example of desired output
2. Start typing second example
3. Excel suggests pattern (press Enter)
4. Or Ctrl+E to trigger manually

Example: Extract ticker from "AAPL US Equity"

A1: AAPL US Equity    B1: AAPL (you type)
A2: MSFT US Equity    B2: MSFT (Flash Fill suggests)
A3: GOOGL US Equity   B3: GOOGL (auto-filled)

Portfolio Dashboard Best Practices

Structure Your Workbook

1. Data Sheet: Raw imported data
2. Calculations Sheet: Formulas and processing
3. Dashboard Sheet: Charts and summary
4. Reference Sheet: Lookup tables, constants

Use Named Ranges

Makes formulas readable:

// Instead of
=SUM(B2:B100)

// Use
=SUM(PortfolioValues)

// Create named range:
Select range → Name Box (left of formula bar) → Type name

Protect Formula Cells

1. Select input cells → Format Cells → Protection → Uncheck "Locked"
2. Review → Protect Sheet
3. Users can only edit unlocked cells

Common Pitfalls to Avoid

Pitfall #1: Hardcoded Values in Formulas

❌ =A2 * 0.02  (what is 0.02?)
✓ =A2 * Management_Fee  (named cell = 0.02)

Pitfall #2: Not Using Tables

Tables (Ctrl+T) automatically expand formulas:

❌ =VLOOKUP(A2, $D$2:$E$100, 2, FALSE)
   (breaks if data grows beyond row 100)

✓ =VLOOKUP(A2, PriceTable, 2, FALSE)
   (PriceTable automatically expands)

Pitfall #3: Circular References

When a formula refers to itself:

❌ A1: =A1+B1  (circular!)

✓ Enable iterative calculation:
File → Options → Formulas → Enable iterative calculation
(Only if intentional, like goal seek)

Pitfall #4: Not Documenting Assumptions

Add notes for key assumptions:

1. Right-click cell → Insert Comment
2. Document source, date, assumptions
3. Or create separate Assumptions sheet