Using TradingView to Backtest Your Portfolio Strategy with Excel and Microsoft Office 365 Plus

Backtesting a portfolio strategy requires reliable historical data, a calculation engine, and a way to visualise results. TradingView supplies charting and data, while Microsoft Office 365 Plus gives Excel’s powerful analytical tools. Follow these numbered steps to pull data from TradingView, import it into Excel, and run a backtest.

1. Choose a TradingView plan that supports data export

1. Log into TradingView and navigate to the chart of the first asset.
2. Click the Export Chart Data icon (down‑arrow beside the chart title).
3. Select CSV and set the date range (e.g., 01/01/2018 – 31/12/2023). The free plan caps at 100 rows; Premium removes this limit.
4. Download the file. Repeat for each asset in your portfolio.

2. Consolidate data in a single Excel workbook

1. Open Microsoft Excel from Office 365 Plus.
2. Create a new workbook and name the first sheet Data.
3. Use Data → Get & Transform Data → From Text/CSV to import each CSV.
4. In Power Query, rename columns to Date, Open, High, Low, Close, Volume and set Date as a date type.
5. Append all queries into a single table using Append Queries.
6. Close & Load to a sheet named Combined.

3. Build a simple backtest model

1. In a new sheet, create a table with columns: Date, Asset, Position, Entry Price, Exit Price, P/L.
2. Use VLOOKUP or XLOOKUP to pull Close prices from the Combined sheet based on date and asset.
3. Define your entry and exit rules in separate columns. For example, buy when a 20‑day SMA crosses above the 50‑day SMA.
4. Calculate P/L as (Exit Price – Entry Price) * Position. Use IFERROR to handle missing data.
5. Sum P/L for each period to get cumulative performance.

4. Visualise results with Office 365 Plus features

• Insert a line chart of cumulative P/L over time.
• Use conditional formatting to highlight drawdowns.
• Add a pivot table to break down performance by asset.
• Leverage Office 365’s Ideas feature to auto‑generate insights.

5. Common pitfalls and how to avoid them

• Data mismatch: Ensure all symbols use the same exchange and time zone. TradingView uses UTC; adjust in Excel if necessary.
• Missing rows: Free plan exports may omit days. Check row counts and upgrade to Premium if gaps exceed 5 days.
• Formula errors: Circular references can occur if entry and exit formulas reference each other. Use separate columns and lock ranges.
• Performance lag: Large datasets (>50,000 rows) can slow Excel. Use Power Pivot or split data across sheets.

For deeper visual reports, consider linking the Excel workbook to Canva Pro to create polished charts. See our guide for step‑by‑step integration.

Automating the export process can be done with TradingView webhooks and N8N Starter. Read this article for details.

For official documentation, visit TradingView and Microsoft Office 365.