This page demonstrates the database of solar panel costs that is used from the PVINSIGHTS website. You can use the code in the database to continually update the data and evaluate current prices. The database demonstrates a dramatic decline in the cost of solar modules. The solar price database also demonstrates how you can scrape data and create a time series database.
The solar cost database that you can update is available for download by clicking on the button below. After opening the excel file you can click on a couple of buttons and update the database as describe below. Because of the manner in which downloads from different dates are retained, you have to also re-format the database.
Using and Updating the Solar Cost Database
If you want to use the file to upload trends in solar data you can follow the instructions below. These instructions walk through downloading data to formatting data to using the graphs. To illustrate how the process works, I start with the end of the file. The sheets at the end of the file include the downloaded data from PVINSIGHTS at different dates — note that the date is part of the sheet name. The format of the PVINSIGHTS presentation has changed over time (for example the name Chinese poly silicon was changed) meaning finding the data from each sheet and recording the data in a summary sheet has changed. The format of PVINSIGHTS panel prices that you can find in the spreadsheet is shown in the screenshot below.
To get the data, you go to the page named menu and press the menu button. The macro attached to this button uses the WORKBOOKS.OPEN function and puts the downloaded data in a new sheet. The new sheet is re-named using the date that you download the file. The screenshot demonstrates where you can press the button to read new data.
You are not finished after reading the data. You must add a column to re-format the data and then copy the sheet name so that you can use the INDIRECT function. The INDIRECT function needs the sheet name and the MATCH function needs the name in the sheet to look for — the poly silicon modules cost and the thin-film cost. To do this in an automated way, you can go to the database sheet and press the button for re-formatting the sheet.
After re-formatting the data you can look at the graphs and the summary. The summary just uses the TRANSPOSE function and puts in #N/A for data future dates. The #N/A is used for the x-axis of the graphs.
Finally, you can look at the graphs. The graphs of module prices and module prices along with silicon prices are illustrated below. The first graph demonstrates just how dramatic the reduction in price for solar modules has been. In the year of the graph for 2010, the
Technical VBA Code for Reading PVINSIGHTS
The VBA code for reading the data is below.
Sub read_file() ' Reads PVinsight base_status = Application.Calculation Application.Calculation = xlCalculationSemiautomatic Application.DisplayAlerts = False base_book = ActiveWorkbook.Name ' Define current workbook as ususal base_sheet = ActiveSheet.Name base_cell = ActiveCell.Address num_sheet = Sheets.Count Workbooks.Open (Range("url")) ' Read PV Insights temp_book = ActiveWorkbook.Name ' name of new book read sheet_name = ActiveSheet.Name ' name of sheet name date_time = Now() ' Get Current Date date_time1 = WorksheetFunction.text(date_time, "dd mm yy") ' Put Date in Format sheet_name = sheet_name & " " & date_time1 ' Combine Sheet Name with date ActiveSheet.Name = sheet_name ' Re-name Sheet On Error Resume Next Sheets(sheet_name).Move After:=Workbooks(base_book).Sheets(num_sheet) ' Move Sheet to this workbook Workbooks(base_book).Activate Workbooks(temp_book).Close ' Close the Workbook read Format_New_Read Application.Calculation = xlCalculationSemiautomatic End Sub
Technical VBA Code for Re-formatting the Data
The VBA code for re-formatting the data is below.
Sub Format_New_Read() ' ' Macro1 Macro ' Application.Calculate Application.CalculateFullRebuild Select Case Range("last_col") ' Get the column letter for the last column read Case 10: col_name = "J" Case 11: col_name = "K" Case 12: col_name = "L" Case 13: col_name = "M" Case 14: col_name = "N" Case 15: col_name = "O" Case 16: col_name = "P" Case 17: col_name = "Q" End Select range_name = col_name & ":" & col_name 'Make entire col ' Columns(range_name).Select Selection.Copy Cells(1, Range("last_col") + 1).Select ActiveSheet.Paste Application.CutCopyMode = False ' Range("last_sheet_name").Select Application.CutCopyMode = False Selection.Copy Cells(Range("File_row"), Range("last_col") + 1).Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End Sub