Unable to get the forecast_ets property of the worksheetfunction class
I've been trying to call the Forecast.ETS
function from VBA code with Application.WorksheetFunction
, however I'm getting the "unable to get the forecast_ets
property of the worksheetfunction class error."
Sub test()
Cells(20, 5) = Application.WorksheetFunction.Forecast_ETS(Cells(5, 3).Value, _
Range(Cells(5, 3), Cells(20, 3)), _
Range(Cells(5, 2), Cells(20, 2)).Value)
End Sub
Cells(5,3).Value
is a date, same as Range(Cells(5,2),Cells(20,2)).Value
Range(Cells(5, 3), Cells(20, 3))
contains the historical values.
See also questions close to this topic

How to insert/Update 10000 rows in SQL Server using C# efficiently while comparing each row from database
I have been given an Excel file from a customer. It has 4 columns: Id, name, place, date).
I have a table in my database which stores these values. I have to check each row from Excel and compare its values to the database table. If a row already exists, then compare the date and update to latest date from Excel. If the row does not exist yet, insert a new row.
I'm fetching each row and comparing its values using for a loop and updating database using insert/update statement by creating data table adapter.
My problem is this operation is taking 4+ hours to update the data. Is there any efficient way to do this? I have searched a lot and found options like
SqlBulkCopy
but how will I compare each and every row from database?I'm using ASP.NET with C# and SQL Server.
Here's my code:
for (var row = 2; row <= workSheet.Dimension.End.Row; row++) { // Get data from excel var Id = workSheet.Cells[row, 1].Text; var Name = workSheet.Cells[row, 2].Text; var Place = workSheet.Cells[row, 3].Text; var dateInExcel = workSheet.Cells[row, 4].Text; // check in database if ID exists in database then compare date and update database> if (ID.Rows.Count <= 0) //no row exist in database { // Insert row in the database using data table adapter's insert statement } else if (Id.Rows.Count > 0) //Id exists in database { if (Db.DateInDB < (dateUpdate)) // compare dates { // Update database with the new date using data table adapter Update statement. } } }

loading data from excel
I am new to C# and I wanted to ask a question: I have an Excel file in which the first column is number of row second column is variable x(number) and third column is variable y(number) and I want to show variable x and y in textbooks, The rest of code that opens Excel is OK because it's working in another form, I use this code for it:
for (int i = 1; i <= 11; i++) //rowCount { if (xlRange.Cells[i, 1] != null && xlRange.Cells[i, 2].Value2 != null) { int x = xlRange.Rows[i  1].Cells[1].Value = xlRange.Cells[1].Value2; int y = xlRange.Rows[i  1].Cells[2].Value = xlRange.Cells[2].Value2; hodnotax.Text = x.ToString(); hodnotay.Text = y.ToString(); } }
but in the final step, I get this error:
System.Runtime.InteropServices.COMException: 'Valuebased exception HRESULT: 0x800A03EC'
and I don't know what to do with that code to make it work, thanks for every answer

How to copy and paste values in another worksheet using transpose and loop it
I have been having trouble in copy pasting a range of cells (vertically) to another worksheet which should display horizontally. And also using a spin button to determine how many times it will loop to copy the data.
This is my single line of code to copy and transpose, I only get the error in "Transpose:=True" The error is
Compiler Expected: error element
I only follow YouTube tutorials and I just combine my code.
Range("D3:D10").Copy Worksheets("Sheet2").Range("A2").PasteSpecial Transpose:=True
And this is my code for the spin button that will determine how many times it will copy and paste to the other worksheet. Cell G7 will determine the number of times it will copy to the other worksheet
With Range("G7") .Value = WorksheetFunction.Min(.Value  1) End With End Sub Private Sub SpinButton1_SpinUp() With Range("G7") .Value = WorksheetFunction.Max(.Value + 1) End With End Sub
Thank you for your help.

Scraping data from website with css selectors excel vba
I am trying to scrape specific data from website with CSS selectors. I succeeded with the help of QHar but the requirements now have changed. This is my code below:
Code
Public Sub CompanyData2() Dim html As HTMLDocument, ws As Worksheet, re As Object Set re = CreateObject("VBScript.RegExp") re.Pattern = "\s{2,}" Set ws = ThisWorkbook.Worksheets("Sheet1") Set html = New HTMLDocument With CreateObject("MSXML2.XMLHTTP") .Open "GET", "https://www.bizi.si/iskanje?q=", False .send html.body.innerHTML = .responseText End With ws.Range("A4").Value = re.Replace(Join$(Array(html.querySelector("td.item a").innerText), ", "), Chr$(32)) ws.Range("A5").Value = re.Replace(Join$(Array(html.querySelector("td.item + td.item").innerText), ", "), Chr$(32)) ws.Range("B6").Value = re.Replace(Join$(Array(html.querySelector("td.item + td.item + td.item + td.item").innerText), ", "), Chr$(32)) End Sub
The result is as follows:
Website
I want to extract name of company on sheet 1 A3 like that:
Thank you.

Delete all rows that contains any of several texts
I have a main sheet that contains 100k rows of data. In a second sheet I have a column "A" that contains criteria for deleting the rows.
And I want to create a macro button that will delete all the rows that its column "D" contains any of the criteria on the second sheet.
Currently I used this. But I got an error message saying,
"Runtime error '1004':Applicationdefined or objectdefined error"
And almost all rows are gone.
I just learn about excel vba today, so please explain in a way that even beginners would understand. Thank you so much.
Option Explicit Public Sub Find_Vendor() Dim filters As Variant With Sheets("Criteria") ' change "Criteria" to your actual sheet with lookup values name filters = Application.Transpose(.Range("A2", .Cells(.Rows.Count, 1).End(xlUp)).Value) ' collect "Criteria" sheet column A values from row 2 down to last not empty row End With With Sheets("Factory") 'reference "Factory" sheet With .Range("D2", .Cells(.Rows.Count, 3).End(xlUp)) ' reference referenced sheet column D cells from row down to last not empty one .AutoFilter Field:=1, Criteria1:=filters, Operator:=xlFilterValues ' filter referenced range with values from "LookupColumnSheet" sheet column A If Application.WorksheetFunction.Subtotal(103, .Cells) > 1 Then .Resize(.Rows.Count  1).Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Delete ' if any filtered cells other than header then delete their entire row End With .AutoFilterMode = False ' remove filters End With End Sub

Unable to run legacy code to prepare forecast
I am trying to run this code to prepare forecast. This is a legacy code. I am new to R. I have made some changes but unable to comprehend most of it and not able to run it. Can some one help me to understand and run it?
Summary:
This code is supposed to read the sales history of multiple products from an excel file. create a time series, then forecast using multiple methods, there are multiple variables created to store the forecast value in a Matrix. Also create the MAPE to calculate the error of the forecast and then write the forecasted value in to an excel. The excel only has 1 sheet, I want to prepare forecast of each product individually by running a loop. I am trying to use batch forecasting technique. I have around 10,000 Products to do the forecast. For this example, I have prepared a demo data set just to show the type of data. Here is the reprex of the code below. Hope to get response.
#rm(list = ls()) setwd("d:\\DemandPlanning") # install.packages("reprex") reprex::reprex() #> No input provided and clipboard is not available. #> Rendering reprex... library(readxl) library(fpp2) #> Loading required package: ggplot2 #> Loading required package: forecast #> Registered S3 method overwritten by 'xts': #> method from #> as.zoo.xts zoo #> Registered S3 method overwritten by 'quantmod': #> method from #> as.zoo.data.frame zoo #> Registered S3 methods overwritten by 'forecast': #> method from #> fitted.fracdiff fracdiff #> residuals.fracdiff fracdiff #> Loading required package: fma #> Loading required package: expsmooth library(forecast) library(devtools) #> Loading required package: usethis library(ggplot2) library(fma) library(expsmooth) library(tseries) library(timeSeries) #> Loading required package: timeDate library(ggseasonplot) #> Error in library(ggseasonplot): there is no package called 'ggseasonplot' library(data.table) library(Rcpp) library(xlsx) library(openxlsx) #> #> Attaching package: 'openxlsx' #> The following objects are masked from 'package:xlsx': #> #> createWorkbook, loadWorkbook, read.xlsx, saveWorkbook, #> write.xlsx library(naniar) #sample data for reprex sales_data < data.frame( dates = c('Week1', 'Week2', 'Week3', 'Week4', 'Week5', 'Week6', 'Week7', 'Week8', 'Week9', 'Wk10', 'Wk11'), Prod1 = c(92, 94, 60, 71, 26, 89, 52, 151, 14, 12, 19), Prod2 = c(56, 63, 90, 76, 47, 65, 65, 61, 85, 48, 64), Prod3 = c(106, 154, 118, 103, 90, 140, 100, 112, 88, 44, 80), Prod4 = c(311, 256, 231, 125, 78, 50, 32, 15, 15, 12, 10), Prod5 = c(492, 468, 391, 368, 331, 342, 343, 441, 341, 352, 376), Prod6 = c(102, 193, 223, 169, 192, 114, 269, 106, 128, 139, 118), Prod7 = c(96, 56, 23, 109, 135, 150, 132, 145, 72, 77, 155) ) #timeseries tsdataraw < ts( clean_data, start = c(2013,4),end = c(2019,3), frequency = 12) #> Error in is.data.frame(data): object 'clean_data' not found tsdata_ < matrix(NA, nrow = 72, ncol = ncol(tsdataraw)) #> Error in ncol(tsdataraw): object 'tsdataraw' not found i < 1 for (i in 1:ncol(tsdataraw)) { tryCatch({ tsdata_[, i] < tsclean(tsdataraw[, i]) }, error = function(e){cat("ERROR", "SKU",j,"ABU", i, "\n")}) } #> Error in ncol(tsdataraw): object 'tsdataraw' not found tsdata < ts(tsdata_, start = c(2013,4), end = c(2019,3), frequency = 12) #> Error in is.data.frame(data): object 'tsdata_' not found fcts < window(tsdata, start = c(2013, 4), end = c(2018, 3)) #> Error in window(tsdata, start = c(2013, 4), end = c(2018, 3)): object 'tsdata' not found fcts2 < window(tsdata, start = c(2018,4)) #> Error in window(tsdata, start = c(2018, 4)): object 'tsdata' not found fc_ets < matrix(NA, nrow = 12, ncol = ncol(fcts)) #> Error in ncol(fcts): object 'fcts' not found fc_arima < matrix(NA, nrow = 12, ncol = ncol(fcts)) #> Error in ncol(fcts): object 'fcts' not found fc_autoarima < matrix(NA, nrow = 12, ncol = ncol(fcts)) #> Error in ncol(fcts): object 'fcts' not found fc_naive < matrix(NA, nrow = 12, ncol = ncol(fcts)) #> Error in ncol(fcts): object 'fcts' not found fc_rwdrift < matrix(NA, nrow = 12, ncol = ncol(fcts)) #> Error in ncol(fcts): object 'fcts' not found fc_tbats < matrix(NA, nrow = 12, ncol = ncol(fcts)) #> Error in ncol(fcts): object 'fcts' not found fc_hwadditive < matrix(NA, nrow = 12, ncol = ncol(fcts)) #> Error in ncol(fcts): object 'fcts' not found fc_hwmultiplicative < matrix(NA, nrow = 12, ncol = ncol(fcts)) #> Error in ncol(fcts): object 'fcts' not found fc_stlf < matrix(NA, nrow = 12, ncol = ncol(fcts)) #> Error in ncol(fcts): object 'fcts' not found ma_ < matrix(NA, nrow = 60, ncol = ncol(fcts)) #> Error in ncol(fcts): object 'fcts' not found wma_ < matrix(NA, nrow = 60, ncol = ncol(fcts)) #> Error in ncol(fcts): object 'fcts' not found fc_ma < matrix(NA, nrow = 1, ncol = ncol(fcts)) #> Error in ncol(fcts): object 'fcts' not found fc_wma < matrix(NA, nrow = 1, ncol = ncol(fcts)) #> Error in ncol(fcts): object 'fcts' not found accuracy_ets < matrix(NA, nrow = ncol(fcts), ncol = 7) #> Error in ncol(fcts): object 'fcts' not found accuracy_arima < matrix(NA, nrow = ncol(fcts), ncol = 7) #> Error in ncol(fcts): object 'fcts' not found accuracy_autoarima < matrix(NA, nrow = ncol(fcts), ncol = 7) #> Error in ncol(fcts): object 'fcts' not found accuracy_drift < matrix(NA, nrow = ncol(fcts), ncol = 7) #> Error in ncol(fcts): object 'fcts' not found accuracy_naive < matrix(NA, nrow = ncol(fcts), ncol = 7) #> Error in ncol(fcts): object 'fcts' not found accuracy_tbats < matrix(NA, nrow = ncol(fcts), ncol = 7) #> Error in ncol(fcts): object 'fcts' not found accuracy_hwa < matrix(NA, nrow = ncol(fcts), ncol = 7) #> Error in ncol(fcts): object 'fcts' not found accuracy_hwm < matrix(NA, nrow = ncol(fcts), ncol = 7) #> Error in ncol(fcts): object 'fcts' not found accuracy_stlf < matrix(NA, nrow = ncol(fcts), ncol = 7) #> Error in ncol(fcts): object 'fcts' not found accuracy_ma < matrix(NA, nrow = ncol(fcts), ncol = 7) #> Error in ncol(fcts): object 'fcts' not found accuracy_wma < matrix(NA, nrow = ncol(fcts), ncol = 7) #> Error in ncol(fcts): object 'fcts' not found fitts < list(ncol(fcts)) #> Error in ncol(fcts): object 'fcts' not found mapefc < matrix(NA, nrow = ncol(fcts), ncol = 11) #> Error in ncol(fcts): object 'fcts' not found skus < matrix(NA, nrow = 12, ncol = length(sku)) #> Error in matrix(NA, nrow = 12, ncol = length(sku)): object 'sku' not found skus[, j] < c(sku[j]) #> Error in eval(expr, envir, enclos): object 'sku' not found i < 1 for (i in 1:ncol(fcts)) { if (sum(is.na(tsdata_[, i])) == 72){next} tryCatch({ fitts[[i]] < stl(fcts[, i], s.window = 36) fc_ets[, i] < forecast(fitts[[i]], method = "ets", h = 12)$mean fc_arima[, i] < forecast(fitts[[i]], method = "arima", h = 12)$mean fc_autoarima[, i] < forecast(auto.arima(fcts[, i]), h = 12)$mean fc_rwdrift[, i] < forecast(fitts[[i]], method = "rwdrift", h = 12)$mean fc_naive[, i] < forecast(fitts[[i]], method = "naive", h = 12)$mean fc_tbats[, i] < forecast(tbats(fcts[, i]), h = 12)$mean fc_hwadditive[, i] < forecast(HoltWinters(fcts[, i], seasonal = "additive"), h = 12, prediction.interval = TRUE)$mean fc_hwmultiplicative[, i] < forecast(HoltWinters(fcts[, i], seasonal = "multiplicative"), h = 12, prediction.interval = TRUE)$mean fc_stlf[, i] < stlf(fcts[, i], h = 12)$mean ma_[, i] < filter(fcts[, i], filter = (1/3)*c(0,1,1,1), sides = 1) wma_[, i] < filter(fcts[, i], filter = (1/10)*c(0,5,3,2), sides = 1) fc_ma[, i] < forecast(ma_[, i], h = 1)$mean fc_wma[, i] < forecast(wma_[, i], h = 1)$mean accuracy_ets[i, ] < accuracy(fc_ets[, i], fcts2[, i]) accuracy_arima[i, ] < accuracy(fc_arima[, i], fcts2[, i]) accuracy_autoarima[i, ] < accuracy(fc_autoarima[, i], fcts2[, i]) accuracy_drift[i, ] < accuracy(fc_rwdrift[, i], fcts2[, i]) accuracy_naive[i, ] < accuracy(fc_naive[, i], fcts2[, i]) accuracy_tbats[i, ] < accuracy(fc_tbats[, i], fcts2[, i]) accuracy_hwa[i, ] < accuracy(fc_hwadditive[, i], fcts2[, i]) accuracy_hwm[i, ] < accuracy(fc_hwmultiplicative[, i], fcts2[, i]) accuracy_stlf[i, ] < accuracy(fc_stlf[, i], fcts2[, i]) accuracy_ma[i, ] < accuracy(ma_[, i], fcts[, i]) accuracy_wma[i, ] < accuracy(wma_[, i], fcts[, i]) mapefc[i, ] < c(accuracy_ets[i, 5], accuracy_arima[i, 5], accuracy_autoarima[i, 5], accuracy_drift[i, 5], accuracy_naive[i, 5], accuracy_tbats[i, 5], accuracy_hwa[i, 5], accuracy_hwm[i, 5], accuracy_stlf[i, 5], accuracy_ma[i, 5], accuracy_wma[i, 5]) df_1 < data.frame(SKU = sales_data[, j], ABU = sales_data[i], Date = c("01042018", "01052018", "01062018", "01072018", "01082018", "01092018", "01102018", "01112018", "01122018", "01012019", "01022019", "01032019"), ETS = fc_ets[,i], Arima = fc_arima[, i], Auto.Arima = fc_autoarima[, i], Drift = fc_rwdrift[, i], Naive = fc_naive[, i], TBATS = fc_tbats[, i], HoltWinters_Additive = fc_hwadditive[, i], HoltWinters_Multiplicative = fc_hwmultiplicative[, i], STLF = fc_stlf[, i], MA = fc_ma[, i], WMA = fc_wma[, i]) df_2 < data.frame(SKU = sales_data[j], ABU = sales_data[i], Date = "MAPE", ETS = mapefc[i, 1], Arima = mapefc[i, 2], Auto.Arima = mapefc[i, 3], Drift = mapefc[i, 4], Naive = mapefc[i, 5], TBATS = mapefc[i, 6], HoltWinters_Additive = mapefc[i, 7], HoltWinters_Multiplicative = mapefc[i, 8], STLF = mapefc[i, 9], MA = mapefc[i, 10], WMA = mapefc[i, 11]) df_ < rbind(df_1, df_2) }, error = function(e){cat("ERROR", "SKU",j,"ABU", i, "\n")}) write.table(list(df_), file = "C:/Users/forecasted_data.csv", append = TRUE, quote = FALSE, sep = ",", col.names = TRUE, row.names = FALSE) } #> Error in ncol(fcts): object 'fcts' not found
^{Created on 20191115 by the reprex package (v0.3.0)} Reply

Time series model predicting one step ahead of target
I have a problem that I've been sitting with for a while and I've tried creating various models but keep getting the same problem... Whenever I build a model for stock market and predict upon it all my predictions are are literally predicting n step too late for every window I add and this doesn't make sense to me and I mean this is useless for practice :(:(:(
I've created the data with one shift and removing every second row so that there are no duplicates which it works moderately fine when predicting. But when I then predict for every day, it once again shifts one ahead!
So I've looked at many other people examples on the internet and replicated it yet I get the exact same problem which makes even less sense!?!? So my question is whether it's supposed to predict this way(since it happens all the time) and if not what am I doing wrong.
Here's a link where I got most of my data(I just converted it into daily): EURUSD historical data No need to actually get it, it's just for reproducibility (just look at the graphs and tell me whats wrong please!) Here's a short version of my code in creating the model/graph:
df = pd.read_csv('x2019#EURUSD.csv', index_col=0) df.columns=['Close'] forecast_len=1 df['Predicted'] = df[['Close']].shift(forecast_len) # if want to remove every second row: # df_full = df.copy() # df = df.iloc[::2] x=np.array(amazon.drop(['Predicted'],1)) y=np.array(amazon['Predicted']) x=x[:forecast_len] y=y[:forecast_len] x_train,x_test,y_train,y_test=train_test_split(x,y,test_size=0.2, shuffle=False) lr=LinearRegression() lr.fit(x_train,y_train) lr_confidence=lr.score(x_test,y_test) print(f"Linear Regression Confidence: {round(lr_confidence*100,2)}%") targets = y_test preds = lr.predict(x_test) start = 0 end = 1 plt.figure(figsize=(20, 10)) plt.plot(targets[start:end], label='Targets') plt.plot(np.array(np.array(preds[start:end])), label='Predictions') plt.legend()
Thanks in advance!

Forecasting with small data set using Gradient boosting
I want to do monthly sales forecasting for 10 items. I have 3 cycles of data i.e. 3*12*10=360 records. As Each item sales' pattern is different. Can I build one single classification model like Gradient Boosting for it?

How do I get Excel to return a value based on the max value in a table?
In the table the largest value is 1.71, how do i return 2.25 and 235?

I want to calculate the XIRR of data
I am trying to take a column of dates and corresponding values and use the built in XIRR function and I get an error each time. The dates are stored in column H and start on row 4. The values are in column I and start on row 4 also.
Sub XIRR() Dim wb As Workbook Set wb = ActiveWorkbook Dim wsLedger As Worksheet Set wsLedger = wb.Sheets("Cashflows") Dim i As Integer Dim n As Integer Dim j As Integer Dim MyXIRR as Double Dim dateArrary() As Date Dim valArray() As Double 'resize based on how much data there is n = wsLedger.Range("H:H").Find(what:="*", searchdirection:=xlPrevious).Row ReDim dateArray(n) ReDim valArray(n) ReDim dateStrings(n) 'if there is data then calculate the XIRR If IsEmpty(wsLedger.Range("H" & 4).Value) = False And IsEmpty(wsLedger.Range("I" & 4).Value) = False Then 'load the dates in an array For i = 4 To n j = i  4 dateArray(j) = wsLedger.Range("H" & i).Value Next i 'load the values in an array For i = 4 To n j = i  4 valArray(j) = wsLedger.Range("I" & i).Value Next i End If MyXIRR = Application.WorksheetFunction.XIRR(valArray, dateArray, 0.1) End Sub
I get the error "Runtime error '1004': Unable to get the Xirr property of the WorksheetFunction class". And I do have at least one negative and one positive value in my valArray.

How do I use WorksheetFunction.Text to convert a date from M/D/YY to YYYYMMDD?
I have a date in cell B7 in my worksheet that is formated as 9/1/2019, I want to convert it to a text string in the format YYYYMMDD or 20190901. I tried using WorksheetFunction.Text and searched the web for examples, but my code just generated a "True" result instead of a reformatted date.
This code did not work:
Dim FormattingValue As String Dim FormattingResult As String FormattingValue = WS1.Range("B7").Select FormattingResult = WorksheetFunction.Text(FormattingValue, "YYYYMMDD") Debug.Print FormattingResult
The Debug.Print returns the value "True" and not a date in the YYYYMMDD format.
The code below does work thanks to the contributors: Dim FormattingResult As String FormattingResult = Format(WS1.Range("B7").Value, "YYYYMMDD") Debug.Print FormattingResult This successfully converted 11/5/19 to 20191105