Summary
Keywords
Full Transcript
@Matt Macarty ## 🐍 Python: Calculate Stock Returns & Save to Multi-Sheet Excel File (Pandas) | Part 6 Welcome to Part 6 of the **Python Stock Analysis Course**! This video provides a complete workflow for **downloading, transforming, and exporting** financial data for external use, particularly with **Excel**. You will learn how to write a function that performs a fresh data pull using the EOD API client, calculates both **instantaneous returns** and **percent changes**, and then writes all the results to a single, multi-sheet **Excel (.xlsx) file** using the **Pandas ExcelWriter**. This is crucial for sharing your analyzed data or performing further modeling outside of Python. ### 🎯 Key Learning Outcomes: 1. **Full Download & Transform Workflow:** Write a reusable function to download stock data for multiple tickers and immediately calculate two types of returns: **Instantaneous Rate of Return** (using NumPy) and **Percent Change** (using Pandas built-in method). 2. **Flexible Column Choice:** Allow users to choose between using the **Close** or **Adjusted Close** price for calculations. 3. **Master the ExcelWriter:** Learn how to use the **Pandas ExcelWriter** object and a context manager to save multiple DataFrames (prices, instantaneous returns, percent changes) to different sheets within a single `.xlsx` file. 4. **External Compatibility:** Understand how to prepare Python data for seamless use in applications like Microsoft Excel or Google Sheets. ### ⏱️ Video Chapters (Jump Ahead!): 0:00 - Introduction & Review (Moving from plots to data export) 0:30 - Defining the `get_return_data` function (Arguments: Tickers, Date, Adjusted Close) 0:53 - Setting up the **EOD API Client** 1:10 - Looping through Tickers and Using a **Try/Except Block** 1:28 - Conditional Logic: Downloading **Adjusted Close vs. Close** Prices 2:36 - **Calculating Instantaneous Rate of Return (NumPy)** 2:57 - **Calculating Percent Change (Pandas Built-in)** 3:09 - **Writing to Multi-Sheet Excel with Pandas ExcelWriter** 3:35 - Adding DataFrames to Separate Excel Sheets (Prices, Returns, Changes) 4:34 - Testing the Function with Multiple Tickers 5:17 - Preview of Part 7: Plotting All Securities on One Chart ### 🔗 Course Series & Resources: * **Part 5 (Correlation & Plots):** [https://youtu.be/OYS75jihV5Y](https://youtu.be/OYS75jihV5Y) * **Get the Code:** https://github.com/mjmacarty/python-stock-analysis ***Disclaimer: This video is for educational purposes only. The information provided should not be construed as investment advice. *** \#PythonForFinance \#PandasExcel \#ExcelWriter \#CalculateReturns \#StockReturns \#NumPy \#DataExport \#PythonTutorial \#Part6
Continue this lesson in the app
Install CourseHive on Android or iOS to keep learning while you move.
