[Solved] How to get regression from an AOO Base Table

The Application Programming Interface and the OASIS Open Document Format

[Solved] How to get regression from an AOO Base Table

Postby misitu » Sun Oct 04, 2015 5:31 am

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).

So, is there something more elegant available using something like
Code: Select all   Expand viewCollapse view
"c:\Program Files (x86)\OpenOffice 4\program\soffice.exe" -headless -n "macro:///[library].[module].[macro]()"

which would read the table and spit out somewhere, in another table (or wherever I choose within the macro) the simple boolean I need?

Thanks in advance
David

Afterthought
It would be acceptable to have a macro that will [silently] read the database into a spreadsheet, create a chart, and figure out the trend from there, if the mechanisms exist to do so.
Last edited by misitu on Mon Oct 05, 2015 5:14 pm, edited 2 times in total.
User avatar
misitu
 
Posts: 91
Joined: Mon Dec 16, 2013 5:34 am
Location: Trujillo - La Libertad - Perú

Re: Macro to derive simple regression from an OO Base Table

Postby misitu » Sun Oct 04, 2015 6:29 am

Har!
I have located a formula for calculating the regression line so I guess I can include this in the macro to analyse the data in the OOBase table and derive the slope required as above.

In view that this might be helpful, if I get any further with this I'll return here with some details of the solution.

Best regards
David
OpenOffice 4.1.1
HSQLDB 2.3.4
Windows 7 HP / Windows 10
OOBasic
User avatar
misitu
 
Posts: 91
Joined: Mon Dec 16, 2013 5:34 am
Location: Trujillo - La Libertad - Perú

Re: Macro to derive simple regression from an OO Base Table

Postby Villeroy » Sun Oct 04, 2015 11:21 am

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26399
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to derive simple regression from an OO Base Table

Postby misitu » Sun Oct 04, 2015 3:26 pm

:D 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 :D
Anyway all the best. I may be back for some more in depth macro help !
Cheers
David
OpenOffice 4.1.1
HSQLDB 2.3.4
Windows 7 HP / Windows 10
OOBasic
User avatar
misitu
 
Posts: 91
Joined: Mon Dec 16, 2013 5:34 am
Location: Trujillo - La Libertad - Perú

Re: Macro to derive simple regression from an OO Base Table

Postby Villeroy » Sun Oct 04, 2015 4:17 pm

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, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26399
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Macro to derive simple regression from an OO Base Table

Postby Villeroy » Sun Oct 04, 2015 10:34 pm

Of course, somebody named Frank Lauer did that already 10 years ago.
Attachments
RegressionToolsDemo.ods
Regressions by user defined array functions (StarBasic)
(110.8 KiB) Downloaded 132 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26399
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Process (was: Macro) to get regression from an OO Base T

Postby misitu » Mon Oct 05, 2015 5:13 pm

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
OpenOffice 4.1.1
HSQLDB 2.3.4
Windows 7 HP / Windows 10
OOBasic
User avatar
misitu
 
Posts: 91
Joined: Mon Dec 16, 2013 5:34 am
Location: Trujillo - La Libertad - Perú

Re: SOLVED: how to get regression from an OO Base Table

Postby Villeroy » Mon Oct 05, 2015 6:11 pm

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:
Code: Select all   Expand viewCollapse view
Sub refreshImport()
e = ThisComponent.DatabaseRanges.createEnumeration()
while e.hasMoreElements()
dbr = e.nextElement()
dbr.refresh()
wend
End Sub
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26399
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SOLVED: how to get regression from an OO Base Table

Postby Villeroy » Mon Oct 05, 2015 6:47 pm

I have found the linear regression best fit equations and have written them into a SQL function so all solved.

which means that the whole thing has no connection to OpenOffice or any of its derivates.
Would you share your knowledge about the regressions in SQL?
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, OpenOffice 4.x & LibreOffice 6.x
User avatar
Villeroy
Volunteer
 
Posts: 26399
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: SOLVED: how to get regression from an OO Base Table

Postby misitu » Mon Oct 05, 2015 10:12 pm

er yes it does because I am using AOO and I had to choose which bit of AOO would work and all my data is in a set of AOO Base tables!

Therefore I need to work in AOO to get the analysis I require.

I will post an attachment as soon as I have finished tweaking the results. I am happy to share etc.

David

In the meantime, a couple of links to the source of my methodology.
OpenOffice 4.1.1
HSQLDB 2.3.4
Windows 7 HP / Windows 10
OOBasic
User avatar
misitu
 
Posts: 91
Joined: Mon Dec 16, 2013 5:34 am
Location: Trujillo - La Libertad - Perú

Re: SOLVED: how to get regression from an OO Base Table

Postby misitu » Tue Oct 06, 2015 11:47 pm

FOREX_LAST_27_DAYS_TREND function
based on the following www links
http://onlinestatbook.com/2/regression/intro.html
http://onlinestatbook.com/2/describing_bivariate_data/calculation.html

the tables concerned are
FOREX 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 1
I 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 falling
because if it is falling and a transfer is due then it ought to happen sooner
whereas 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 value
But for the purpose of finding the generalised trend (Down, Level, Up) only "b" is useful
I 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 concerned
Finally 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

Good Wishes

David

Code: Select all   Expand viewCollapse view
create function FOREX_LAST_27_DAYS_TREND()
returns table
   (
      "b(GBPUSD)" decimal(15,9),
      "b(USDPEN)" decimal(15,9),
      "last(GBPUSD)" decimal(10,8),
      "last(USDPEN)" decimal(10,8)
   )
specific FOREX_LAST_27_DAYS_TREND
reads SQL data
begin atomic
return table
   (
   select
      cast(GBP_USD_X_Y_OR_TREND_GROSS * STDDEV_GBPUSD / STDDEV_LOGGED as decimal(15,9)) as "b(GBPUSD)",
      cast(USD_PEN_X_Y_OR_TREND_GROSS * STDDEV_USDPEN / STDDEV_LOGGED as decimal(15,9)) as "b(USDPEN)",
      (select GBPUSD_XE from FOREX where DATE_LOGGED = (select max(DATE_LOGGED) from FOREX)) as "last(GBPUSD)",
      (select USDPEN_XE from FOREX where DATE_LOGGED = (select max(DATE_LOGGED) from FOREX)) as "last(USDPEN)"
   from
      (
      select
         (select stddev_samp(GBP_USD) from "View FOREX_LAST_27_DAYS") as STDDEV_GBPUSD,
         (select stddev_samp(USD_PEN) from "View FOREX_LAST_27_DAYS") as STDDEV_USDPEN,
         (select stddev_samp(datediff(DATE_LOGGED,date '1899-12-30')) from "View FOREX_LAST_27_DAYS") as STDDEV_LOGGED,
         GBP_USD_X_Y_OR_TREND_GROSS,
         USD_PEN_X_Y_OR_TREND_GROSS
      from
         (
         select
            sum("xy GBP_USD") as GBP_USD_X_Y_OR_TREND_GROSS,
            sum("xy USD_PEN") as USD_PEN_X_Y_OR_TREND_GROSS
         from
            (
            select
               power("normalised DATE_LOGGED",2) as "x**2 DATE_LOGGED"
               ,
               power("normalised GBP_USD",2) as "y**2 GBP_USD"
               ,
               power("normalised USD_PEN",2) as "y**2 USD_PEN"
               ,
               "normalised GBP_USD"*"normalised DATE_LOGGED" as "xy GBP_USD"
               ,
               "normalised USD_PEN"*"normalised DATE_LOGGED" as "xy USD_PEN"
            from
               (
               select
                  (
                     (
                        unix_timestamp(DATE_LOGGED)
                     -
                        (
                           select
                              avg(unix_timestamp(DATE_LOGGED))
                           from
                              "View FOREX_LAST_27_DAYS"   
                        )
                     )
                  /
                     (
                        select
                           stddev_samp(unix_timestamp(DATE_LOGGED))
                        from
                           "View FOREX_LAST_27_DAYS"   
                     )
                  ) as "normalised DATE_LOGGED"
                  ,
                  (   
                     (
                        GBP_USD
                     -
                        (
                           select
                              avg(GBP_USD)
                           from
                              "View FOREX_LAST_27_DAYS"   
                        )
                     )
                  /
                     (
                        select
                           stddev_samp(GBP_USD)
                        from
                           "View FOREX_LAST_27_DAYS"   
                     )
                  ) as "normalised GBP_USD"
                  ,
                  (
                     (
                        USD_PEN
                     -
                        (
                           select
                              avg(USD_PEN)
                           from
                              "View FOREX_LAST_27_DAYS"   
                        )
                     )
                  /
                     (
                        select
                           stddev_samp(USD_PEN)
                        from
                           "View FOREX_LAST_27_DAYS"   
                     )
                  ) as "normalised USD_PEN"
               from
                  "View FOREX_LAST_27_DAYS"
               )
            )
         )
      )
   );
end;
OpenOffice 4.1.1
HSQLDB 2.3.4
Windows 7 HP / Windows 10
OOBasic
User avatar
misitu
 
Posts: 91
Joined: Mon Dec 16, 2013 5:34 am
Location: Trujillo - La Libertad - Perú


Return to UNO API and ODF

Who is online

Users browsing this forum: No registered users and 0 guests