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

Creating a macro - Writing a Script - Using the API (OpenOffice Basic, Python, BeanShell, JavaScript)
Post Reply
User avatar
misitu
Posts: 102
Joined: Mon Dec 16, 2013 5:34 am
Location: Trujillo - La Libertad - Perú

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

Post by misitu »

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

"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: 102
Joined: Mon Dec 16, 2013 5:34 am
Location: Trujillo - La Libertad - Perú

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

Post by misitu »

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
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Post by Villeroy »

Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
misitu
Posts: 102
Joined: Mon Dec 16, 2013 5:34 am
Location: Trujillo - La Libertad - Perú

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

Post by misitu »

: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
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Post by Villeroy »

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Post by Villeroy »

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 499 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
misitu
Posts: 102
Joined: Mon Dec 16, 2013 5:34 am
Location: Trujillo - La Libertad - Perú

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

Post by misitu »

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
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Post by Villeroy »

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

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

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

Post by Villeroy »

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 with LibreOffice 6.0, latest OpenOffice and LibreOffice
User avatar
misitu
Posts: 102
Joined: Mon Dec 16, 2013 5:34 am
Location: Trujillo - La Libertad - Perú

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

Post by misitu »

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: 102
Joined: Mon Dec 16, 2013 5:34 am
Location: Trujillo - La Libertad - Perú

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

Post by misitu »

FOREX_LAST_27_DAYS_TREND function
based on the following www links
http://onlinestatbook.com/2/regression/intro.html
http://onlinestatbook.com/2/describing_ ... ation.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

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
Post Reply