Science

discussion

How to fold a light curve using Excel

Started by kianjin
Default_user
over 5 years ago
kianjin in response to pauldrye

Let me know how that works for you and I can walk you through any tricky bits, or refine it to act differently if needed. Or if you're using Excel 2003 or earlier, let me know and I'll post the different instructions (it's mostly the same once you've figured out where the Active X controls are hiding in the older versions and get it drawn onto the sheet).

Wow, thanks a bunch, Paul!

The trouble is I'm using Excel 11 on Mac Snow Leopard - and it doesn't have ActiveX controls and although I also have Windows 7 I don't have Excel on that.

So this Excel only has Form controls - I did try to insert a Form spinner and got it to increment or decrement by 1, but then I realized it didn't do negative numbers, so will have to modify the formula slightly. Would Form controls work or are they different or inferior to ActiveX controls?

Finally I guess I can assign some kind of macro to it so that after I click on the spinner, after it changes the value the entire sheet is recalculated with this new value?

Default_user
over 5 years ago
pauldrye in response to kianjin

kianjin:

So this Excel only has Form controls - I did try to insert a Form spinner and got it to increment or decrement by 1, but then I realized it didn't do negative numbers, so will have to modify the formula slightly. Would Form controls work or are they different or inferior to ActiveX controls?

That's one reason it's inferior, and the other is that (at least on Windows, I assume Mac is the same), a form control has a hard-wired upper limit of 30,000 -- in other words, once the "factor everything by 1000" rule comes into play, you can only go +/- 15 days. Hopefully that's enough for you, but it's still annoying.

The other minor thing to watch out for is that if you use an ActiveX control what you type in the linked cell field is just "G1" or whatever, while with a form control it needs an absolute reference, e.g., "$G$1".

It shouldn't be necessary to use a macro to do the update, as all spreadsheets should recalculate automatically. I just checked it on my Windows version and it updates on its own as soon as the clicker drops its value back on to the sheet (as it should everytime you hit it up or down). If not, right-click the spinner to create a new macro in Visual Basic that reads like this:

Sub Spinner1_Change()

   Application.Calculate

End Sub

(Jeez, could PH make code blocks any smaller?)

Just be careful if you've been adding and deleting spinners on your sheet before trying this -- your spinner may not be named Spinner 1, which would change the name of the necessary macro! If you're not sure, click the spinner in Developer mode and (I think it might be called "Select Mode" in your version of Excel) and look above column A. The name of the spinner should be listed there, and you'll want to change the prefix of the macro's name in the code above to match whatever yours is called (less the space, since there's no spaces allowed in Visual Basic macro names).

If you can't get it working, feel free to save it as an xls or xlsm file and send it to me via DropBox or whatever. I can tidy it up pretty quickly and it should translate back to the Mac cleanly.

Default_user
over 5 years ago
kianjin in response to Greg*

greg_78_FR:

Hi,

getfits works well but then I've just a little problem with kfit :

kfits -mr kp4678171llc.fits >4678171.csv

IOError: [Errno 22] La syntaxe du nom de fichier, de rÚpertoire ou de volume est

incorrecte: 'kp4678171llc.fits'

meaning in english : incorrect syntax on the file name or directory.

It does not like the "*"

If I give the full name (only one file) it works. Did you have this problem ?

Greg

Hi Greg

I'm not sure what the problem is, but you must put the asterisk (*) in the right place.

kfits -mr kp*4678171*llc.fits >4678171.csv

The asterisk should be in the same place as shown above exactly. I'm using Windows 7. Is the asterisk behavior different in different versions of Windows?

Is the kfits.py script in the right directory? You should install it and the others into C:\Python27\Scripts. Python sets this up to be in your %PATH%, which is where the command-line looks for scripts to execute. Otherwise if you're in a different directory, Windows won't know where to find it.

In order to organize my files I usually put them into a directory named by the KID, so:

md 4678171

cd 4678171

getfits 4678171

kfits -mr kp*4678171*llc.fits >4678171.csv

The * is a wildcard and it will expand the expression into a list of files which match (and in alphabetical order) and then feed this to kfits itself. Otherwise you need to key in the entire name of each file in the right order.

Default_user
over 5 years ago
pauldrye in response to kianjin

kianjin:

I'm not sure what the problem is, but you must put the asterisk (*) in the right place.

kfits -mr kp*4678171*llc.fits >4678171.csv

The asterisk should be in the same place as shown above exactly. I'm using Windows 7. Is the asterisk behavior different in different versions of Windows?

Even Microsoft is not insane enough to change wildcard behaviour as it has always been. I think they found asterisks and question marks on clay tablets dug out of Mesopotamian burial mounds.

Default_user
over 5 years ago

Hi,

I'm on Win7 64 bits, and when I look in kfits.py I can see that :

if sys.platform == 'win64': \# windows does not do wildcard expansion, so expand it ourselves

    import fnmatch

    fnargs=[]

    for i in range(0,len(args)):

        fnargs += fnmatch.filter(os.listdir('.'),args[i])

Maybe my problem comes from that because I'm used to deal with wildcard on Linux systems at my work.

Greg

Edit : Under a Linux virtual machine, it's working.

Default_user
over 5 years ago
kianjin in response to Greg*

greg_78_FR:

Hi,

I'm on Win7 64 bits, and when I look in kfits.py I can see that :

if sys.platform == 'win64': # windows does not do wildcard expansion, so expand it ourselves

import fnmatch

fnargs=[]

for i in range(0,len(args)):

fnargs += fnmatch.filter(os.listdir('.'),args[i])

Maybe my problem comes from that because I'm used to deal with wildcard on Linux systems at my work.

Greg

Edit : Under a Linux virtual machine, it's working.

Greg, that's strange, my Windows setup is Windows 7 Ultimate 64-bit, and the wildcard expansion code does work. But I'll admit it was a bit of a kludge. I attribute it to some strange Windows interaction with Python or something.

It will be OK under Linux/OS X because the script was written to work in that environment. Un*x-like wildcard expansion works as expected and passes the list of matching filenames to the script. You can usually trust Microsoft to implement something different from the established norm.

Default_user
over 5 years ago

I'd been meaning to add this for some time.

If for some reason, you'd like to position the minima right at the 0.5 phase, it is easy to do that. You have to tweak the 'start' cell's value until it falls just right at 0.5. This is where Paul's spinner implementation is incredibly useful.

You can also solve for it using the formula, but you have to deal with inverse modulos, and I don't know how you can do that in Excel.

Default_user
over 5 years ago
pauldrye in response to kianjin

kianjin:

You can also solve for it using the formula, but you have to deal with inverse modulos, and I don't know how you can do that in Excel.

Wikipedia's article on inverse modulos is a bit over my head, but I believe it's saying that the algorithm for modulos is iterative, no? If that's the case then you'd need to code it using a loop in Visual Basic -- no way to do it in Excel itself. I found this:

<code>

Function ModInv(b As Long, m As Long) As Variant

' Returns the modular inverse of b Mod m using the

' Extended Euclidean Algorithm to compute integers x and y

' {x, y || b * x + m * y = GCD(b, m) == 1 (else the inverse does not exist)}

' i.e., Mod(x * b, m) = 1

' Algorithm from Junaid Majeed at

' http://www.codeproject.com/KB/recipes/eealgo.aspx



Dim ak As Long, ak1 As Long, ak2 As Long

Dim xk As Long, xk1 As Long, xk2 As Long

Dim yk As Long, yk1 As Long, yk2 As Long

Dim qk As Long, qk1 As Long, qk2 As Long



If Abs(GCD(b, m)) <> 1 Then

    ModInv = CVErr(xlErrValue)

    Exit Function

Else

    ak1 = b

    xk1 = 1

    yk1 = 0



    ak = m

    xk = 0

    yk = 1

    qk = Int(ak1 / ak)



    Do

        ak2 = ak1: ak1 = ak

        xk2 = xk1: xk1 = xk

        yk2 = yk1: yk1 = yk

        qk2 = qk1: qk1 = qk



        ak = ak2 - qk1 * ak1

        If ak = 0 Then Exit Do



        xk = xk2 - qk1 * xk1

        yk = yk2 - qk1 * yk1

        qk = Int(ak1 / ak)

    Loop

End If

ModInv = xk1

End Function

Function GCD(ByVal i1 As Long, ByVal i2 As Long) As Long

If i2 = 0 Then

    GCD = i1

Else

    GCD = GCD(i2, i1 Mod i2)

End If

End Function

</code>

Copy that into a Visual Basic module in the same workbook as you're using and that workbook will magically gain a new function called =MODINV(cell1,cell2) that calculates the inverse of b mod m where the first cell contains b and the second cell contains m.

Assuming Excel Mac handles add-ins the same way as Excel Win, if you paste it into a module in a blank worksheet you can save it as an add in, add the new add-in via the Add-Ins dialog, and have =MODINV available to all spreadsheets loaded on that computer, including ones made prior to the creation of the add-in.

EDIT: OHGOD I HATE PLANETHUNTERS' CODE BLOCK HANDLING ಥ_ಥ

Default_user
over 5 years ago

Gnumeric is a free spreadsheet that works like Excel (even reads and write Excel files) but it's free. So, of course, it's not as polished as Excel but works under Windows and you can't beat the price!

Default_user
over 5 years ago
pauldrye in response to thaumielx72

thaumielx72:

Gnumeric is a free spreadsheet that works like Excel (even reads and write Excel files) but it's free. So, of course, it's not as polished as Excel but works under Windows and you can't beat the price!

Ehhhhh, I have to use Excel for work -- I teach it for a living, along with the rest of Office -- so I've just got it kicking around anyway. But yes, some of the free programs work quite well. Open Office can even (kind of) handle Visual Basic macros.

Please Log In to make comments.