Basic-Excel-R-Toolkit
Basic-Excel-R-Toolkit copied to clipboard
sdllc
It would be ideal to compile BERT independent of pre-installed R v3.5
Does anyone know how to build BERT from source?
It seems this question has been asked numerous times, of how to integrate BERT with a local/newer R environment (rather than using the pre-installed R v3.5). I wonder if anyone knows or is working on how to make BERT more barebone and make it work with whichever local version R is installed on the PC. Though it may not be as straightforward as thought based on what the stated build process implies (i.e. R may have to be packaged with BERT in the installation, so every time R is updated, BERT needs to be updated to work with it).
Other people and I have already run into problems using the built-in v3.5 of R when installing R packages. Many current R packages stopped working with an older version of R (< 3.6). This list will grow larger over time. And the fact that the BERT's R environment in the console is day and night in terms of UI compared to the interface of RStudio, highlights the potential quality of life and productivity gains from making BERT comptible with a local version of R.
I contacted the developer/company ~a few months ago~ back in mid 2020, but haven't heard a reply from them. And BERT hasn't been updated for the last ~2 years~ 4 years (as of 2022). In spite of the open source nature of BERT, it's possible that few people have the expertise to continue its development. I would give it a crack if the documentation was a bit more detailed than a few bullet points in the front Github page. Could anyone shed some light or help us out in providing more details in this regard? In the meantime, the first step might be to compile BERT with a newer version (R 3.6 or 4.1), if separating the BERT program from R in the compilation process is proved difficult.
#207 #206 #188 #184 #173 #124
That would be great since BERT is a very nice and pretty unique package. Fingers crossed..
I contacted the developer/company a few weeks ago as well, although I did not receive a respons yet.
Would it be an idea to post this as a project on Upwork and share the cost? Initially we can ask to compile BERT with a newer version of R?
The last BERT version that didn't include R is Preview Release 2.0.21 based on release notes & file size. I will check whether it's compatible with R 4.0 when I get the time.
It's worth looking at the source to understand how BERT works. The previous BERT version (2.0.21) would have some bugs that latter versions have fixed, but it might be a workaround for those who have trouble with the built-in R 3.5. And comparing the source files between the latest BERT version and 2.0.21's source files might elucidate why the newer BERT doesn't work with a local R version and whether it's possible to configure it to work with one without messing with re-compilation.
These are good points to identify the issue and can provide a good direction to solve it. I suppose it can be a considerable disadvantage to miss the upgrades after Release 2.0.21.
I have taken a look at the source files, although I do not have the expertise to make a comparison between the BERT versions and to re-compile it or to configure it in a way to work without re-compilation.
[..] why the newer BERT doesn't work with a local R version and whether it's possible to configure it to work with one without messing with re-compilation.
The latest BERT version can run with a local version of R, just not a version later than R 3.5.x unfortunately. I've been able to test it successfully with a local version of R 3.5.3.
That's good to know. So something in R since 3.6 broke compatibility. Perhaps, we could test which folders/files are the cause of it one by one.
I think it is pretty safe to say that this project is dead. The developers don't respond to email, haven't made updates to Github in years, and don't post here. Has anyone moved to anything similar to BERT given R is beyond v4.0 now?
I did find a developer via Upwork who estimates it will take him 45-50 hours to upgrade BERT to R version 4.0.3. His rate is $ 30 an hour. If we can share the cost with 5 users I would be interested.
I think it is pretty safe to say that this project is dead. The developers don't respond to email, haven't made updates to Github in years, and don't post here. Has anyone moved to anything similar to BERT given R is beyond v4.0 now?
Power BI lets you run R scripts directly. There are ways to run R functions/scripts using VBA or Typescript within Excel. But it's never going to be as easy as BERT. Running R 3.5 with BERT is still the most readily available and convenient way, at this point in the time.
BERT alternatives include Xlwings, ExcelDNA with R.Net, and RExcel. RExcel is even older than BERT, see if you can find all the necessary files to install it. Xlwings requires some Python knowledge, whereas ExcelDNA & R.Net requires some C#.
I don't use BERT much these days. While it's not exactly R, the new LAMBDA functions in Excel seem promising.
I use BERT to call a neuralnet (in Caret) and a gam (in mgcv) in R from VBA. The VBA script is running every second which sends the parameter values to R and receives the outcomes in Excel pretty fast. I suppose XLwings could be an alternative tool for me. Do you have experience with the repsonse times of XLwings? I can imagine it takes extra time since Python is an extra layer between Excel and R?
@marboe123 I might be interested in helping to fund a share of the refresh work. Then you only need three more?
@marboe123 I might be interested in helping to fund a share of the refresh work. Then you only need three more?
Hi Govert, that is great. Indeed only three more, although it would be even better if there would be more users to share the cost with I suppose?
has anybody tried this? http://ftp.uni-bayreuth.de/math/statlib/R/CRAN/contrib/extra/dcom/RSrv135.html i'm primarily interested in just using R functions in Excel. any first impressions? thanks
The best solution I found to keep using BERT in its current condition is this: Set the CRAN to be a MRAN snapshot from when version 3.5.x was the latest. I set this code in the functions.R:
local({r <- getOption("repos")
r["CRAN"] <- "https://cran.microsoft.com/snapshot/2019-04-15/"
options(repos=r)
})
You only have packages from April 2019, but all of them install without problems and compilation. You can also try installing packages from github to get newer stuff.
It would be interesting to know how acvelozo linked R-3.5.3 to BERT. When I change bert-config.json to read
"R": {
"lib": "C:\\PROGRA~1\\R\\R-3.5.2"
},
BERT opens, but when I look in BERT's R console, I find that it is still using R-3.5.0, not the requested 3.5.3 version.
@StatsMan1953 the "lib" parameter is the driectory that BERT is going to use for R packages. In order to change the version of R used by BERT, you need to change R's home directory using the "home" parameter. Here is an illustrative screenshot:
@marboe123 I might be interested in helping to fund a share of the refresh work? I can't get this thing to launch the console as the ribbon/as the com add-in is not found. Its very frustrating to work with outdated software, and it sounds like the developers have checked out years ago.
@34MidwayMonsta @govert In that case we are with 3 interested persons. It would be optimal if we find 2 more persons to share the cost. In the meantime my post on Upwork has expired and I don't have the contactdetails from the developer who reacted initially since these details were hidden. I suggest we wait till we have 2 more persons and post it again as a project on Upwork.
@vsin420 see my post above: 45-50 hours to upgrade BERT to R version 4.0.3. His rate is $ 30 an hour. Although the $ was much lower at that moment.
@marboe123 thanks for the reminder :) apologies it's been a while. i would like to contribute.
happy to contribute :) any progress on this per chance? cheers
On Tue, Apr 12, 2022 at 8:24 AM ODOU @.***> wrote:
I can also contribute
— Reply to this email directly, view it on GitHub https://github.com/sdllc/Basic-Excel-R-Toolkit/issues/194#issuecomment-1096074032, or unsubscribe https://github.com/notifications/unsubscribe-auth/ASH3ZQD7BBDKHFAS3CMFQ5LVEUCJPANCNFSM4QSPYPIA . You are receiving this because you were mentioned.Message ID: @.***>
@govert @ODOU @vsin420 @34MidwayMonsta Great, it seems we are with 5 persons if everyone is still interested? I will contact Upwork how we can organize this. I suppose it will be optimal if we can all pay our share to Upwork directly. After this I suggest I will make a new post on Upwork.
I contacted Upwork and it is not possible to split the payment in 5 parts and pay each part individually. Has someone experience with a set up for this situation?
Hi guys,
I was having the same problem as you. As BERT doesn't work with more recent versions of R, I had to stop using it. I decided then to develop my own Excel add-in with C# to call R from Excel. I mix @govert's ExcelDna and R.NET to achieve that. My focus is less on automating Excel (although it is possible by using the R package "excel.link") and more on developing Excel functions that can access R packages. I found that the new Excel Lambda functions are also valuable resources in this communication since part of data handling can be conducted either before or after calling R.
The GitHub is the following: https://github.com/edugca/xlRcode
It is in a very initial stage of development, and there are many issues to be solved. Right now, I'm trying to figure out a way of interrupting R.Net in the middle of a calculation (perhaps, pressing ESC) without shutting down Excel.
Best, Eduardo
Thanks for the hard work and for letting us know. It's wonderful we can something similar to BERT (and perhaps more) by using the latest available tools. I can't wait to check it out.
@edugca
https://www.virustotal.com/gui/file/6dc1df01f765bd69ad885f41b6d07fc4f2bc660141c21bffdf7f93ceb5dd9b75?nocache=1
Although most anti-virus don't flag it, xlRcode has been flagged as malware. Do you think you could take a look to see if this could be resolved?