Hello!I have some charts of Foreign Exchange rates. Currently I do an OO Calc TREND function to extend the past N days to the next N days. This tells me (¡obviously!) whether the rates are going up or down or staying neutral. To be precise, what the last N days have been. This gives a crude heuristic as to whether to do anything or not with the cash on the wrong side of the ocean ...I would like to code a bit of macro to simulate the TREND sufficiently to tell me whether the slope of the trendline is downwards or not, nothing more.

A sledgehammer walnut approach would be to register the spreadsheet with Base and grab the data that way, but that has several drawbacks not the least being timeliness (needs a manual hit to update).

Thanks for that, Villeroy.Apologies, not seeming to be less than grateful, but I am already in that zone. The problem being that I need to move on from that.

I think that I did not give enough detail in the original post. My bad.

As mentioned, now having the stats formula available I can make the calculation I need either in OO Basic or in SQL and the latter will probably be my instrument of choice as I understand it better Anyway all the best. I may be back for some more in depth macro help !CheersDavid

I can't understand why everybody insists in walking the most tedious and unpracticable path using spreadsheets as databases and databases as spreadsheets. Macros (particularly the ones written in Basic) are not a viable option to do any statistics. You can do it in SQL, you can do it in a spreadsheet and you can use dedicated statistical packages able to read your database but I doubt that you will ever finish your Basic macro simply because it is too absurd doing statistics in that obscure programming lingo of the 90ies.

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.Ubuntu 18.04, no OpenOffice, LibreOffice 6.x

Thanks, I already had that in place! My intention was to run the regression against a BASE table in order to use the results of the regression directly in another SQL process.As mentioned above, I have found the linear regression best fit equations and have written them into a SQL function so all solved. In this situation, the use of Spreadsheet as an intermediary is a distraction.Thanks for all your help!David

What can be easier than setting up a spreadsheet with some (hidden) formulas linked to a database range and a nice chart?A spreadsheet is an almost perfect report engine.The one and only macro you would need in addition to the user defined function:

the tables concerned areFOREX contains Foreign Exchange rates (manually) input daily by eyeball and fingers from the site http://www.xe.com/currencycharts/FOREX_LAST_27_DAYS contains the most recent 28 days from FOREX "normalised" relative to the first entry in each column as 1I used that to produce a nice graph with all the rates emanating and criss-crossing from a common origin

But what I needed more was a simple indication of whether or not the trend is fallingbecause if it is falling and a transfer is due then it ought to happen soonerwhereas otherwise I can wait a bit, it might go up a bit more(FX LOL)

There are two rates I need to know, GBPUSD (Great British Pounds to US Dollars) and USDPEN (USD to Peruvian Nuevos Soles)It could be more, it could be less (I might want to incorporate Euros later, for example)The returned data is "b" for the slope of the trend line and "last" for the last known valueBut for the purpose of finding the generalised trend (Down, Level, Up) only "b" is usefulI have ignored the Intercept ("A") here but it could be incorporated quite simply if required

I have used Sample stddev rather than Population because FX is a bit rubbish anyway as far as stats are concernedFinally there are a few redundant variables in here, I have provided the code "as is" rather than editing it down for ease of consumption

The X Y and other stuff relates directly to the nice book as linked above