import requests
import datetime
import pandas as pd

def get_historical_weather(api_key, lat, lon, start_date, end_date, output_file):
    url = "http://api.weatherapi.com/v1/history.json"
    
    current_date = datetime.datetime.strptime(start_date, "%Y-%m-%d")
    end_date = datetime.datetime.strptime(end_date, "%Y-%m-%d")
    
    weather_data = []

    while current_date <= end_date:
        params = {
            "key": api_key,
            "q": f"{lat},{lon}",
            "dt": current_date.strftime("%Y-%m-%d")
        }
        
        response = requests.get(url, params=params)
        
        if response.status_code == 200:
            data = response.json()
            day_data = data['forecast']['forecastday'][0]['day']
            weather_data.append({
                "date": current_date.strftime("%Y-%m-%d"),
                "rainfall_mm": day_data['totalprecip_mm'],
                "avg_temp_c": day_data['avgtemp_c'],
                "max_temp_c": day_data['maxtemp_c'],
                "min_temp_c": day_data['mintemp_c']
            })
        else:
            print(f"Failed to retrieve data for {current_date.strftime('%Y-%m-%d')}: {response.status_code}, {response.text}")
        
        current_date += datetime.timedelta(days=1)
    
    # Create a DataFrame and save it as an Excel file
    df = pd.DataFrame(weather_data)
    df.to_excel(output_file, index=False)
    
    print(f"Weather data saved to {output_file}")

# Example usage:
api_key = "dd6e11f5d20e4694bed72932241007"
latitude = 40.7128
longitude = -74.0060
start_date = "2023-01-01"
end_date = "2023-01-07"
output_file = "historical_weather.xlsx"

get_historical_weather(api_key, latitude, longitude, start_date, end_date, output_file)