Repository: OpenBB-finance/OpenBB
Pull request: fix: Modify 'load' behavior to append data to existing sheet
Details:
- Added logic to detect existing sheet and append data if present.
- There is no direct way to append on the sheet, so I am reading the sheet to get the last row number and then writing after that row number.

Resolves: #5472

# Description

- [x] Summary of the change / bug fix.
- [x] Link # issue, if applicable.
- [ ] Screenshot of the feature or the bug before/after fix, if applicable.
- [x] Relevant motivation and context.
- [ ] List any dependencies that are required for this change.


# How has this been tested?

* Please describe the tests that you ran to verify your changes.
* Provide instructions so we can reproduce.
* Please also list any relevant details for your test configuration.
- [x] Make sure affected commands still run in terminal
- [ ] Ensure the SDK still works
- [ ] Check any related reports


# Checklist:

- [x] I have adhered to the GitFlow naming convention and my branch name is in the format of `feature/feature-name` or `hotfix/hotfix-name`.
- [ ] Update [our documentation](https://openbb-finance.github.io/OpenBBTerminal/) following [these guidelines](https://github.com/OpenBB-finance/OpenBBTerminal/tree/main/website).  Update any user guides that are affected by the changes.
- [ ] Update our tests following [these guidelines](https://github.com/OpenBB-finance/OpenBBTerminal/tree/main/tests).
- [x] Make sure you are following our [CONTRIBUTING guidelines](https://github.com/OpenBB-finance/OpenBBTerminal/blob/main/CONTRIBUTING.md).
- [ ] If a feature was added make sure to add it to the corresponding [integration test script](https://github.com/OpenBB-finance/OpenBBTerminal/tree/develop/openbb_terminal/miscellaneous/integration_tests_scripts).


# Others
- [x] I have performed a self-review of my own code.
- [ ] I have commented my code, particularly in hard-to-understand areas.


Problem statement:
[IMPROVE] Append existing `--sheet-name` with additional rows when exporting to XLSX
What would be an awesome outcome is if:

`load aapl --start 2022-01-01 --end 2022-12-31 --weekly --export aapl.xlsx --sheet-name weekly`

could be appended to add more rows later:

`load aapl --start 2023-01-01 --weekly --export aapl.xlsx --sheet-name weekly`


The current behaviour is to create a new sheet-name, `weekly1`.

Hints from issue discussion:
Hey @deeleeramone, could I work on this issue? 
> Hey @deeleeramone, could I work on this issue?

![image](https://github.com/OpenBB-finance/OpenBBTerminal/assets/85772166/05d5b2a4-f8a1-4fc0-ae59-6debb960a17f)

Thank you for assigning this issue to me 😀. Could I please have a starting point to build up from so that I can get started by the earliest.
> Thank you for assigning this issue to me 😀. Could I please have a starting point to build up from so that I can get started by the earliest.

I think the place to start poking around is, `export_data()`, in, `/openbb_terminal/helper_funcs.py`.

Commit message: fix: Modify 'load' behavior to append data to existing sheet

Details:
- Added logic to detect existing sheet and append data if present.
- There is no direct way to append on the sheet, so I am reading the sheet to get the last row number and then writing after that row number.

Resolves: #5472

Patch to review:
diff --git a/openbb_terminal/helper_funcs.py b/openbb_terminal/helper_funcs.py
index 700ef0b7316a..84c128a0b7b9 100644
--- a/openbb_terminal/helper_funcs.py
+++ b/openbb_terminal/helper_funcs.py
@@ -1602,15 +1602,28 @@ def export_data(
                     )
 
                 elif saved_path.exists():
+                    # Load the Excel file to get the existing data
+                    existing_df = pd.read_excel(saved_path, sheet_name=sheet_name)
+                    # Get the number of rows in the existing data
+                    start_row = (
+                        existing_df.shape[0] + 1
+                    )  # Add 1 to start writing after the last row
+
+                    # Append data to the existing sheet
                     with pd.ExcelWriter(
                         saved_path,
                         mode="a",
-                        if_sheet_exists="new",
+                        if_sheet_exists="overlay",
                         engine="openpyxl",
                     ) as writer:
                         df.to_excel(
-                            writer, sheet_name=sheet_name, index=True, header=True
+                            writer,
+                            sheet_name=sheet_name,
+                            startrow=start_row,
+                            index=True,
+                            header=False,
                         )
+
                 else:
                     with pd.ExcelWriter(
                         saved_path,


Code review comments:
diff_hunk: @@ -1602,15 +1602,28 @@ def export_data(
                     )
 
                 elif saved_path.exists():
+                    # Load the Excel file to get the existing data
+                    existing_df = pd.read_excel(saved_path, sheet_name=sheet_name)
+                    # Get the number of rows in the existing data
+                    start_row = (
+                        existing_df.shape[0] + 1
+                    )  # Add 1 to start writing after the last row
+
+                    # Append data to the existing sheet
                     with pd.ExcelWriter(
                         saved_path,
                         mode="a",
-                        if_sheet_exists="new",
path: openbb_terminal/helper_funcs.py
text: @user1:
Thanks for this! It appears to be working, however with one slight caveat:

- Assigning a sheet name to a new export is now generating an error:

```
(🦋) /stocks/ $ load aapl -i 1 --source YahooFinance --export test1.xlsx --sheet-name regular

Loading Intraday 1min data for AAPL with starting period 2023-09-26.
Error: Worksheet named 'regular' not found
```

IIUC, the solution is looking to read for an existing sheet name.  We just need to add in something that writes to a new sheet name if the supplied name does not exist in the file.

@author:
I have fixed the 'worksheet not found' error. Moreover, I would also like to ask the user, if they provide an existing sheet name, if they want to append or replace it. I was wondering if there is some code that I can reuse to prompt the user and get the input.

@user1:
There is a Feature Flag - environment variable - that is called "overwrite".  When this is false, the user is prompted Y/N to overwrite an existing file when the exported filename already exists.  I don't see why that can't be recycled for this purpose as well. 

@author:
Changes done.
![image](https://github.com/OpenBB-finance/OpenBBTerminal/assets/11327529/c5a619b4-ae20-469b-85ac-540fbc94ec9d)