Serving the Quantitative Finance Community

• 1
• 5
• 6
• 7
• 8
• 9

Cuchulainn
Posts: 17468
Joined: July 16th, 2004, 7:38 am
Location: Lviv

### Re: Excel tricks

I was confusing xlwings with XLw

https://xlw.github.io/

So, I take it back

Still, maybe a bit of redesign is an idea.........

many constraints from company's IT
non-sensical constraints?

As we discussed elsewhere, sometimes the ball of mud gets the job done.
I agree. I'm glad i don't have to maintain it.

My own journey way back with Excel was COM, ATL, Automation, C#, Xlopers etc.

My enduring memory was once a C++ course for quants and EXCEL_C++ interop in ATL no one got it working because IT froze access to the Registry without telling us...

// you pipped me to the post on the great balls of mud remarks.
You shake my nerves and you rattle my brain
Too much VB drives a man insane
You broke my will, but what a thrill
Goodness gracious, great balls of mud
planetoid 17300 == 2002 AV_63

Collector
Posts: 2534
Joined: August 21st, 2001, 12:37 pm
Contact:

### Re: Excel tricks

The IMSqrt(-1) returns in my Excel 6.1257422745431E-17+i

or IMSqrt(Complex(-1,0)) returns 6.1257422745431E-17+i

the answer should be 0+i, it seems like excel do some silly algorithm that rounds towards zero?

bearish
Posts: 4815
Joined: February 3rd, 2011, 2:19 pm

### Re: Excel tricks

The IMSqrt(-1) returns in my Excel 6.1257422745431E-17+i

or IMSqrt(Complex(-1,0)) returns 6.1257422745431E-17+i

the answer should be 0+i, it seems like excel do some silly algorithm that rounds towards zero?
That looks and feels awfully half baked. But the following calculation works:

=imreal(imexp(improduct(imsqrt(-1),pi())))

The result equals -1.

Collector
Posts: 2534
Joined: August 21st, 2001, 12:37 pm
Contact:

### Re: Excel tricks

but the Sqrt of -1 is not -1 but i in standard trans-number system, anyway thanks will look into if any help

IMaginary() should be -1 and IMReal() 0
Last edited by Collector on August 12th, 2021, 4:29 pm, edited 4 times in total.

Collector
Posts: 2534
Joined: August 21st, 2001, 12:37 pm
Contact:

### Re: Excel tricks

looks like mathematica do same
(-1)^0.5
returns:
6.12323*10^-17 + 1. I

so yes they use some aloe also to get approx zero. Or looks like one need write (-1.0)^0.5, returns i

and yes (-1)^(1/2) works it returns i  (the imaginary beast)

hemm any such simple solutions in excel? the  6.1257422745431E-17 output easily causes confusion in the blink of an eye.

bearish
Posts: 4815
Joined: February 3rd, 2011, 2:19 pm

### Re: Excel tricks

In the category of very clunky:

=imsum(1,imsqrt(-1),1)

returns just plain i

bearish
Posts: 4815
Joined: February 3rd, 2011, 2:19 pm

### Re: Excel tricks

It is also sort of worth noting that the type of the result of a complex number operation is “text”. This feels a bit like the outcome of somebody’s summer internship project.

Collector
Posts: 2534
Joined: August 21st, 2001, 12:37 pm
Contact:

### Re: Excel tricks

excel (hopless)

=imsum(1,imsqrt(-1),-1) =i
=imsum(1,imsqrt(-2),-1) =1.4142135623731i
=imsum(1,imsqrt(-3),-1) =1.73205080756888i
=imsum(1,imsqrt(-4),-1) =2.22044604925031E-16+2i  (mathematica (-4.0)^(1/2) = 0. + 2.i )

quantie
Posts: 20
Joined: October 18th, 2001, 8:47 am

### Re: Excel tricks

Excel only stores 15 digits for precision so FP arithmetic can give incorrect results
https://docs.microsoft.com/en-us/office ... ate-result

tagoma
Posts: 2238
Joined: February 21st, 2010, 12:58 pm

### Re: Excel tricks

Collector
Posts: 2534
Joined: August 21st, 2001, 12:37 pm
Contact:

### Re: Excel tricks

bearish
Posts: 4815
Joined: February 3rd, 2011, 2:19 pm

### Re: Excel tricks

I like it, a lot! It basically extends the functional programming paradigm that is a foundational concept of the original spreadsheet design to a wide range of real world applications.

bearish
Posts: 4815
Joined: February 3rd, 2011, 2:19 pm

### Re: Excel tricks

I didn’t mean that to sound like a piece of marketing speech, but I don’t think that it is a coincidence that a key driver of this development was Simon Peyton Jones.