Ajuster VBA pour importer la plage de données de la table web
Salut amis ,
J'ai trouvé un excellent code VBA pour importer des données directement d'Internet vers Excel .
Je veux vraiment utiliser le même code VBA mentionné précédemment, pour importer dans Excel les données du tableau de données Historical Data (avec 7 colonnes) .
S'il était également possible d'adapter le code VBA mentionné ci-dessus, pour importer des données avec des critères dans l'intervalle de périodes de mois-année à mois-année .
par Exemple :
importer uniquement les données de février 2020 à décembre 2019, c'est-à-dire définir une plage de périodes de mois-année à mois-année .
la feuille de calcul avec le code VBA et les données importées que j'ai mentionnées dans l'exemple est jointe .
merci d'avance .
Bonjour,
Dans ta spécification il manque le code génial que tu as trouvé (je voudrais voir cela !), le site web et les données que tu veux récupérer.
Tu peux aussi t'inspirer de ceci : https://forum.excel-pratique.com/viewtopic.php?p=838401#p838401
Bonjour ,
Désolé, j'ai oublié de joindre la feuille de calcul au début, mais maintenant elle est déjà jointe avec le code VBA .
As-tu une version FR ou EN de excel ? car dans certains cas (Février, Décembre) vba ne peut reconnaître automatiquement la date (alors que la feuille excel le fait correctement)
Proposition,
Je viens du Brésil et j'utilise la version portugaise d'Excel 2010 64 bits ici du Brésil .
votre proposition était bonne, mais la dernière ligne ne devrait pas être avec le tableau .
Je parle de cette dernière ligne ci-dessous :
*Close price adjusted for splits.**Adjusted close price adjusted for both dividends and splits
après avoir exécuté la macro pour importer les données, puis juste après je lancerai une macro de tableau croisé dynamique automatique .
alors cette dernière ligne que j'ai mentionnée apparaîtra ensemble dans le tableau croisé dynamique .
Voici sans la ligne
si vous regardez, vous verrez que maintenant dans la dernière ligne, vous importez des données de novembre 2019 .
il serait possible de filtrer les données en ne considérant que le mois et l'année ?
J'ai inséré une nouvelle feuille de calcul montrant à quoi ressemblerait ce nouveau modèle, vous pouvez voir dans les cellules J1 et L1 de la feuille de calcul 2 .
Désolé, c'est en supprimant le message dernière ligne que j'ai commis une omission dans le code.
J'ai quand même laissé des dates précises dans les critères, c'est plus simple et qui peut le plus peut le moins
Résolu
merci beaucoup monsieur Steelson pour le temps que vous avez consacré à m'aider .
Je vous félicite également pour votre excellente connaissance en VBA .
un câlin et même une autre occasion .
bonjour ,
J'avais besoin de revoir toutes les données, j'ai vérifié que si je veux importer la table entière, la macro VBA ne peut pas importer la table totale depuis la page Web .
la table Web totale a une plage du 27 mars 2020 au 29 mars 2019 .
Mais la macro VBA ne peut pas importer la table entière Si vous voulez toutes les données .
ci-joint est la feuille de calcul avec la tentative d'importer toutes les données de la table web .
Hé oui ! je n'ai pas touché à la structure de la macro que tu avais trouvée excellentela macro VBA ne peut pas importer la table totale depuis la page Web .
et sur laquelle tu avais éveillé ma curiositéJ'ai trouvé un excellent code VBA pour importer des données directement d'Internet vers Excel .
il manque le code génial que tu as trouvé (je voudrais voir cela !)
En fait ce n'est pas comme cela qu'il faut procéder ... les données sont contenues dans un JSON :
"HistoricalPriceStore":{"prices":[{"date":1585315800,"open":2555.8701171875,"high":2615.909912109375,"low":2520.02001953125,"close":2541.469970703125,"volume":6194330000,"adjclose":2541.469970703125},{"date":1585229400,"open":2501.2900390625,"high":2637.010009765625,"low":2500.719970703125,"close":2630.070068359375,"volume":7753160000,"adjclose":2630.070068359375},{"date":1585143000,"open":2457.77001953125,"high":2571.419921875,"low":2407.530029296875,"close":2475.56005859375,"volume":8285670000,"adjclose":2475.56005859375},{"date":1585056600,"open":2344.43994140625,"high":2449.7099609375,"low":2344.43994140625,"close":2447.330078125,"volume":7547350000,"adjclose":2447.330078125},{"date":1584970200,"open":2290.7099609375,"high":2300.72998046875,"low":2191.860107421875,"close":2237.39990234375,"volume":7402180000,"adjclose":2237.39990234375},{"date":1584711000,"open":2431.93994140625,"high":2453.010009765625,"low":2295.56005859375,"close":2304.919921875,"volume":9044690000,"adjclose":2304.919921875},{"date":1584624600,"open":2393.47998046875,"high":2466.969970703125,"low":2319.780029296875,"close":2409.389892578125,"volume":7946710000,"adjclose":2409.389892578125},{"date":1584538200,"open":2436.5,"high":2453.570068359375,"low":2280.52001953125,"close":2398.10009765625,"volume":8755780000,"adjclose":2398.10009765625},{"date":1584451800,"open":2425.659912109375,"high":2553.929931640625,"low":2367.0400390625,"close":2529.18994140625,"volume":8358500000,"adjclose":2529.18994140625},{"date":1584365400,"open":2508.590087890625,"high":2562.97998046875,"low":2380.93994140625,"close":2386.1298828125,"volume":7781540000,"adjclose":2386.1298828125},{"date":1584106200,"open":2569.989990234375,"high":2711.330078125,"low":2492.3701171875,"close":2711.02001953125,"volume":8258670000,"adjclose":2711.02001953125},{"date":1584019800,"open":2630.860107421875,"high":2660.949951171875,"low":2478.860107421875,"close":2480.639892578125,"volume":8829380000,"adjclose":2480.639892578125},{"date":1583933400,"open":2825.60009765625,"high":2825.60009765625,"low":2707.219970703125,"close":2741.3798828125,"volume":7374110000,"adjclose":2741.3798828125},{"date":1583847000,"open":2813.47998046875,"high":2882.590087890625,"low":2734,"close":2882.22998046875,"volume":7635960000,"adjclose":2882.22998046875},{"date":1583760600,"open":2863.889892578125,"high":2863.889892578125,"low":2734.429931640625,"close":2746.56005859375,"volume":8423050000,"adjclose":2746.56005859375},{"date":1583505000,"open":2954.199951171875,"high":2985.929931640625,"low":2901.5400390625,"close":2972.3701171875,"volume":6552140000,"adjclose":2972.3701171875},{"date":1583418600,"open":3075.699951171875,"high":3083.0400390625,"low":2999.830078125,"close":3023.93994140625,"volume":5575550000,"adjclose":3023.93994140625},{"date":1583332200,"open":3045.75,"high":3130.969970703125,"low":3034.3798828125,"close":3130.1201171875,"volume":5035480000,"adjclose":3130.1201171875},{"date":1583245800,"open":3096.4599609375,"high":3136.719970703125,"low":2976.6298828125,"close":3003.3701171875,"volume":6355940000,"adjclose":3003.3701171875},{"date":1583159400,"open":2974.280029296875,"high":3090.9599609375,"low":2945.18994140625,"close":3090.22998046875,"volume":6376400000,"adjclose":3090.22998046875},{"date":1582900200,"open":2916.89990234375,"high":2959.719970703125,"low":2855.840087890625,"close":2954.219970703125,"volume":8563850000,"adjclose":2954.219970703125},{"date":1582813800,"open":3062.5400390625,"high":3097.070068359375,"low":2977.389892578125,"close":2978.760009765625,"volume":7058840000,"adjclose":2978.760009765625},{"date":1582727400,"open":3139.89990234375,"high":3182.510009765625,"low":3108.989990234375,"close":3116.389892578125,"volume":5478110000,"adjclose":3116.389892578125},{"date":1582641000,"open":3238.93994140625,"high":3246.989990234375,"low":3118.77001953125,"close":3128.2099609375,"volume":5591510000,"adjclose":3128.2099609375},{"date":1582554600,"open":3257.610107421875,"high":3259.81005859375,"low":3214.64990234375,"close":3225.889892578125,"volume":4842960000,"adjclose":3225.889892578125},{"date":1582295400,"open":3360.5,"high":3360.760009765625,"low":3328.449951171875,"close":3337.75,"volume":3899270000,"adjclose":3337.75},{"date":1582209000,"open":3380.449951171875,"high":3389.14990234375,"low":3341.02001953125,"close":3373.22998046875,"volume":4007320000,"adjclose":3373.22998046875},{"date":1582122600,"open":3380.389892578125,"high":3393.52001953125,"low":3378.830078125,"close":3386.14990234375,"volume":3600150000,"adjclose":3386.14990234375},{"date":1582036200,"open":3369.0400390625,"high":3375.010009765625,"low":3355.610107421875,"close":3370.2900390625,"volume":3746720000,"adjclose":3370.2900390625},{"date":1581690600,"open":3378.080078125,"high":3380.68994140625,"low":3366.14990234375,"close":3380.159912109375,"volume":3398040000,"adjclose":3380.159912109375},{"date":1581604200,"open":3365.89990234375,"high":3385.090087890625,"low":3360.52001953125,"close":3373.93994140625,"volume":3498240000,"adjclose":3373.93994140625},{"date":1581517800,"open":3370.5,"high":3381.469970703125,"low":3369.719970703125,"close":3379.449951171875,"volume":3926380000,"adjclose":3379.449951171875},{"date":1581431400,"open":3365.8701171875,"high":3375.6298828125,"low":3352.719970703125,"close":3357.75,"volume":3760550000,"adjclose":3357.75},{"date":1581345000,"open":3318.280029296875,"high":3352.260009765625,"low":3317.77001953125,"close":3352.090087890625,"volume":3450350000,"adjclose":3352.090087890625},{"date":1581085800,"open":3335.5400390625,"high":3341.419921875,"low":3322.1201171875,"close":3327.7099609375,"volume":3730650000,"adjclose":3327.7099609375},{"date":1580999400,"open":3344.919921875,"high":3347.9599609375,"low":3334.389892578125,"close":3345.780029296875,"volume":3868370000,"adjclose":3345.780029296875},{"date":1580913000,"open":3324.909912109375,"high":3337.580078125,"low":3313.75,"close":3334.68994140625,"volume":4117730000,"adjclose":3334.68994140625},{"date":1580826600,"open":3280.610107421875,"high":3306.919921875,"low":3280.610107421875,"close":3297.590087890625,"volume":3995320000,"adjclose":3297.590087890625},{"date":1580740200,"open":3235.659912109375,"high":3268.43994140625,"low":3235.659912109375,"close":3248.919921875,"volume":3757910000,"adjclose":3248.919921875},{"date":1580481000,"open":3282.330078125,"high":3282.330078125,"low":3214.679931640625,"close":3225.52001953125,"volume":4527830000,"adjclose":3225.52001953125},{"date":1580394600,"open":3256.449951171875,"high":3285.909912109375,"low":3242.800048828125,"close":3283.659912109375,"volume":3787250000,"adjclose":3283.659912109375},{"date":1580308200,"open":3289.4599609375,"high":3293.469970703125,"low":3271.889892578125,"close":3273.39990234375,"volume":3584500000,"adjclose":3273.39990234375},{"date":1580221800,"open":3255.35009765625,"high":3285.780029296875,"low":3253.219970703125,"close":3276.239990234375,"volume":3526720000,"adjclose":3276.239990234375},{"date":1580135400,"open":3247.159912109375,"high":3258.85009765625,"low":3234.5,"close":3243.6298828125,"volume":3823100000,"adjclose":3243.6298828125},{"date":1579876200,"open":3333.10009765625,"high":3333.179931640625,"low":3281.530029296875,"close":3295.469970703125,"volume":3707130000,"adjclose":3295.469970703125},{"date":1579789800,"open":3315.77001953125,"high":3326.8798828125,"low":3301.8701171875,"close":3325.5400390625,"volume":3764860000,"adjclose":3325.5400390625},{"date":1579703400,"open":3330.02001953125,"high":3337.77001953125,"low":3320.0400390625,"close":3321.75,"volume":3619850000,"adjclose":3321.75},{"date":1579617000,"open":3321.030029296875,"high":3329.7900390625,"low":3316.610107421875,"close":3320.7900390625,"volume":4105340000,"adjclose":3320.7900390625},{"date":1579271400,"open":3323.659912109375,"high":3329.8798828125,"low":3318.860107421875,"close":3329.6201171875,"volume":3698170000,"adjclose":3329.6201171875},{"date":1579185000,"open":3302.969970703125,"high":3317.110107421875,"low":3302.820068359375,"close":3316.81005859375,"volume":3535080000,"adjclose":3316.81005859375},{"date":1579098600,"open":3282.27001953125,"high":3298.659912109375,"low":3280.68994140625,"close":3289.2900390625,"volume":3716840000,"adjclose":3289.2900390625},{"date":1579012200,"open":3285.35009765625,"high":3294.25,"low":3277.18994140625,"close":3283.14990234375,"volume":3665130000,"adjclose":3283.14990234375},{"date":1578925800,"open":3271.1298828125,"high":3288.1298828125,"low":3268.429931640625,"close":3288.1298828125,"volume":3456380000,"adjclose":3288.1298828125},{"date":1578666600,"open":3281.81005859375,"high":3282.989990234375,"low":3260.860107421875,"close":3265.35009765625,"volume":3212970000,"adjclose":3265.35009765625},{"date":1578580200,"open":3266.030029296875,"high":3275.580078125,"low":3263.669921875,"close":3274.699951171875,"volume":3638390000,"adjclose":3274.699951171875},{"date":1578493800,"open":3238.590087890625,"high":3267.070068359375,"low":3236.669921875,"close":3253.050048828125,"volume":3720890000,"adjclose":3253.050048828125},{"date":1578407400,"open":3241.860107421875,"high":3244.909912109375,"low":3232.429931640625,"close":3237.179931640625,"volume":3420380000,"adjclose":3237.179931640625},{"date":1578321000,"open":3217.550048828125,"high":3246.840087890625,"low":3214.639892578125,"close":3246.280029296875,"volume":3674070000,"adjclose":3246.280029296875},{"date":1578061800,"open":3226.360107421875,"high":3246.14990234375,"low":3222.340087890625,"close":3234.85009765625,"volume":3461290000,"adjclose":3234.85009765625},{"date":1577975400,"open":3244.669921875,"high":3258.139892578125,"low":3235.530029296875,"close":3257.85009765625,"volume":3458250000,"adjclose":3257.85009765625},{"date":1577802600,"open":3215.179931640625,"high":3231.719970703125,"low":3212.030029296875,"close":3230.780029296875,"volume":2893810000,"adjclose":3230.780029296875},{"date":1577716200,"open":3240.090087890625,"high":3240.919921875,"low":3216.570068359375,"close":3221.2900390625,"volume":3013290000,"adjclose":3221.2900390625},{"date":1577457000,"open":3247.22998046875,"high":3247.929931640625,"low":3234.3701171875,"close":3240.02001953125,"volume":2428670000,"adjclose":3240.02001953125},{"date":1577370600,"open":3227.199951171875,"high":3240.080078125,"low":3227.199951171875,"close":3239.909912109375,"volume":2160680000,"adjclose":3239.909912109375},{"date":1577197800,"open":3225.449951171875,"high":3226.429931640625,"low":3220.510009765625,"close":3223.3798828125,"volume":1296540000,"adjclose":3223.3798828125},{"date":1577111400,"open":3226.050048828125,"high":3227.780029296875,"low":3222.300048828125,"close":3224.010009765625,"volume":3060610000,"adjclose":3224.010009765625},{"date":1576852200,"open":3223.330078125,"high":3225.64990234375,"low":3216.030029296875,"close":3221.219970703125,"volume":6454270000,"adjclose":3221.219970703125},{"date":1576765800,"open":3192.320068359375,"high":3205.47998046875,"low":3192.320068359375,"close":3205.3701171875,"volume":3720450000,"adjclose":3205.3701171875},{"date":1576679400,"open":3195.2099609375,"high":3198.47998046875,"low":3191.139892578125,"close":3191.139892578125,"volume":4014080000,"adjclose":3191.139892578125},{"date":1576593000,"open":3195.39990234375,"high":3198.219970703125,"low":3191.030029296875,"close":3192.52001953125,"volume":3837540000,"adjclose":3192.52001953125},{"date":1576506600,"open":3183.6298828125,"high":3197.7099609375,"low":3183.6298828125,"close":3191.449951171875,"volume":4051790000,"adjclose":3191.449951171875},{"date":1576247400,"open":3166.64990234375,"high":3182.679931640625,"low":3156.510009765625,"close":3168.800048828125,"volume":3736870000,"adjclose":3168.800048828125},{"date":1576161000,"open":3141.22998046875,"high":3176.280029296875,"low":3138.469970703125,"close":3168.570068359375,"volume":3990690000,"adjclose":3168.570068359375},{"date":1576074600,"open":3135.75,"high":3143.97998046875,"low":3133.2099609375,"close":3141.6298828125,"volume":3252540000,"adjclose":3141.6298828125},{"date":1575988200,"open":3135.360107421875,"high":3142.1201171875,"low":3126.090087890625,"close":3132.52001953125,"volume":3343790000,"adjclose":3132.52001953125},{"date":1575901800,"open":3141.860107421875,"high":3148.8701171875,"low":3135.4599609375,"close":3135.9599609375,"volume":3345990000,"adjclose":3135.9599609375},{"date":1575642600,"open":3134.6201171875,"high":3150.60009765625,"low":3134.6201171875,"close":3145.909912109375,"volume":3479480000,"adjclose":3145.909912109375},{"date":1575556200,"open":3119.2099609375,"high":3119.449951171875,"low":3103.760009765625,"close":3117.429931640625,"volume":3355750000,"adjclose":3117.429931640625},{"date":1575469800,"open":3103.5,"high":3119.3798828125,"low":3102.530029296875,"close":3112.760009765625,"volume":3695030000,"adjclose":3112.760009765625},{"date":1575383400,"open":3087.409912109375,"high":3094.969970703125,"low":3070.330078125,"close":3093.199951171875,"volume":3653390000,"adjclose":3093.199951171875},{"date":1575297000,"open":3143.85009765625,"high":3144.31005859375,"low":3110.780029296875,"close":3113.8701171875,"volume":3268740000,"adjclose":3113.8701171875},{"date":1575037800,"open":3147.179931640625,"high":3150.300048828125,"low":3139.340087890625,"close":3140.97998046875,"volume":1743020000,"adjclose":3140.97998046875},{"date":1574865000,"open":3145.489990234375,"high":3154.260009765625,"low":3143.409912109375,"close":3153.6298828125,"volume":3033090000,"adjclose":3153.6298828125},{"date":1574778600,"open":3134.85009765625,"high":3142.68994140625,"low":3131,"close":3140.52001953125,"volume":4595590000,"adjclose":3140.52001953125},{"date":1574692200,"open":3117.43994140625,"high":3133.830078125,"low":3117.43994140625,"close":3133.639892578125,"volume":3511530000,"adjclose":3133.639892578125},{"date":1574433000,"open":3111.409912109375,"high":3112.8701171875,"low":3099.260009765625,"close":3110.2900390625,"volume":3226780000,"adjclose":3110.2900390625},{"date":1574346600,"open":3108.489990234375,"high":3110.110107421875,"low":3094.550048828125,"close":3103.5400390625,"volume":3720560000,"adjclose":3103.5400390625},{"date":1574260200,"open":3114.659912109375,"high":3118.969970703125,"low":3091.409912109375,"close":3108.4599609375,"volume":4034890000,"adjclose":3108.4599609375},{"date":1574173800,"open":3127.449951171875,"high":3127.639892578125,"low":3113.469970703125,"close":3120.179931640625,"volume":3590070000,"adjclose":3120.179931640625},{"date":1574087400,"open":3117.909912109375,"high":3124.169921875,"low":3112.06005859375,"close":3122.030029296875,"volume":3436690000,"adjclose":3122.030029296875},{"date":1573828200,"open":3107.919921875,"high":3120.4599609375,"low":3104.60009765625,"close":3120.4599609375,"volume":3335650000,"adjclose":3120.4599609375},{"date":1573741800,"open":3090.75,"high":3098.199951171875,"low":3083.260009765625,"close":3096.6298828125,"volume":3276070000,"adjclose":3096.6298828125},{"date":1573655400,"open":3084.179931640625,"high":3098.06005859375,"low":3078.800048828125,"close":3094.0400390625,"volume":3509280000,"adjclose":3094.0400390625},{"date":1573569000,"open":3089.280029296875,"high":3102.610107421875,"low":3084.72998046875,"close":3091.840087890625,"volume":3466010000,"adjclose":3091.840087890625},{"date":1573482600,"open":3080.330078125,"high":3088.330078125,"low":3075.820068359375,"close":3087.010009765625,"volume":3035530000,"adjclose":3087.010009765625},{"date":1573223400,"open":3081.25,"high":3093.090087890625,"low":3073.580078125,"close":3093.080078125,"volume":3499150000,"adjclose":3093.080078125},{"date":1573137000,"open":3087.02001953125,"high":3097.77001953125,"low":3080.22998046875,"close":3085.179931640625,"volume":4144640000,"adjclose":3085.179931640625},{"date":1573050600,"open":3075.10009765625,"high":3078.340087890625,"low":3065.889892578125,"close":3076.780029296875,"volume":4458190000,"adjclose":3076.780029296875},{"date":1572964200,"open":3080.800048828125,"high":3083.949951171875,"low":3072.14990234375,"close":3074.6201171875,"volume":4486130000,"adjclose":3074.6201171875},{"date":1572877800,"open":3078.9599609375,"high":3085.199951171875,"low":3074.8701171875,"close":3078.27001953125,"volume":4146850000,"adjclose":3078.27001953125},{"date":1572615000,"open":3050.719970703125,"high":3066.949951171875,"low":3050.719970703125,"close":3066.909912109375,"volume":3930200000,"adjclose":3066.909912109375},{"date":1572528600,"open":3046.89990234375,"high":3046.89990234375,"low":3023.18994140625,"close":3037.56005859375,"volume":4139280000,"adjclose":3037.56005859375},{"date":1572442200,"open":3039.739990234375,"high":3050.10009765625,"low":3025.9599609375,"close":3046.77001953125,"volume":3776030000,"adjclose":3046.77001953125},{"date":1572355800,"open":3035.389892578125,"high":3047.8701171875,"low":3034.81005859375,"close":3036.889892578125,"volume":3589930000,"adjclose":3036.889892578125},{"date":1572269400,"open":3032.1201171875,"high":3044.080078125,"low":3032.1201171875,"close":3039.419921875,"volume":3521230000,"adjclose":3039.419921875},{"date":1572010200,"open":3003.320068359375,"high":3027.389892578125,"low":3001.93994140625,"close":3022.550048828125,"volume":3370370000,"adjclose":3022.550048828125},{"date":1571923800,"open":3014.780029296875,"high":3016.070068359375,"low":3000.419921875,"close":3010.2900390625,"volume":3692600000,"adjclose":3010.2900390625},{"date":1571837400,"open":2994.010009765625,"high":3004.780029296875,"low":2991.2099609375,"close":3004.52001953125,"volume":3392870000,"adjclose":3004.52001953125},{"date":1571751000,"open":3010.72998046875,"high":3014.570068359375,"low":2995.0400390625,"close":2995.989990234375,"volume":3523890000,"adjclose":2995.989990234375},{"date":1571664600,"open":2996.47998046875,"high":3007.330078125,"low":2995.35009765625,"close":3006.719970703125,"volume":3271620000,"adjclose":3006.719970703125},{"date":1571405400,"open":2996.840087890625,"high":3000,"low":2976.31005859375,"close":2986.199951171875,"volume":3264290000,"adjclose":2986.199951171875},{"date":1571319000,"open":3000.77001953125,"high":3008.2900390625,"low":2991.7900390625,"close":2997.949951171875,"volume":3115960000,"adjclose":2997.949951171875},{"date":1571232600,"open":2989.679931640625,"high":2997.5400390625,"low":2985.199951171875,"close":2989.68994140625,"volume":3222570000,"adjclose":2989.68994140625},{"date":1571146200,"open":2973.610107421875,"high":3003.280029296875,"low":2973.610107421875,"close":2995.679931640625,"volume":3340740000,"adjclose":2995.679931640625},{"date":1571059800,"open":2965.81005859375,"high":2972.840087890625,"low":2962.93994140625,"close":2966.14990234375,"volume":2557020000,"adjclose":2966.14990234375},{"date":1570800600,"open":2963.070068359375,"high":2993.280029296875,"low":2963.070068359375,"close":2970.27001953125,"volume":3580460000,"adjclose":2970.27001953125},{"date":1570714200,"open":2918.550048828125,"high":2948.4599609375,"low":2917.1201171875,"close":2938.1298828125,"volume":3217250000,"adjclose":2938.1298828125},{"date":1570627800,"open":2911.10009765625,"high":2929.320068359375,"low":2907.409912109375,"close":2919.39990234375,"volume":2726820000,"adjclose":2919.39990234375},{"date":1570541400,"open":2920.39990234375,"high":2925.469970703125,"low":2892.659912109375,"close":2893.06005859375,"volume":3356450000,"adjclose":2893.06005859375},{"date":1570455000,"open":2944.22998046875,"high":2959.75,"low":2935.679931640625,"close":2938.7900390625,"volume":2940140000,"adjclose":2938.7900390625},{"date":1570195800,"open":2918.56005859375,"high":2953.739990234375,"low":2918.56005859375,"close":2952.010009765625,"volume":2990830000,"adjclose":2952.010009765625},{"date":1570109400,"open":2885.3798828125,"high":2911.1298828125,"low":2855.93994140625,"close":2910.6298828125,"volume":3503640000,"adjclose":2910.6298828125},{"date":1570023000,"open":2924.780029296875,"high":2924.780029296875,"low":2874.929931640625,"close":2887.610107421875,"volume":3912520000,"adjclose":2887.610107421875},{"date":1569936600,"open":2983.68994140625,"high":2992.530029296875,"low":2938.699951171875,"close":2940.25,"volume":3558040000,"adjclose":2940.25},{"date":1569850200,"open":2967.070068359375,"high":2983.85009765625,"low":2967.070068359375,"close":2976.739990234375,"volume":3247610000,"adjclose":2976.739990234375},{"date":1569591000,"open":2985.469970703125,"high":2987.31005859375,"low":2945.530029296875,"close":2961.7900390625,"volume":3243650000,"adjclose":2961.7900390625},{"date":1569504600,"open":2985.72998046875,"high":2987.280029296875,"low":2963.7099609375,"close":2977.6201171875,"volume":3077240000,"adjclose":2977.6201171875},{"date":1569418200,"open":2968.35009765625,"high":2989.820068359375,"low":2952.860107421875,"close":2984.8701171875,"volume":3318870000,"adjclose":2984.8701171875},{"date":1569331800,"open":3002.429931640625,"high":3007.97998046875,"low":2957.72998046875,"close":2966.60009765625,"volume":3868160000,"adjclose":2966.60009765625},{"date":1569245400,"open":2983.5,"high":2999.14990234375,"low":2982.22998046875,"close":2991.780029296875,"volume":3186590000,"adjclose":2991.780029296875},{"date":1568986200,"open":3008.419921875,"high":3016.3701171875,"low":2984.679931640625,"close":2992.070068359375,"volume":6094740000,"adjclose":2992.070068359375},{"date":1568899800,"open":3010.360107421875,"high":3021.989990234375,"low":3003.159912109375,"close":3006.7900390625,"volume":3251290000,"adjclose":3006.7900390625},{"date":1568813400,"open":3001.5,"high":3007.830078125,"low":2978.570068359375,"close":3006.72998046875,"volume":3435540000,"adjclose":3006.72998046875},{"date":1568727000,"open":2995.669921875,"high":3006.2099609375,"low":2993.72998046875,"close":3005.699951171875,"volume":3671840000,"adjclose":3005.699951171875},{"date":1568640600,"open":2996.409912109375,"high":3002.18994140625,"low":2990.669921875,"close":2997.9599609375,"volume":4274640000,"adjclose":2997.9599609375},{"date":1568381400,"open":3012.2099609375,"high":3017.330078125,"low":3002.89990234375,"close":3007.389892578125,"volume":3520060000,"adjclose":3007.389892578125},{"date":1568295000,"open":3009.080078125,"high":3020.739990234375,"low":3000.919921875,"close":3009.570068359375,"volume":3791860000,"adjclose":3009.570068359375},{"date":1568208600,"open":2981.409912109375,"high":3000.929931640625,"low":2975.31005859375,"close":3000.929931640625,"volume":3927550000,"adjclose":3000.929931640625},{"date":1568122200,"open":2971.010009765625,"high":2979.389892578125,"low":2957.010009765625,"close":2979.389892578125,"volume":4390770000,"adjclose":2979.389892578125},{"date":1568035800,"open":2988.429931640625,"high":2989.429931640625,"low":2969.389892578125,"close":2978.429931640625,"volume":4002890000,"adjclose":2978.429931640625},{"date":1567776600,"open":2980.330078125,"high":2985.030029296875,"low":2972.510009765625,"close":2978.7099609375,"volume":3208280000,"adjclose":2978.7099609375},{"date":1567690200,"open":2960.60009765625,"high":2985.860107421875,"low":2960.60009765625,"close":2976,"volume":3890700000,"adjclose":2976},{"date":1567603800,"open":2924.669921875,"high":2938.840087890625,"low":2921.860107421875,"close":2937.780029296875,"volume":3163260000,"adjclose":2937.780029296875},{"date":1567517400,"open":2909.010009765625,"high":2914.389892578125,"low":2891.85009765625,"close":2906.27001953125,"volume":3426790000,"adjclose":2906.27001953125},{"date":1567171800,"open":2937.090087890625,"high":2940.429931640625,"low":2913.320068359375,"close":2926.4599609375,"volume":3008450000,"adjclose":2926.4599609375},{"date":1567085400,"open":2910.3701171875,"high":2930.5,"low":2905.669921875,"close":2924.580078125,"volume":3176190000,"adjclose":2924.580078125},{"date":1566999000,"open":2861.280029296875,"high":2890.030029296875,"low":2853.050048828125,"close":2887.93994140625,"volume":3097420000,"adjclose":2887.93994140625},{"date":1566912600,"open":2893.139892578125,"high":2898.7900390625,"low":2860.590087890625,"close":2869.159912109375,"volume":3533630000,"adjclose":2869.159912109375},{"date":1566826200,"open":2866.699951171875,"high":2879.27001953125,"low":2856,"close":2878.3798828125,"volume":2857600000,"adjclose":2878.3798828125},{"date":1566567000,"open":2911.070068359375,"high":2927.010009765625,"low":2834.969970703125,"close":2847.110107421875,"volume":3937300000,"adjclose":2847.110107421875},{"date":1566480600,"open":2930.93994140625,"high":2939.080078125,"low":2904.510009765625,"close":2922.949951171875,"volume":2890880000,"adjclose":2922.949951171875},{"date":1566394200,"open":2922.0400390625,"high":2928.72998046875,"low":2917.909912109375,"close":2924.429931640625,"volume":3011190000,"adjclose":2924.429931640625},{"date":1566307800,"open":2919.010009765625,"high":2923.6298828125,"low":2899.60009765625,"close":2900.510009765625,"volume":3066300000,"adjclose":2900.510009765625},{"date":1566221400,"open":2913.47998046875,"high":2931,"low":2913.47998046875,"close":2923.64990234375,"volume":3212880000,"adjclose":2923.64990234375},{"date":1565962200,"open":2864.739990234375,"high":2893.6298828125,"low":2864.739990234375,"close":2888.679931640625,"volume":3498150000,"adjclose":2888.679931640625},{"date":1565875800,"open":2846.199951171875,"high":2856.669921875,"low":2825.510009765625,"close":2847.60009765625,"volume":4038000000,"adjclose":2847.60009765625},{"date":1565789400,"open":2894.14990234375,"high":2894.14990234375,"low":2839.639892578125,"close":2840.60009765625,"volume":4312530000,"adjclose":2840.60009765625},{"date":1565703000,"open":2880.719970703125,"high":2943.31005859375,"low":2877.050048828125,"close":2926.320068359375,"volume":3853600000,"adjclose":2926.320068359375},{"date":1565616600,"open":2907.070068359375,"high":2907.580078125,"low":2873.139892578125,"close":2882.699951171875,"volume":2851630000,"adjclose":2882.699951171875},{"date":1565357400,"open":2930.510009765625,"high":2935.75,"low":2900.14990234375,"close":2918.64990234375,"volume":3350640000,"adjclose":2918.64990234375},{"date":1565271000,"open":2896.2099609375,"high":2938.719970703125,"low":2894.469970703125,"close":2938.090087890625,"volume":4106370000,"adjclose":2938.090087890625},{"date":1565184600,"open":2858.64990234375,"high":2892.169921875,"low":2825.7099609375,"close":2883.97998046875,"volume":4491750000,"adjclose":2883.97998046875},{"date":1565098200,"open":2861.179931640625,"high":2884.39990234375,"low":2847.419921875,"close":2881.77001953125,"volume":4154240000,"adjclose":2881.77001953125},{"date":1565011800,"open":2898.070068359375,"high":2898.070068359375,"low":2822.1201171875,"close":2844.739990234375,"volume":4513730000,"adjclose":2844.739990234375},{"date":1564752600,"open":2943.89990234375,"high":2945.5,"low":2914.110107421875,"close":2932.050048828125,"volume":3874660000,"adjclose":2932.050048828125},{"date":1564666200,"open":2980.320068359375,"high":3013.590087890625,"low":2945.22998046875,"close":2953.56005859375,"volume":4762300000,"adjclose":2953.56005859375},{"date":1564579800,"open":3016.219970703125,"high":3017.39990234375,"low":2958.080078125,"close":2980.3798828125,"volume":4623430000,"adjclose":2980.3798828125},{"date":1564493400,"open":3007.659912109375,"high":3017.18994140625,"low":3000.93994140625,"close":3013.179931640625,"volume":3634330000,"adjclose":3013.179931640625},{"date":1564407000,"open":3024.469970703125,"high":3025.610107421875,"low":3014.300048828125,"close":3020.969970703125,"volume":3203710000,"adjclose":3020.969970703125},{"date":1564147800,"open":3013.25,"high":3027.97998046875,"low":3012.590087890625,"close":3025.860107421875,"volume":3257590000,"adjclose":3025.860107421875},{"date":1564061400,"open":3016.260009765625,"high":3016.31005859375,"low":2997.239990234375,"close":3003.669921875,"volume":3645270000,"adjclose":3003.669921875},{"date":1563975000,"open":2998.77001953125,"high":3019.590087890625,"low":2996.820068359375,"close":3019.56005859375,"volume":3428980000,"adjclose":3019.56005859375},{"date":1563888600,"open":2994.739990234375,"high":3005.89990234375,"low":2988.56005859375,"close":3005.469970703125,"volume":3313660000,"adjclose":3005.469970703125},{"date":1563802200,"open":2981.929931640625,"high":2990.7099609375,"low":2976.64990234375,"close":2985.030029296875,"volume":3003720000,"adjclose":2985.030029296875},{"date":1563543000,"open":3004.260009765625,"high":3006.02001953125,"low":2975.860107421875,"close":2976.610107421875,"volume":3260360000,"adjclose":2976.610107421875},{"date":1563456600,"open":2978.8701171875,"high":2998.280029296875,"low":2973.090087890625,"close":2995.110107421875,"volume":3296580000,"adjclose":2995.110107421875},{"date":1563370200,"open":3005.10009765625,"high":3005.260009765625,"low":2984.25,"close":2984.419921875,"volume":3181600000,"adjclose":2984.419921875},{"date":1563283800,"open":3012.1298828125,"high":3015.02001953125,"low":3001.14990234375,"close":3004.0400390625,"volume":3290650000,"adjclose":3004.0400390625},{"date":1563197400,"open":3017.800048828125,"high":3017.800048828125,"low":3008.77001953125,"close":3014.300048828125,"volume":2874970000,"adjclose":3014.300048828125},{"date":1562938200,"open":3003.360107421875,"high":3013.919921875,"low":3001.8701171875,"close":3013.77001953125,"volume":2974960000,"adjclose":3013.77001953125},{"date":1562851800,"open":2999.6201171875,"high":3002.330078125,"low":2988.800048828125,"close":2999.909912109375,"volume":3154620000,"adjclose":2999.909912109375},{"date":1562765400,"open":2989.300048828125,"high":3002.97998046875,"low":2984.6201171875,"close":2993.070068359375,"volume":3154240000,"adjclose":2993.070068359375},{"date":1562679000,"open":2965.52001953125,"high":2981.89990234375,"low":2963.43994140625,"close":2979.6298828125,"volume":3028210000,"adjclose":2979.6298828125},{"date":1562592600,"open":2979.77001953125,"high":2980.760009765625,"low":2970.090087890625,"close":2975.949951171875,"volume":2904550000,"adjclose":2975.949951171875},{"date":1562333400,"open":2984.25,"high":2994.030029296875,"low":2967.969970703125,"close":2990.409912109375,"volume":2434210000,"adjclose":2990.409912109375},{"date":1562160600,"open":2978.080078125,"high":2995.840087890625,"low":2977.9599609375,"close":2995.820068359375,"volume":1963720000,"adjclose":2995.820068359375},{"date":1562074200,"open":2964.659912109375,"high":2973.2099609375,"low":2955.919921875,"close":2973.010009765625,"volume":3206840000,"adjclose":2973.010009765625},{"date":1561987800,"open":2971.409912109375,"high":2977.929931640625,"low":2952.219970703125,"close":2964.330078125,"volume":3513270000,"adjclose":2964.330078125},{"date":1561728600,"open":2932.93994140625,"high":2943.97998046875,"low":2929.050048828125,"close":2941.760009765625,"volume":5420700000,"adjclose":2941.760009765625},{"date":1561642200,"open":2919.659912109375,"high":2929.300048828125,"low":2918.570068359375,"close":2924.919921875,"volume":3122920000,"adjclose":2924.919921875},{"date":1561555800,"open":2926.070068359375,"high":2932.590087890625,"low":2912.989990234375,"close":2913.780029296875,"volume":3478130000,"adjclose":2913.780029296875},{"date":1561469400,"open":2945.780029296875,"high":2946.52001953125,"low":2916.010009765625,"close":2917.3798828125,"volume":3578050000,"adjclose":2917.3798828125},{"date":1561383000,"open":2951.419921875,"high":2954.919921875,"low":2944.050048828125,"close":2945.35009765625,"volume":3136250000,"adjclose":2945.35009765625},{"date":1561123800,"open":2952.7099609375,"high":2964.14990234375,"low":2946.8701171875,"close":2950.4599609375,"volume":5000120000,"adjclose":2950.4599609375},{"date":1561037400,"open":2949.60009765625,"high":2958.06005859375,"low":2931.5,"close":2954.179931640625,"volume":3905940000,"adjclose":2954.179931640625},{"date":1560951000,"open":2920.550048828125,"high":2931.739990234375,"low":2911.429931640625,"close":2926.4599609375,"volume":3287890000,"adjclose":2926.4599609375},{"date":1560864600,"open":2906.7099609375,"high":2930.7900390625,"low":2905.43994140625,"close":2917.75,"volume":3437620000,"adjclose":2917.75},{"date":1560778200,"open":2889.75,"high":2897.27001953125,"low":2887.300048828125,"close":2889.669921875,"volume":2810140000,"adjclose":2889.669921875},{"date":1560519000,"open":2886.820068359375,"high":2894.449951171875,"low":2879.6201171875,"close":2886.97998046875,"volume":2922330000,"adjclose":2886.97998046875},{"date":1560432600,"open":2886.239990234375,"high":2895.239990234375,"low":2881.989990234375,"close":2891.639892578125,"volume":3069810000,"adjclose":2891.639892578125},{"date":1560346200,"open":2882.72998046875,"high":2888.570068359375,"low":2874.679931640625,"close":2879.840087890625,"volume":3034130000,"adjclose":2879.840087890625},{"date":1560259800,"open":2903.27001953125,"high":2910.610107421875,"low":2878.530029296875,"close":2885.719970703125,"volume":3548420000,"adjclose":2885.719970703125},{"date":1560173400,"open":2885.830078125,"high":2904.77001953125,"low":2885.510009765625,"close":2886.72998046875,"volume":3209210000,"adjclose":2886.72998046875},{"date":1559914200,"open":2852.8701171875,"high":2884.969970703125,"low":2852.8701171875,"close":2873.340087890625,"volume":3220250000,"adjclose":2873.340087890625},{"date":1559827800,"open":2828.510009765625,"high":2852.10009765625,"low":2822.449951171875,"close":2843.489990234375,"volume":3396410000,"adjclose":2843.489990234375},{"date":1559741400,"open":2818.090087890625,"high":2827.280029296875,"low":2800.919921875,"close":2826.14990234375,"volume":3548830000,"adjclose":2826.14990234375},{"date":1559655000,"open":2762.639892578125,"high":2804.489990234375,"low":2762.639892578125,"close":2803.27001953125,"volume":3810430000,"adjclose":2803.27001953125},{"date":1559568600,"open":2751.530029296875,"high":2763.070068359375,"low":2728.81005859375,"close":2744.449951171875,"volume":3943810000,"adjclose":2744.449951171875},{"date":1559309400,"open":2766.14990234375,"high":2768.97998046875,"low":2750.52001953125,"close":2752.06005859375,"volume":3981020000,"adjclose":2752.06005859375},{"date":1559223000,"open":2786.93994140625,"high":2799,"low":2776.739990234375,"close":2788.860107421875,"volume":3273790000,"adjclose":2788.860107421875},{"date":1559136600,"open":2790.25,"high":2792.030029296875,"low":2766.06005859375,"close":2783.02001953125,"volume":3700050000,"adjclose":2783.02001953125},{"date":1559050200,"open":2830.030029296875,"high":2840.510009765625,"low":2801.580078125,"close":2802.389892578125,"volume":4121410000,"adjclose":2802.389892578125},{"date":1558704600,"open":2832.409912109375,"high":2841.360107421875,"low":2820.18994140625,"close":2826.06005859375,"volume":2887390000,"adjclose":2826.06005859375},{"date":1558618200,"open":2836.699951171875,"high":2836.699951171875,"low":2805.489990234375,"close":2822.239990234375,"volume":3891980000,"adjclose":2822.239990234375},{"date":1558531800,"open":2856.06005859375,"high":2865.469970703125,"low":2851.110107421875,"close":2856.27001953125,"volume":3192510000,"adjclose":2856.27001953125},{"date":1558445400,"open":2854.02001953125,"high":2868.8798828125,"low":2854.02001953125,"close":2864.360107421875,"volume":3218700000,"adjclose":2864.360107421875},{"date":1558359000,"open":2841.93994140625,"high":2853.860107421875,"low":2831.2900390625,"close":2840.22998046875,"volume":3288870000,"adjclose":2840.22998046875},{"date":1558099800,"open":2858.60009765625,"high":2885.47998046875,"low":2854.22998046875,"close":2859.530029296875,"volume":3257950000,"adjclose":2859.530029296875},{"date":1558013400,"open":2855.800048828125,"high":2892.14990234375,"low":2855.800048828125,"close":2876.320068359375,"volume":3338060000,"adjclose":2876.320068359375},{"date":1557927000,"open":2820.3798828125,"high":2858.679931640625,"low":2815.080078125,"close":2850.9599609375,"volume":3125950000,"adjclose":2850.9599609375},{"date":1557840600,"open":2820.1201171875,"high":2852.5400390625,"low":2820.1201171875,"close":2834.409912109375,"volume":3322720000,"adjclose":2834.409912109375},{"date":1557754200,"open":2840.18994140625,"high":2840.18994140625,"low":2801.429931640625,"close":2811.8701171875,"volume":3894030000,"adjclose":2811.8701171875},{"date":1557495000,"open":2863.10009765625,"high":2891.31005859375,"low":2825.389892578125,"close":2881.39990234375,"volume":3529600000,"adjclose":2881.39990234375},{"date":1557408600,"open":2859.840087890625,"high":2875.969970703125,"low":2836.39990234375,"close":2870.719970703125,"volume":3638820000,"adjclose":2870.719970703125},{"date":1557322200,"open":2879.610107421875,"high":2897.9599609375,"low":2873.280029296875,"close":2879.419921875,"volume":3485790000,"adjclose":2879.419921875},{"date":1557235800,"open":2913.030029296875,"high":2913.030029296875,"low":2862.60009765625,"close":2884.050048828125,"volume":3767100000,"adjclose":2884.050048828125},{"date":1557149400,"open":2908.889892578125,"high":2937.320068359375,"low":2898.2099609375,"close":2932.469970703125,"volume":3181520000,"adjclose":2932.469970703125},{"date":1556890200,"open":2929.2099609375,"high":2947.85009765625,"low":2929.2099609375,"close":2945.639892578125,"volume":3338120000,"adjclose":2945.639892578125},{"date":1556803800,"open":2922.159912109375,"high":2931.679931640625,"low":2900.5,"close":2917.52001953125,"volume":3778890000,"adjclose":2917.52001953125},{"date":1556717400,"open":2952.330078125,"high":2954.1298828125,"low":2923.360107421875,"close":2923.72998046875,"volume":3645850000,"adjclose":2923.72998046875},{"date":1556631000,"open":2937.139892578125,"high":2948.219970703125,"low":2924.110107421875,"close":2945.830078125,"volume":3919330000,"adjclose":2945.830078125},{"date":1556544600,"open":2940.580078125,"high":2949.52001953125,"low":2939.35009765625,"close":2943.030029296875,"volume":3118780000,"adjclose":2943.030029296875},{"date":1556285400,"open":2925.81005859375,"high":2939.8798828125,"low":2917.56005859375,"close":2939.8798828125,"volume":3248500000,"adjclose":2939.8798828125},{"date":1556199000,"open":2928.989990234375,"high":2933.10009765625,"low":2912.840087890625,"close":2926.169921875,"volume":3425280000,"adjclose":2926.169921875},{"date":1556112600,"open":2934,"high":2936.830078125,"low":2926.050048828125,"close":2927.25,"volume":3448960000,"adjclose":2927.25},{"date":1556026200,"open":2909.989990234375,"high":2936.31005859375,"low":2908.530029296875,"close":2933.679931640625,"volume":3635030000,"adjclose":2933.679931640625},{"date":1555939800,"open":2898.780029296875,"high":2909.510009765625,"low":2896.35009765625,"close":2907.969970703125,"volume":2997950000,"adjclose":2907.969970703125},{"date":1555594200,"open":2904.81005859375,"high":2908.39990234375,"low":2891.89990234375,"close":2905.030029296875,"volume":3506850000,"adjclose":2905.030029296875},{"date":1555507800,"open":2916.0400390625,"high":2918,"low":2895.449951171875,"close":2900.449951171875,"volume":3602300000,"adjclose":2900.449951171875},{"date":1555421400,"open":2912.260009765625,"high":2916.06005859375,"low":2900.7099609375,"close":2907.06005859375,"volume":3402210000,"adjclose":2907.06005859375},{"date":1555335000,"open":2908.320068359375,"high":2909.60009765625,"low":2896.47998046875,"close":2905.580078125,"volume":3088330000,"adjclose":2905.580078125},{"date":1555075800,"open":2900.860107421875,"high":2910.5400390625,"low":2898.3701171875,"close":2907.409912109375,"volume":3688490000,"adjclose":2907.409912109375},{"date":1554989400,"open":2891.919921875,"high":2893.419921875,"low":2881.989990234375,"close":2888.320068359375,"volume":2938540000,"adjclose":2888.320068359375},{"date":1554903000,"open":2881.3701171875,"high":2889.7099609375,"low":2879.1298828125,"close":2888.2099609375,"volume":3062380000,"adjclose":2888.2099609375},{"date":1554816600,"open":2886.580078125,"high":2886.8798828125,"low":2873.330078125,"close":2878.199951171875,"volume":3007980000,"adjclose":2878.199951171875},{"date":1554730200,"open":2888.4599609375,"high":2895.949951171875,"low":2880.780029296875,"close":2895.77001953125,"volume":3054030000,"adjclose":2895.77001953125},{"date":1554471000,"open":2884.159912109375,"high":2893.239990234375,"low":2882.989990234375,"close":2892.739990234375,"volume":3146820000,"adjclose":2892.739990234375},{"date":1554384600,"open":2873.989990234375,"high":2881.280029296875,"low":2867.139892578125,"close":2879.389892578125,"volume":3015180000,"adjclose":2879.389892578125},{"date":1554298200,"open":2876.090087890625,"high":2885.25,"low":2865.169921875,"close":2873.39990234375,"volume":3550240000,"adjclose":2873.39990234375},{"date":1554211800,"open":2868.239990234375,"high":2872.89990234375,"low":2858.75,"close":2867.239990234375,"volume":3246900000,"adjclose":2867.239990234375},{"date":1554125400,"open":2848.6298828125,"high":2869.39990234375,"low":2848.6298828125,"close":2867.18994140625,"volume":3500760000,"adjclose":2867.18994140625},{"date":1553866200,"open":2828.27001953125,"high":2836.030029296875,"low":2819.22998046875,"close":2834.39990234375,"volume":3740700000,"adjclose":2834.39990234375}]
Il faut donc aller chercher les différentes valeurs comme ici https://forum.excel-pratique.com/viewtopic.php?f=3&t=136908
Le problème est qu'à ce stade, je ne sais pas à quoi correspond cette date !
Je vais regarder cela de près.
S'agissant d'un historique -qui ne bougera plus- tu peux le charger manuellement ... et ensuite tes requêtes complèteront sur le court terme. C'est la méthode la plus simple et la plus efficace.
Sinon, j'ai testé différentes façons de faire, mais yahoo teste l'application qui appelle et limite les données.
Reste une solution que je vais tester demain : lacer le site via chrome ou firefox à partir d'excel. C'st théoriquement faisable mais pointu. Ensuite décoder le json.
Quel est ton navigateur par défaut (pour savoir s'il est compatible de la méthode) ?
Quel est ton navigateur par défaut (pour savoir s'il est compatible de la méthode) ?
mon navigateur par défaut est google Chrome .
Bonjour,
Voici.Reste une solution que je vais tester demain : lancer le site via chrome ou firefox à partir d'excel. C'st théoriquement faisable mais pointu. Ensuite décoder le json.
Il ne faut toucher à RIEN durant le fonctionnement de la macro.
#If VBA7 Then
Private Declare PtrSafe Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
(ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
#Else
Private Declare Function ShellExecute Lib "shell32.dll" Alias "ShellExecuteA" _
(ByVal hwnd As Long, ByVal lpOperation As String, ByVal lpFile As String, ByVal lpParameters As String, ByVal lpDirectory As String, ByVal nShowCmd As Long) As Long
#End If
Sub lire()
Dim ScriptEngine As Object
Set ScriptEngine = CreateObject("ScriptControl")
With ScriptEngine
.Language = "JScript"
' nombre d'occurences
.AddCode "function N(jsonObj){return jsonObj.length;} "
' pour les noms réservés par excel VBA, les valeurs numériques ou commençant par un chiffre, les variables
.AddCode "Object.prototype.element=function( i ) { return this[i] } ; "
End With
' origine des informations
Dim nav As Long, MyData As DataObject
Set MyData = New DataObject
Dim avant$, apres$
' Les signes plus +, Caret ^, pourcentage %, ~ tilde et les parenthèses () ont une signification spéciale pour SendKeys.
' Pour spécifier l’un de ces caractères, mettez-le entre accolades {}
nav = ShellExecute(0, "open", "https://www.qwant.com/", 0, 0, 1)
Application.Wait (Now + TimeValue("00:00:05"))
SendKeys "%d"
Application.Wait (Now + TimeValue("00:00:01"))
SendKeys "view-source:https://finance.yahoo.com/quote/{%}5EGSPC/history?p={%}5EGSPC"
SendKeys "{ENTER}"
Application.Wait (Now + TimeValue("00:00:6"))
SendKeys "^a"
Application.Wait (Now + TimeValue("00:00:01"))
SendKeys "^c"
Application.Wait (Now + TimeValue("00:00:01"))
MyData.GetFromClipboard
'commence et finit par {}
avant = """HistoricalPriceStore"":{"
apres = "}]"
JsonString = "{" & Split(Split(MyData.GetText(1), avant)(1), apres)(0) & apres & "}"
SendKeys "%{F4}"
Application.CutCopyMode = False
Dim HistoricalPriceStore As Object
Set HistoricalPriceStore = ScriptEngine.Eval("(" + JsonString + ")")
Cells(1, 1).CurrentRegion.Offset(1, 0).ClearContents
For i = 0 To ScriptEngine.Run("N", HistoricalPriceStore.prices) - 1
With HistoricalPriceStore.prices.element(i)
Cells(i + 2, 1) = .element("date")
Cells(i + 2, 2) = .element("open")
Cells(i + 2, 3) = .high
Cells(i + 2, 4) = .low
Cells(i + 2, 5) = .element("close")
Cells(i + 2, 6) = .volume
Cells(i + 2, 7) = .adjclose
End With
Next
End Sub
merci beaucoup pour votre grand dévouement à vouloir m'aider .
J'ai eu quelques problèmes avec ce dernier modèle que vous avez présenté .
J'ai joint les images des problèmes que j'ai dans ce dernier modèle :
image Date > Je n'ai pas pu mettre en forme la colonne A des dates pour jour-mois-année .
image VBAproject > dans le projet VBA, il y avait deux problèmes :
1) que voyez-vous en rouge dans les lignes 5 et 6, est-ce très étrange ici pour moi ces deux lignes 5 et 6 sont rouges? car il n'est pas normal de voir du code VBA rouge ici dans mon excel .
2) ce que vous voyez avec le fond jaune, c'est une erreur qui s'est produite lorsque j'ai essayé d'exécuter la macro ici dans mon excel .
Set ScriptEngine = CreateObject("ScriptControl")
image version portugaise Excel 2010 > cette image correspond à la version portugaise de mon Excel .
Normal, j'avais bien dit que ce que je récupérais n'était pas réellement des dates.image Date > Je n'ai pas pu mettre en forme la colonne A des dates pour jour-mois-année .
Je pense que c'est un format Unix, je suis en train de faire la conversion par une régression linéaire ... donc à venir !Le problème est qu'à ce stade, je ne sais pas à quoi correspond cette date !
Ce code ne doit pas bloquer le déroulement de la macro ... je suppose que tu es en 64bits. Cette partie est faite pour les plateformes en 32bits.1) que voyez-vous en rouge dans les lignes 5 et 6, est-ce très étrange ici pour moi ces deux lignes 5 et 6 sont rouges? car il n'est pas normal de voir du code VBA rouge ici dans mon excel .
Donc ici cela bloque ! Est-ce avec le fichier que j'ai posté ou est-ce avec ton fichier dans lequel tu as repris la macro ? Dans le second cas, il faut voir si les références sont bien activées.2) ce que vous voyez avec le fond jaune, c'est une erreur qui s'est produite lorsque j'ai essayé d'exécuter la macro ici dans mon excel .
Set ScriptEngine = CreateObject("ScriptControl")
une erreur se produit toujours lors de l'exécution de la macro ici pour moi .
Je joins des images et je fais également des commentaires :
image Références > cette image montre les références des bibliothèques qui sont activées ici dans mon excel .
dans votre image est Excel 15.0 et Office 15.0, tandis que dans mon image est Excel 14.0 et Office 14.0 .
image d'Erreur > cette image montre une erreur d'exécution (classe non enregistrée) .
image VBAproject > dans cette image montre avec un fond jaune la ligne du code VBA où l'erreur continue de se produire .
Le fichier Excel que j'exécute la macro est également joint .
Bon ben désolé, je n'ai aucune erreur chez moi avec le fichier que tu m'as renvoyé.
Reste donc la solution plus simple https://forum.excel-pratique.com/viewtopic.php?p=852328#p852328