Serving the Quantitative Finance Community

  • 1
  • 5
  • 6
  • 7
  • 8
  • 9
 
User avatar
Cuchulainn
Posts: 65000
Joined: July 16th, 2004, 7:38 am
Location: AV_63

Re: Excel tricks

June 8th, 2021, 6:28 pm

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 65000 == 2002 AV_63
 
User avatar
Collector
Posts: 5010
Joined: August 21st, 2001, 12:37 pm
Contact:

Re: Excel tricks

August 12th, 2021, 11:08 am

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?
 
User avatar
bearish
Posts: 6753
Joined: February 3rd, 2011, 2:19 pm

Re: Excel tricks

August 12th, 2021, 3:25 pm

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.
 
User avatar
Collector
Posts: 5010
Joined: August 21st, 2001, 12:37 pm
Contact:

Re: Excel tricks

August 12th, 2021, 4:16 pm

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.
 
User avatar
Collector
Posts: 5010
Joined: August 21st, 2001, 12:37 pm
Contact:

Re: Excel tricks

August 12th, 2021, 4:19 pm

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.
 
User avatar
bearish
Posts: 6753
Joined: February 3rd, 2011, 2:19 pm

Re: Excel tricks

August 12th, 2021, 4:49 pm

In the category of very clunky:

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

returns just plain i
 
User avatar
bearish
Posts: 6753
Joined: February 3rd, 2011, 2:19 pm

Re: Excel tricks

August 12th, 2021, 4:58 pm

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.
 
User avatar
Collector
Posts: 5010
Joined: August 21st, 2001, 12:37 pm
Contact:

Re: Excel tricks

August 12th, 2021, 9:00 pm

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 )
 
User avatar
quantie
Posts: 910
Joined: October 18th, 2001, 8:47 am

Re: Excel tricks

September 20th, 2021, 11:22 pm

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