Got a credit card? use our Credit Card & Finance Calculators
Thanks to gvonge,Shelford,GrahamPlatt,gpadsa,Steffers0, for Donating to support the site
Copying Security Prices Automatically once Per Week
Copying Security Prices Automatically once Per Week
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
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
-
- Lemon Quarter
- Posts: 3240
- Joined: December 7th, 2016, 9:09 pm
- Has thanked: 364 times
- Been thanked: 1070 times
Re: Copying Security Prices Automatically once Per Week
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.
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.
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.
-
- Lemon Half
- Posts: 8358
- Joined: November 4th, 2016, 11:20 am
- Has thanked: 926 times
- Been thanked: 4201 times
Re: Copying Security Prices Automatically once Per Week
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
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
-
- Lemon Quarter
- Posts: 4120
- Joined: November 4th, 2016, 9:24 am
- Has thanked: 3272 times
- Been thanked: 2859 times
Re: Copying Security Prices Automatically once Per Week
TerryWood wrote:
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
Re: Copying Security Prices Automatically once Per Week
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
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
Re: Copying Security Prices Automatically once Per Week
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 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
Re: Copying Security Prices Automatically once Per Week
It looks as the the attachments weren't sent. Is it being blocked?
Thanks
Thanks
-
- Lemon Quarter
- Posts: 4120
- Joined: November 4th, 2016, 9:24 am
- Has thanked: 3272 times
- Been thanked: 2859 times
Re: Copying Security Prices Automatically once Per Week
TerryWood wrote: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
Re: Copying Security Prices Automatically once Per Week
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
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
-
- Lemon Quarter
- Posts: 4120
- Joined: November 4th, 2016, 9:24 am
- Has thanked: 3272 times
- Been thanked: 2859 times
Re: Copying Security Prices Automatically once Per Week
TerryWood wrote: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
Return to “Financial Software - Discussion”
Who is online
Users browsing this forum: No registered users and 6 guests