2

I'm a student of Economic Sciences and i'm making a Program for my Final Paper about stock market. But, when i try to run my code in C# using EPPLUS, it doesn't working well. The STOCKHISTORY Formula isn't calculating and "@" is being inserted before the formula.

string Equity = "PETR4", FDate = "01/01/2019", LDate = "01/01/2022";
        using (ExcelPackage Excel = new ExcelPackage(new FileInfo("Stock.xlsx")))
        {
            var Plan = Excel.Workbook.Worksheets.Add("Base");
            Plan.Cells[1, 1].Value = FDate;
            Plan.Cells[1, 2].Value = LDate;
            Plan.Cells[1, 3].Value = Equity;
            Plan.Cells[2, 1].Formula = $"STOCKHISTORY(C1, A1, B1)";
            Excel.Workbook.Calculate();
            Excel.Save();
            Excel.Dispose();
        }

PrintProblem

D Stanley
  • 149,601
  • 11
  • 178
  • 240
Matheus Lira
  • 21
  • 1
  • formulas should start with an `=` – D Stanley Jan 20 '22 at 23:34
  • I tried, but it didn't work. Same Problem – Matheus Lira Jan 21 '22 at 18:37
  • Hmm that's strange - what if you don't use the dollar sign? I can't see why it would matter but it's not needed if you're not interpolating anything – D Stanley Jan 21 '22 at 18:47
  • STOCKHISTORY only seems to be available in Excel 365 - so it may not be supported by the more general Open XML format. It also does some unusual things like spilling its result into other cells, so you may still need to open the file in excel and remove the `@` manually. – D Stanley Jan 21 '22 at 18:57
  • Does this answer your question? [Excel VBA - How to add dynamic array formula](https://stackoverflow.com/questions/61138029/excel-vba-how-to-add-dynamic-array-formula) – GSerg Jan 21 '22 at 19:04
  • I didn't find support for Formula2 in Epplus. Does anyone know if it has? Or do I have to create a VBA Macro? – Matheus Lira Jan 22 '22 at 08:51
  • `.Formula2` is vba-related syntax. The point is to insert an array-formula instead. Don't know much about 'EPPLUS' but a quick search made me belief you can use `Plan.Cells[2, 1].CreateArrayFormula` somehow. It shouldn't be the cause of the `#NAME?` error though since dynamic arrays are only available in Excel365. – JvdV Jan 24 '22 at 14:35

0 Answers0