February 17, 2009 at 11:44 pm
Hi All,
I need to convert the below Excel formula into sql format.
{=(PRODUCT(1+$G4:G$6/100)^(12/COUNT($G$4:$G$15))-1)*100}
Assume
G4 = 1.05
G5 = 1.89
G6 = 2.08
G7 = 2.55
G8 = 1.99
G9 = 3.09
G11= 4.32
G12= 3.12
G13= 3.87
G14= 2.90
G15= 1.33
In Sql,
Table Name:
create table Fund
(
Perf_Val decimal(4,2)
)
insert into Fund
select 1.05
union
select 1.89
union
select 2.08
union
select 2.55
union
select 1.99
union
select 3.09
union
select 4.32
union
select 3.12
union
select 3.87
union
select 2.90
union
select 1.33
Inputs are welcome!
karthik
February 18, 2009 at 12:20 am
the above exl formula not working
February 19, 2009 at 1:44 pm
The Excel formula DOES work when fixing this typo
[font="Courier New"](PRODUCT(1+$G4;G$6/100)[/font]
By replacing the semi-colon character with a comma.
[font="Courier New"](PRODUCT(1+$G4,G$6/100)[/font]
Let me look at an SQL solution, I'll get back to you.
February 19, 2009 at 2:07 pm
It might be worth mentioning the value you are expecting. For example, in Excel's Function Window, I get "0.000412776" but it'll error in the "1+$G4:G$6/100" section when you actually use it. With sum() around G4:G6, it'll return 5.02 but not sure if that's what you're after either. I'll leave to SQL Conversion to someone who knows what they're doing though.
February 19, 2009 at 2:14 pm
Given that the order of data in a relational database normally has no meaning and that you have only values in the table you created, you're lacking a method for assigning any of the values to any of the variables in the formula. Posting the actual table definition and the method whereby you determine which value is G4 would be helpful.
February 19, 2009 at 2:16 pm
This is precisely what I am working on right now.
February 19, 2009 at 2:21 pm
I'd say J would either create a second column (or table, I guess) to differntiate the two groups, adding together those in Group 1 and Counting those in both Groups for the two values required. The G4 etc is meaningless as it'll just be the first value in Group 1, important in Excel, but yes, meaningless in J's solution.
February 19, 2009 at 2:33 pm
Yup !
Here goes, not fully completed yet
[font="Courier New"]CREATE TABLE Fund
(
EntryName varchar(5) NOT NULL PRIMARY KEY,
Perf_Val decimal(4,2),
Result decimal(4,2)
)
INSERT INTO Fund (EntryName, Perf_Val)
SELECT 'G4', 1.05 UNION
SELECT 'G5', 1.89 UNION
SELECT 'G6', 2.08 UNION
SELECT 'G7', 2.55 UNION
SELECT 'G8', 1.99 UNION
SELECT 'G9', 3.09 UNION
SELECT 'G11', 4.32 UNION
SELECT 'G12', 3.12 UNION
SELECT 'G13', 3.87 UNION
SELECT 'G14', 2.9 UNION
SELECT 'G15', 1.33
select * from Fund order by EntryName
DECLARE @lf_Result decimal(8, 4), @lf_Exponent decimal(8, 4)
SELECT @lf_Result =
(1 + (SELECT Perf_Val FROM Fund WHERE EntryName = 'G4'))
*
( ((SELECT Perf_Val FROM Fund WHERE EntryName = 'G6') / 100.00) )
SELECT @lf_Result
SELECT @lf_Exponent = (11 - 1) * 100
SELECT @lf_Exponent[/font]
SELECT @lf_Result = @lf_Result ^ @lf_Exponent
Now, the expression [font="Courier New"]COUNT($G$4:$G$15)[/font] in Excel evaluates to 11. It would be produced by someone highlighting cells G4 to G15. The $ character means that G4 remains G4 when you copy the formula on some other line or column. But since you are only interested in a COUNT, it always evaluates the same...
Regards
February 19, 2009 at 2:43 pm
And that was just a litteral translation ...
In SQL, G4 does not mean anything as already mentioned.
One much smarter way to go about it would be to use meaningful names instead of cell coordinates.
Then, you could improve your table design this way
[font="Courier New"]CRREATE TABLE Fund
....(
........pk int PRIMARY KEY NOT NULL IDENTITY,
........Month_01 decimal(4,2),
........Month_02 decimal(4,2),
........Month_03 decimal(4,2),
........Month_04 decimal(4,2),
and so on
........Month_12 decimal(4,2)
)[/font]
After that, a single select would be sufficient.
[font="Courier New"]
SELECT @lf_Result =
(1 + (SELECT Perf_Val FROM Fund WHERE EntryName = 'G4'))
*
( ((SELECT Perf_Val FROM Fund WHERE EntryName = 'G6') / 100.00) )[/font]
simply becomes
[font="Courier New"]SELECT
(1 + Month_1)
*
( (Month_6 ) / 100.00) )
FROM Fund [/font]
Your calculation statement could be a stored procedure applied in a single swoop to any complete record in table Fund.
Now you have finally stepped away from Excel and into real SQL.
Regards
February 19, 2009 at 4:19 pm
Well, tried to produce it using my Logic and J's original Code (First Table I've ever created using a query, though I've had plenty of experience writing SELECT statements to produce info to my Business). I'm getting "Operand data type numeric is invalid for boolean XOR operator." and can't work out the issue from Google. I also can't seem to get working to delete the table if it initially exists which is annoying.
Anyway:
declare @lf_group1 as float
declare @lf_exponent as float
declare @result as float
--DELETE * FROM [ODS_ASSET].[dbo].[Fund]
CREATE TABLE dbo.Fund
(
EntryName varchar(5) NOT NULL PRIMARY KEY,
Perf_Val float,
Group12 float
)
INSERT INTO Fund (EntryName, Perf_Val, Group12)
SELECT 'G4', 1.05,1 UNION
SELECT 'G5', 1.89,1 UNION
SELECT 'G6', 2.08,1 UNION
SELECT 'G7', 2.55,2 UNION
SELECT 'G8', 1.99,2 UNION
SELECT 'G9', 3.09,2 UNION
SELECT 'G11', 4.32,2 UNION
SELECT 'G12', 3.12,2 UNION
SELECT 'G13', 3.87,2 UNION
SELECT 'G14', 2.9,2 UNION
SELECT 'G15', 1.33,2
select @lf_group1 = (select sum(perf_val) from dbo.fund where group12 = 1)
select @lf_exponent = (select count(perf_val) from dbo.fund)
select @result = ((1+@lf_group1/100)^(12/@lf_exponent)-1)*100
select @result
February 19, 2009 at 4:58 pm
This is the line causing the error:
select @result = ((1+@lf_group1/100)^(12/@lf_exponent)-1)*100
The ^ character represents the XOR operator in TSQL, not the exponential operator that it represents in VB. In TSQL, you need to use the POWER function instead.
February 19, 2009 at 5:11 pm
Ah, thanks! It never occured to me that would be the Problem. In that case, I get '100.446271221111' as the answer:
--IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_name = 'dbo.Fund') DROP TABLE dbo.Fund
declare @lf_group1 as float
declare @lf_exponent as float
declare @result as float
--DELETE * FROM [ODS_ASSET].[dbo].[Fund]
CREATE TABLE dbo.Fund
(
EntryName varchar(5) NOT NULL PRIMARY KEY,
Perf_Val float,
Group12 float
)
INSERT INTO Fund (EntryName, Perf_Val, Group12)
SELECT 'G4', 1.05,1 UNION
SELECT 'G5', 1.89,1 UNION
SELECT 'G6', 2.08,1 UNION
SELECT 'G7', 2.55,2 UNION
SELECT 'G8', 1.99,2 UNION
SELECT 'G9', 3.09,2 UNION
SELECT 'G11', 4.32,2 UNION
SELECT 'G12', 3.12,2 UNION
SELECT 'G13', 3.87,2 UNION
SELECT 'G14', 2.9,2 UNION
SELECT 'G15', 1.33,2
select @lf_group1 = (select sum(perf_val) from dbo.fund where group12 = 1)
select @lf_exponent = (select count(perf_val) from dbo.fund)
select @result = (power(1+@lf_group1/100,(12/@lf_exponent)-1)*100)
select @result
February 19, 2009 at 5:14 pm
Also realised my Broken Drop Table Statement was because I had the Schema (Dbo) included. On just doing a straight select * on information.Schema I realised what it should look like (in case anyone goes to fix that for me):
IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'dbo' and table_name = 'Fund') DROP TABLE Fund
February 20, 2009 at 10:12 am
Regarding the use of the ^ in the formula, I did not look much further than what I had in the Excel formula.
Instead I concentrated on HOW to organize the database table so that it would respect the SQL way of doing this, i.e. a horizontal row holding all the fields as opposed to the "vertical" representation in Excel.
So forget about this crude
[font="Courier New"]INSERT INTO Fund (EntryName, Perf_Val, Group12)
SELECT 'G4', 1.05,1 UNION
SELECT 'G5', 1.89,1 UNION
SELECT 'G6', 2.08,1 UNION
SELECT 'G7', 2.55,2 UNION[/font]
This was just intended to show that a litteral translation form the Excel way into SQL statements was a poor way of implementing this in SQL.
Debugging the calculation was left as an exercice.
So, karthikeyan, does this help you at all ?
February 21, 2009 at 3:21 pm
nathanb (2/19/2009)
Also realised my Broken Drop Table Statement was because I had the Schema (Dbo) included. On just doing a straight select * on information.Schema I realised what it should look like (in case anyone goes to fix that for me):IF EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE table_schema = 'dbo' and table_name = 'Fund') DROP TABLE Fund
I use
IF Object_ID('dbo.Fund') IS NOT NULL
Much less typing. 🙂
_____________
Code for TallyGenerator
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply