Copying Security Prices Automatically once Per Week

[TerryWood]

Hello @ Lemon Fool

I have several portfolios based on either Hyptuss or Yahoo Simple Price Scrape by Kiloran using LibreOffice Calc.

I am now at the stage where updating my portfolio is either one or two clicks according to Hyptuss or Yahoo Simple Price Scrape usage.

I am wanting to start the process of producing charts so I have started a sheet in each portfolio named Price History.

Dates are along the top row
Security name is in the first column
Prices are against each security name under relevant date advancing to the right.

I want to achieve the following:-

When each spreadsheet is updated (Saturday morning) I want to automatically copy the list of prices after the update, to the list of prices on Price History tab and paste it under the relevant date. Each week the new date is in a column advancing to the right.

I don't know how to do this and I am not strong on macros. Are their formulas that can achieve this?

Thank you for your help.

Terry

[Urbandreamer]

I've got to admit that the title of this thread confused me.

You don't seem to be asking about doing something automatically once per week, but creating a macro to do it when you press a button.

I've not looked into editing the macro's in HYPTUSS, so can't help you with that.

What I can do is provide an answer to the threads title.

I have automated my google sheets spreadsheet to record data every weekday after the close of business.
It does this by copying the first 8 values on the second line of the second sheet into a new line added at the bottom of the sheet.

The feature is called AppScript and allows you to call functions that you write when trigger events happen.

The language used is based upon javascript, which uses java syntax, which is based upon c++ syntax.
LibreOffice uses python, which is based upon c++ syntax.

So it may be possible to use my code as a starting point for a button on your spreadsheet.

Code: Select all

function addProduct() {
  //skip if weekend
   var day = new Date();
    if (day.getDay()<6 && day.getDay()!=0) {
 
      var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
      var secondSheet = spreadsheet.getSheets()[1];
      var numColumns = 8;
      var firstOpenRow = secondSheet.getLastRow() + 1;
      var firstOpenRange = secondSheet.getRange(firstOpenRow, 1, 1, numColumns+1);
      var existingData = secondSheet.getRange(2, 1, 1, numColumns);
      for (var i = 0; i < numColumns; i++) {
        firstOpenRange.getCell(1, i+1).setValue(existingData.getCell(1, i+1).getValue());
      }
    firstOpenRange.getCell(1, numColumns+1).setValue(day.getDay());
    }
}


Ps, when I unitized my LibreOffice HYPTUSS, I created a sheet with pivot table of the stuff on the FTSE-HYP tracking page. I then created a chart of the pivot table on a new sheet. The chart could be used to display my relative performance over any period by changing the filter on the pivot table.

Hope that this helps.

[tjh290633]

A thought which occurs to me is the problem caused by the variation in share prices, AZN, for example, over £100 and VOD, less than 70p.

Do you propose to start with actual prices or to start the period with them all at 100%?

I produce a table of the change in price for each share in my portfolio, from the price at the start of the year. I only do the calculation about once per month but could do it more frequently. I could then manually copy the figures into a spreadsheet to generate the chart. The calculation involves several steps, but for your purposes a simple copy of the values into the new spreadsheet would suffice. (CTRL-C then ALT-ES in Libre Calc).

I wonder if you may be overcomplicating the problem?

TJH

[kiloran]

I want to achieve the following:-

When each spreadsheet is updated (Saturday morning) I want to automatically copy the list of prices after the update, to the list of prices on Price History tab and paste it under the relevant date. Each week the new date is in a column advancing to the right.

I don't know how to do this and I am not strong on macros. Are their formulas that can achieve this?

Thank you for your help.

Terry

From what you have described, I think I would just do a manual copy/paste. It's only once per week. I don't think there is any way to achieve what you want using built-in functions, it would have to be a macro.
I normally use Python for LibreOffice macros but I would not recommend this if you are a total beginner. If you really want to do this with a macro, I would recommend using the built-in StarBasic capability. The best guide to this is https://www.pitonyak.org/OOME_3_0.pdf

--kiloran

[TerryWood]

Hi Kiloran and Eveyone who contributed to this

Your help really is appreciated.

Believe it or not, I have sorted my problem using a macro. I know I said I was not Macro savvy but I decided to use the Macro recorder in LO Calc having first mapped out the steps I wanted to take.

It worked, albeit it took a long time because the Push Button icon was missing from the Form Control toolbar. I used the Text Box which gave me a number of fails until I realised about the missing Button.

So thanks again

Terry

[TerryWood]

Hi @ Lemon Fool

I spoke too soon. The Macro I put together using LibreOffice Calc Recorder works in part only.

When I clicked on the Macro button to update prices in Price History tab it updated correctly, but when I tried it again it did not work i.e. it did not move one column to the right and paste.

I attach three pdf files:
1) Prices Sheet
2) Price History Sheet
3) copy of the Macro produced by the recorder

The Macro copies the list of prices shown in Prices Sheet (D4 to D38) and should paste into Price History Sheet in the first empty column from Row 4 to Row 38. It should then return to and open Accounts Sheet. I click the Macro button every Saturday morning to get a weekly NetWorth

Could some knowledgeable member advise a) Why the Recorder macro version does not work and b) how do I fix it?

Thank you

[TerryWood]

It looks as the the attachments weren't sent. Is it being blocked?

Thanks

[kiloran]

Hi @ Lemon Fool

I spoke too soon. The Macro I put together using LibreOffice Calc Recorder works in part only.

When I clicked on the Macro button to update prices in Price History tab it updated correctly, but when I tried it again it did not work i.e. it did not move one column to the right and paste.

I attach three pdf files:
1) Prices Sheet
2) Price History Sheet
3) copy of the Macro produced by the recorder

The Macro copies the list of prices shown in Prices Sheet (D4 to D38) and should paste into Price History Sheet in the first empty column from Row 4 to Row 38. It should then return to and open Accounts Sheet. I click the Macro button every Saturday morning to get a weekly NetWorth

Could some knowledgeable member advise a) Why the Recorder macro version does not work and b) how do I fix it?

Thank you

I was very surprised when you said that the recorded macro worked. I can't see any way that a recorded macro can detect the first empty column. It will require some hand-crafted code.

LemonFool does not allow attachments. You can send the files to the email address at the bottom of https://lemonfoolfinancialsoftware.weebly.com/

It may take a while for me to respond. I've just got a shiny new laptop this morning and I'm busy setting it up, and fighting all the changes from Windows 10 to Windows 11. I want to set it up the way that I want, and not accept all the defaults that Microsoft tries to sucker me into. It's a battle of wills, and I will win, ultimately

--kiloran

[TerryWood]

Hi Kiloran

Attachments sent and thanks for your help. They are PDF, I could send the actual .ods file if absolutely necessary.

Good luck with Win 11. I am yet to take the plunge.

Terry

[kiloran]

Hi Kiloran

Attachments sent and thanks for your help. They are PDF, I could send the actual .ods file if absolutely necessary.

Good luck with Win 11. I am yet to take the plunge.

Terry

Yes, I've got the files Terry. I'll have a gander as soon as I can

--kiloran