Calculating Magnitudes and Making a CMD

Using a .csv File

The 'Aperture Photometry' box within Makali'i (see Figure 1) presents a lot of information and values in a layout that isn't particularly easy to read. By using the 'print' option for these values, a .csv (comma separated value) file can be created. This file can be opened in Microsoft Excel or Libre Office Calc, which places the values neatly into columns as can be seen in Figure 2. The instructions below include screenshots from Microsoft Excel 2010 with different versions of Excel having slightly different layouts.

Figure 1: Makali'i photometry window.
Credit: Fraser Lewis, Makali'i (NOAJ)

The .csv files that Makali'i creates have several columns in them (e.g. Figure 2) with data relating not just to the stars that you've clicked on but also the brightness of the sky around the stars, along with some interim values that the software makes. For each file, there are only a few columns that we need to create a CMD. For each filter, these are

  • A - Number - ideally, this will match the star numbers in the finder chart
  • B, C - Obj X, Obj Y - these are the X and Y co-ordinates for your star
  • Q - Result Count - this is the number of counts for a given star
Figure 2: CSV data for V-band.
Credit: Fraser Lewis


If you have been able to successfully perform photometry on the same number of stars (and most crucuallly, in the same order), the numbers that you have in columns B and C for your two .csv files will look very similar. The only reason these columns might not be identical is that the telescope may have drifted slightly between the two exposures. For example, star 1 in the V-band image (Figure 2) may have co-ordinates 304, 1005 whereas the B-band image may show as 304, 998 (see Figure 3). A difference of a couple of pixels here is acceptable, but if these values are different by more than this, it might be worth checking your measurements as this could indicate an error in your work. Another clue here would be if your two .csv files had different number of rows in them, suggesting that you haven't measured the same set of stars in each filter.

Figure 3: CSV data for B-band.
Credit: Fraser Lewis

Assuming your two files are the same size and represent the same stars in the same order, you are now ready to start work on your data. 

Calculating Magnitudes

For most open clusters, it should be possible to collect values for around 30-40 stars, each of which will then have an intensity value (in counts) for both the B and V. These can be converted to instrumental magnitudes using the following formula:

Magnitude = - 2.5 * log (counts / exposure time)

For example, the images of NGC957 are both 5 second exposures. Other images may have different exposure times (as shown in the FITS header) but generally, both images of the same cluster will have the same exposure time.

At this stage, it is perhaps easiest to open a new worksheet. From the V band .csv file, you can paste columns A, B, C and Q into columns A - D of your new worksheet. You can then select column Q from your B-band .csv file and paste it into column F of your new sheet (see Figure 4). Columns E, G and H are blank for now.

  • A - Star Number  
  • B - x
  • C - y
  • D - V intensity
  • E - V mag
  • F - B intensity
  • G - B mag
  • H - B-V colour


Figure 4: Excel sheet before calculations.
Credit: Fraser Lewis

Next, you can calculate values for the V magnitude column (column E) by typing the formula:


'=2.5*log(D/5)' where D is the corresponding value in column D.

This can be repeated for the B magnitude value into column G using 

'=2.5*log(F/5)' where F is the corresponding value in column F.

Finally, column H is calculated by subtracting column E from column G.

Figure 5: Excel sheet with calculations.
Credit: Fraser Lewis


Making Your CMD


Once you have measured all of your stars, you can plot a scatter graph with the V value (the star's brightness; column E) on the y-axis and the B-V value (the star's colour; column H) on the x-axis. You will need to reverse the values of the y axis, as the magnitude scale means that lower values are brighter than high ones. You can do this by right clicking on the y-axis, selecting the 'Format Axis' option, clicking the 'Scale' tab and checking the 'Values in reverse order' box.

You can now analyse your plot to see if any of the regions such as Main Sequence and Giant Branch are apparent. Finally, it is possible to take one step further with a CMD and display errorbars on the final plot, which are based on the uncertainties in the data.

Add uncertainties to your data.

Look at a theoretical CMD or compare it with that of NGC957.