Demand Forecasting using Arima

[LukasBen123]

I am trying to build a python tool that can forecast for over 100,000 PART/Locations at one time. Everything I have tried has failed.

I've attached an screenshot of my dataset. I want every part to be forecasted using the Arima model based on B-Y for each part number (24 months of demand). Columns Z through AK should have a forecast for each month for the next year (calculated by the Arima model).

Any help would be extremely appreciated.

https://ibb.co/9WNV5G7

[Larz60+]

Please show:

• your code so far, working or not
  
• all inputs
  
• expected output
  
• actual output if any
  
• Anything else that will help with analysis
  

[LukasBen123]

Please show:

• your code so far, working or not
  
• all inputs
  
• expected output
  
• actual output if any
  
• Anything else that will help with analysis
  

Hi Larz,

I could not build working code. Everything was errors every time. The expected output would have 12 forecasts per part, using the Arima model.
I cannot attach the file as it is too large, but here is a screenshot.
https://ibb.co/0jLH20w
In forecast month 1-12 I plugged in an excel forecast formula, the only difference in what I am looking for is that the values would be calculated using the ARIMA model in Python.

Thanks

[LukasBen123]

Please show:

• your code so far, working or not
  
• all inputs
  
• expected output
  
• actual output if any
  
• Anything else that will help with analysis
  

Hi Larz,

I could not build working code. Everything was errors every time. The expected output would have 12 forecasts per part, using the Arima model.
I cannot attach the file as it is too large, but here is a screenshot.
https://ibb.co/0jLH20w
In forecast month 1-12 I plugged in an excel forecast formula, the only difference in what I am looking for is that the values would be calculated using the ARIMA model in Python.

Thanks

# Define the path to your Excel file

xlsb_file_path = 'Z:*LOCATION*'

 

# Define the list to store your forecasts

forecasts = []

 

# Open the .xlsb file

with open_xlsb(xlsb_file_path) as wb:

   # Get the first sheet in the workbook

   with wb.get_sheet(1) as sheet:

       rows = list(sheet.rows())

       # Remove the first row as it contains headers

       rows.pop(0)

       # Iterate through rows in the first sheet

       for row in rows:

           # Convert the row into a pandas series

           series = pd.Series([item.v for item in row[1:]], index=range(1, 25))

           # Fit an ARIMA model

           model = ARIMA(series, order=(5,1,0))

           model_fit = model.fit()

           # Forecast the next 12 months' demand

           output = model_fit.forecast(steps=12)

           # Store the forecasts

          forecasts.append(output[0])

 

# Read the data again with pandas

df = pd.read_excel(xlsb_file_path, engine='pyxlsb')

 

# Check if forecasts were created correctly

if len(forecasts) == len(df):

   # Create columns for each forecasted month

   for i in range(12):

      df[f'Forecast_{i+1}'] = [forecast[i] for forecast in forecasts]

 

# Save the data to a new .xlsx file (as .xlsb is not supported)

df.to_excel('Z:\\*LOCATION*', index=False)

This is one of my iterations. I don't think it is close to doing what I am looking for, but maybe! lol

I've hidden the file locations.

[Pedroski55]

You should post an Excel with just a few lines of data, say 10 rows.

I have no idea what the "Arima model" actually looks like. I suppose it is some kind of "sum of differences divided by time period"

Can you post the actual formula you wish to use on the data from the XL?

[LukasBen123]

You should post an Excel with just a few lines of data, say 10 rows.

I have no idea what the "Arima model" actually looks like. I suppose it is some kind of "sum of differences divided by time period"

Can you post the actual formula you wish to use on the data from the XL?

I don't have the formula in excel. It is nearly impossible to replicate the formula in excel, which is why I am trying to learn how to do it in Python.

Typically all of my data analysis is done in excel on a powerful computer as it can compute up to 2GB excel analysis's very quickly, but my limitation is not being able to replicate the ARIMA formula.

[Pedroski55]

Yesterday I saw Python has modules for ARIMA calculations. But I would still like to see the actual algorithm.

I presume you find the difference in value between say January and February, then multiply that by a factor representing your hoped-for growth as the estimated figure for March. If growth stalls or sinks, re-evaluate the estimate using the actual data for February and March. Maybe add a seasonal factor as well.

ARIMA models are generally denoted as ARIMA (p, d, q), where p is the order of the autoregressive model (AR), d is the degree of differencing, and q is the order of the moving-average model(MA). ARIMA model uses differencing to convert a non-stationary time series into a stationary one and then predict future values from historical data. The model uses “auto” correlations and moving averages over residual errors in the data to forecast future values.

If you don't post a small sample of your data, it will be hard for people here to try doing what you want to do with your data!